One of the main reasons we use Excel is to analyse and display our data, for either our own consumption, or to show to others. In both cases, we want our data to be easily readable, and any important patterns to be immediately obvious.
We use colours, borders and other formatting to highlight important characteristics of our data, and to de-emphasize those features that should stay in the background (see The Dashboard Squint Test for more). In just the same way, we can use number formats to highlight numbers that are unusual in some way, decrease the focus on uninteresting numbers, or to remove excess detail. Here we recap the essentials of numeric formatting in Excel.
The basics
To apply or change a number format, select the cell or range that needs to be altered, then either:
- make basic changes (add or remove decimal places, use percentages and so on) using the Number button group on the Home tab (in Excel 2007 and newer),
- make more advanced changes by right-clicking on the range and selecting the Format Cells option,
- if you prefer keyboard shortcuts, you can show the format cells dialog by using the Ctrl+1 keyboard shortcut.
From the dialog, you can select some common and very useful formats, including:
- Number: this allows you to customise the number of decimal places and whether to show thousand separators
- Date and Time: for formatting dates and times, allowing a variety of shorter and longer options
- Percentage: format the numbers as percentages, with the desired number of decimal places
- Custom: allows you to specify your own custom formats (see below)
Simple custom formats
We’ll first go through some simple examples, including some of the standard formats mentioned above, so that in the next section we can build up more complex ones. To enter these formats, follow the steps listed above, then select Custom from the list on the left of the dialog.
- To show a custom number of decimal places using a number format, write the number you want to show using zeros, for example 0.00 to show your number using 2 decimal places
- To include digits only if they exist, use a # sign instead of the zero. For example, to only include the part of the number before the decimal if greater than 1 (or less than -1), use #.00
- To include a thousands separator in the number, use #,###. We use the # symbol to avoid forcing Excel to display unnecessary zeros
- To format numbers as percentages, just place a % after the format. For example, using the 0.0% format will cause 0.2534 to display as 25.3%
- To give the numbers a colour, put the name of the colour in square brackets before the format, like this: [Red]0.0
More advanced formats
The formats that we have used so far only use one format for all numbers. In fact, Excel lets us specify four formats: one for positive numbers and one format for negative numbers, one for zeros and one for text – all in one cell. To do this, we use the semicolon to separate the different formats. For example, to format only negative numbers as blue, we can use 0.0;[Blue]-0.0;; In this example, because we have left the other sections blank, zeros will appear as empty cells on the worksheet.
If we combine the pieces of information from the last paragraph, we can find another useful format. Any cells with the ;;; format applied will hide any data in the cell. This can be useful if you want a formula in a particular cell, but don’t want to hide an entire row or column for it.
Another common case is where you have large numbers, but don’t need to see all the digits. In this case, it can be useful to just emphasize the important part of the number, by using this format: 0, This format will round to the nearest thousand, and remove the excess zeros. You can extend this to millions by using another comma, and it’s even possible to include an indicator that the number is shortened like this: 0,,”M”
There are many more special formats available, including changing the boundaries between the semicolons and date and time formats. Have a look at the further reading, below, for more information.
Number formats in XLCubed
XLCubed, being tightly integrated with Excel, allows you to specify number formats in two ways.
- For XLCubed value formulas, for example XL3Lookup and XL3ValueRankLookup, the number format of the containing cell can be modified in the way described above. In the same way as any normal Excel-based formula, the format is preserved when the value changes due to changes in your data.
- For XLCubed Grids, apply the format as above, then use the right-click menu, and choose Apply Format to Data. This asks XLCubed to maintain the format on the entire slice of your data. More detailed instructions can be found here.
