Whatever emotions the talk of £6000000000.00 worth of bank bonuses stirs in you, your primary concern is almost certainly how to format such large numbers as effectively as possible in Microsoft Excel. Excel has several ways of formatting numbers – there are several built in number formats which are fairly self-explanatory, but the custom number format gives you more complete control over how your numbers are displayed. Simon Hurst FSN senior writer, explains.
Whether you are working with billions or fractions, knowing how to format numbers can dramatically improve the clarity and impact of your spreadsheet reports. We will cover the basic elements of creating an Excel custom number format before looking at how to apply the techniques to particularly large numbers.
To access the number formatting settings for a cell, or a group of cells, select the Number tab from the Format Cells dialog. This dialog can be opened using the dialog button in the Number group of the Home ribbon. This will take you directly to the Number tab of the dialog.
Alternatively, right-click on the relevant cells and choose 'Format Cells' or use the Control+1 (number 1) keyboard shortcut:
There are built in Number, Currency and Accounting formats and the Custom option is at the bottom of the list. Selecting Custom displays a list of existing formats that can be used as the ‘starting point’ for a new custom format: just select the format from the list and it appears in the ‘Type’ box ready for editing.
A Custom number format has four sections: Positive, Negative, Zero and Text. Each section contains a format ‘picture’ that defines how to format the cell contents depending on which of the four categories it falls into. All sections after the first are optional and the sections are separated by semi-colons. Generally, you will only need to set formats for the first three sections, the fourth only being used when someone enters text into the cell.
Let’s look at a typical positive number section format and see what the elements of the format picture represent:
#,##0.00_)
The # characters are used as placeholders in order to get to indicate that a comma should be used to separate each block of three digits. You only have to indicate one comma for it to be used to separate thousands, millions, billions and so on:
123,456,789,000.00
The use of a 0 as the last ‘placeholder’ before the decimal point makes Excel display the zero even if there are no numbers to its left. So, using our example format, .9 would display as 0.90
A format of 0,000.00_) would display 0,000.90
If we didn’t want to display the decimal places at all, our format picture would just look like this:
#,##0_)
Now onto the odd bit at the end: underscore, closing bracket. The underscore tells Excel to leave an amount of space equal to the width of the next character in the format picture. In our case we leave an amount of space at the right-hand side of every positive number exactly equal to the width of a closing bracket character. This ensures that positive numbers line up properly with negative numbers shown using brackets.
Our negative number format with brackets would look like this:
(#,##0)
To show negatives in a different colour we precede the format picture in the negative section with the name of our colour surrounded by square brackets:
[red](#,##0)
Although colours are perhaps most commonly used to differentiate negative numbers, the same method can be used to show all four sections in different colours:
[Blue]#,##0_);[Red](#,##0);[Green]-?;[Yellow]@
Here we have added [Blue] to our positive format and added a third and fourth section, with the sections separated by semi-colons. Our third section uses a ‘-‘ to display an actual ‘-‘ rather than a zero for zero values, with the question mark acting as a digit placeholder but leaving an amount of space equal in width to a number character.
Finally, the fourth section uses the @ sign to display whatever text is entered in the cell as entered.
Now we know how the different elements of the custom format works, we can apply the technique to our problem of bonus billions. Here is an example showing large numbers displayed with the standard 2 decimal place Number format, compared with using a custom format:
Although, for some, every single penny might hurt, once we get into billions, then pennies, pounds and even thousands of pounds are likely to lose much significance, so just displaying the millions may be the clearest presentation.
In the Excel custom number format picture, each comma at the right-hand end of the number format suppresses a set of three digits, so adding two commas to the end of each of our number sections would result in the presentation shown in our £billion column:
#,##0,,;[Red](#,##0,,);-?
Where we are using a number in isolation, rather than as part of a column with a suitable heading, then we might want to include an indication of the magnitude of the figure in the format itself.
The following format would show millions with a suffix of M. The M needs to be surrounded in double-quotes to avoid it being treated as the month element of a date format:
#,##0,,"M"_);[Red](#,##0,,"M");-?
Here we have deleted a comma and used k to signify thousands. There is no need to use the double quotes for k as, unlike M, it is not used as a format code:
#,##0,k_);[Red](#,##0,k);-?
Below we can see the results of these two formats in cells H13 and H14:
There’s a lot more to working with number formats and large numbers. Simon Hurst is author of “Accounting Spreadsheets to the MAX” due to be published by FSN in the spring. Amongst many other subjects, it will cover formatting for maximum impact and dealing with rounding problems.
