Change how cell values are displayed using Custom Number Formats

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Custom number formats are all about customising how values are displayed in cells. For example, when dealing with very large numbers we might want to show it in thousands/millions rather than units (e.g. we might want to see $3.46m instead of 3460000). The traditional approach is to perform a calculation on the cells, dividing them by the factor we require. But this actually changes our numbers and creates an additional layer between our source numbers and what we report. A much better option is to keep the numbers as they originally were and use custom number formats to display it in the way we want. A similar thing applies to dates – sometimes we want to show a date as “Mar 12” for example rather than 01/03/2012.

When we go to the Format Cells dialog box (screen shot below), through the right-click menu or Home Ribbon, we have a list of standard formats categorised by Type (Number, Currency, Date etc). If the format we require is listed here then this is an easy way to apply it; we only need to Apply a Custom number format instead (by going into category ‘Custom’) when one of the standard options aren’t what we’re after. The custom category itself has a list of custom number formats – written as a number of symbols and characters-which are ready to use, but we need to understand what those represent in order to know which one to apply or to write our own.

This topic will look at the meaning of the characters and symbols contained in custom number format codes. A useful starting point is to use one of the standard number formats as a basis – once we select one of the standard number formats if we then go to the ‘Custom’ category we will see how that number format is written using a custom number format string. For example if we select to format a number as Currency with the £ symbol, rounded to 2 decimal places, and then go to the ‘Custom’ tab we will see £#,##0.00 which is the custom number format equating to our standard number format selection.

Once we’ve identified our format string, we can also use a formula (the TEXT function) to convert our number format on demand – we can leave our source numbers in the original format but when we bring them to reporting we change the format to display how we want using the TEXT function.

The table below shows the most common number symbols along with a description for each.

Using this we can scale numbers as required and display numbers in formats not available as standard. Consider the below table of custom number formats and how the number 1234567.89 would be displayed. Note that we can use either 0 or # the digit placeholder – here I’ve used 0 throughout to keep things simple.

Note that adding a string at the end (like “k” or “m”) doesn’t turn the cell format into a text one – it’s still a number for the purposes of calculations; the only thing that is changed is how the value is displayed.

To take this one step further, sometimes we want to display values differently depending on the value itself – for example to show negative numbers in brackets (or in red).

The 2 new characters we need to take into account to do this are the semicolon which separates out different conditions (e.g. positive vs. negative numbers) and square brackets which are used to indicate the conditions themselves or the formats required.

For example the number format code 0;(-0) will show the number in whole units (rounded to the nearest whole number), with brackets around it if the number is negative.

The number format 0;[Red](-0) will show the same thing but negative numbers will be both in brackets and will be displayed in red font. (Positive numbers will display as normal black text).

We can use the square brackets to also show different things (including text!) depending on the value of the cell – for example instead of (or in addition to) the number we can show “Low” if the number is less than 10, “Medium” if it’s between 10 and 50 and “High” if it’s greater than 50. (We can have up to 3 conditions).

For this, the number format text string will need to have the conditions in square brackets (e.g. [<10]) followed by the text we want to show with each condition/value separated by a semicolon. So our number format text string would be [<10]"Low";[<50]"Medium";"High". In this case the number 5 will just display as ‘Low’ and the number 80 would display as ‘High’. If we wanted to show the number as well as the text, we could use the digit placeholder 0 as part of the value following the condition for example the text format string [<10]0" (Low)";[<50]0" (Medium)";0" (High)" would show the number 5 as ‘5 (Low)’ and 80 as ‘80 (High)’. This can be very useful in Pivot tables for example which are only capable of having numbers as column values (aggregating them) – this way we can keep them as numbers but display them as text!

Finally we can apply this concept to hide zeros – the custom number format required is “0;0;;@”

With dates, unless you specifically choose a date number format for a cell the default way of displaying dates is as a number (e.g. 41235). This actually represents the number of days since 1900 which makes sense to Excel but not really to its human users! But using this number is how Excel can do [calculations with dates] regardless of whether it is shown in US/UK format or with text rather than numbers.

The standard Date number formats allow you to show the date elements separated by slashes or commas, and to show the full date written in words or just as numbers. If we want to change this to show it in a custom way, we can use the following codes (we can combine any of these e.g. d mmm yy on the 1st Jan 2012 would show ‘1 Jan 12’).

When we link to a cell in a formula it will always take the default/source number format – if we want to present that in a different way or use it in a different format for the purposes of our formula then we can use the TEXT function to convert it into the format we require.

The syntax of the TEXT function is =TEXT(value, format_text). The ‘value’ is the cell reference (or hard-coded value) we want to convert, the ‘format_ text’ is the custom number format string we’ve built up using the characters/symbols above. This number format string can be stored in a cell value as well.

In the Topic Example Workbook, we have our original numbers in Column A and have written out the custom format strings in Column D. In Column E we have linked to the value in Column A and then formatted each cell individually in Column E according to the format required. A quicker alternative to this is using the TEXT Function (in Colum F) where we link to the cell in Column A but use the custom text string in Column D to display it in the way required.

For example if we have 1234567.89 in cell A2 and the format string of 0.00,, in cell D2 then in cell F2 we can have the formula = TEXT(A2,D2) which will return ‘1.23’ (scaling the number by 1 million and showing to 2 decimal places).

Of course this isn’t a different result from just linking to cell A2 and then formatting that cell, but it is more convenient when we want to have dynamic number formats (e.g. according to a scale chosen by the user). It is also useful for when we want to use numbers or dates as part of a text string. For example, if we have a date somewhere in the workbook which we then want to refer to elsewhere then we can convert that into the format we require as part of our link formula. For example. if the date is stored in the format 01/03/2012 in cell F13 but we want to have a column heading that reads “Balance as at Mar 12” then we can have write a dynamic column heading as =”Balance as at ” & TEXT(F13,”mmm yy”)
. Note that this column heading will be dynamic – if the value in cell F13 changes to another date our heading will change.

In the ‘ Custom number formats’ tab of the Topic Example Workbook:

• In cells D3:D7, Write a custom number format string that represents the corresponding description in column B and would display the number in Column A (-1234567.89) in the format as shown in Column C.
• In cells E3:E7, change the format of the cells (using the Format Cells Dialog) by applying the custom number format string stated in Column D. The cells should now look like the expected results in cells C3:C7
• In cells F3:F7, use the TEXT function passing in the corresponding cell in Column A formatted by the text string in column D. The cells should now look like the expected results in cells C3:C7

A demonstration is shown in the video clip below.



Conditional Number Formats

In the ‘ Conditional Number Formats’ worksheet, write a conditional number format string in cell B4 that represents the description in the Data Validation comment. Then use the TEXT function in cells C4:C14 to represent the values in Cells A4:A14 using the custom number format text string in cell B4.

In the ‘TEXT function’ worksheet, in cell B3 create a dynamic heading using the TEXT function that reads “Balance as at ” followed by the date (in cell B1) written in the format mmm yy (e.g. “Mar 12”). So for example if the date in cell B1 is 01/03/2012, the text in cell B3 should read “Balance as at Mar 12”.

A demonstration is shown in the video clip below.

Resist the temptation to view the answers file below until you have tried this!

Once completed, the Excel workbook should look like this.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.