Welcome to a new series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. Excel MVP and FCA / FCMA Liam Bastick looks at an area of Excel many shy away from: customising number formatting.
Do you find you can’t quite get numbers to appear as you would like them to in your Excel spreadsheet? If you don’t really understand what all the various symbols mean in the ‘Custom Formats’ section, then this article is here to help you!
Given that one of the primary purposes of a spreadsheet is to present numerical data, it is important how numerical data is presented.
Cells may be individually formatted, using CTRL + 1 or ALT + O + E in all versions of Excel (alternatively, Home tab, Cells group, Format -> Format Cells…).
Formatting only changes the appearance, not the underlying value, of a cell. For example, if cells A1 and B1 had the number ‘1.4’ typed in but were formatted to zero decimal places, then if cell C1 = A1 + B1, you would truly have 1 + 1 = 3 (well, 1.4 + 1.4 = 2.8 anyway).
This should not be confused with ‘(Set) precision as displayed’ (File -> Excel Options -> Advanced -> When calculating this workbook -> Set precision as displayed). Selecting this option and clicking ‘OK’ will permanently change stored values in cells to whatever format has been selected, including the number of decimal places (e.g. 15.75 formatted to one decimal placed would become precisely 15.8).
From the above diagram, Excel has many built-in number formats that are fairly easy to understand, e.g. Currency, Date, Percentage. The default format is ‘General’ where Excel will endeavour to provide the most appropriate format for the contents. For example, typing ‘3 3/4’ into a cell will result in Excel selecting a mixed format.
But what do you do if you can’t find an appropriate format?
Selecting the ‘Custom’ category activates the ‘Type’ input box and allows between 200 and 250 custom number formats in a particular workbook, depending upon the language version of Excel that has been installed.
The ‘Type’ input box allows up to four aspects of formatting to be specified in a cell. These aspects are referred to as sections and are separated by a semi-colon (;). To ascertain what is contained in each section depends on the total number of sections used, viz.
|No. of Sections||Section Details (assuming no conditions)|
|1 (min)||All numerical values|
|2||Non-negative Numbers; Negative Numbers|
|3||Positive Numbers; Negative Numbers; Zero Values|
|4 (max)||Positive Numbers; Negative Numbers; Zero Values; Text|
|General||General number format|
|0||Digit placeholder (if no number, a ‘0’ will be used to ‘pad’)|
|#||Digit placeholder (does not display extra zeros)|
|?||Digit placeholder (leaves space for extra zeros, but does not display them)|
|. (decimal point / full stop)||Decimal point|
|, (comma)||Thousands separator|
|/||Used to delineate numerator from denominator in Fraction category|
|E+ e+ E- e-||Scientific notation|
|$ - + / ( ) : space||These characters are displayed in the number|
|“text”||For other characters, in order to ensure Excel does not misinterpret them, it is best to use enclose the character(s) in quotation marks…|
|\character||…or precede it with a backslash|
|*||Repeats the next character in the format to fill the column width; only one asterisk per section of a format is allowed|
|_character||Skips the width of the next character. In particular, this syntax is often used with the closing parenthesis, _) , in a positive number format (when the negative format includes brackets). This allows the values to line up at the decimal point|
|m||Month as a number without leading zeros (1 to 12)|
|mm||Month as a number with leading zeros (01 to 12)|
|mmm||Month as an abbreviation (Jan – Dec)|
|mmmm||Unabbreviated month (January – December)|
|mmmmm||First letter of month (J, F, M, A, M, J, J, A, S, O, N, D)|
|d||Day without leading zeros (1 to 31)|
|dd||Day with leading zeros (01 to 31)|
|ddd||Week day as an abbreviation (Sun – Sat)|
|dddd||Unabbreviated week day (Sunday – Saturday)|
|y or yy||Year as a two digit number (e.g. 09, 97)|
|yyy or yyyy||Year as a four digit number (e.g. 2009, 1997)|
|h||Hours as a number without leading zero (0 to 23)|
|hh||Hours as a number with leading zero (00 to 23)|
|m||Minutes as a number without leading zero (0 to 59)|
|mm||Minutes as a number with leading zero (00 to 59)|
|s||Seconds as a number without leading zero (0 to 59)|
|ss||Seconds as a number with leading zero (00 to 59)|
|[h]||With times only, will increment hours to 24 and beyond|
|[m]||With times only, will increment minutes to 60 and beyond|
|[s]||With times only, will increment seconds to 60 and beyond|
|AM/PM am/pm||Time based on the 12-hour clock [24-hour clock is the default]|
|[Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], [Yellow]||Displays the characters in the specified colours|
|[Color n]||Displays the characters in a specified colour, where n is a value from 1 to 56, and refers to the nth colour in the color palette|
|;||Delineates a section|
|[Condition Value]||Condition may be any one of the comparison operators, <, >, =, <=, >=, <> and Value may be any number; a number format may contain up to two conditions|
|Example 1 (Comprehensive!)|
|[Blue]$* _(#,##0.0,_0_);[Red]$* (#,##0.00,);[Color 7]\-_._0_0_);[Cyan]@*.”is text”
#,##0.0, ensures positive numbers contain thousand separators (where needed) and displays the number to the nearest 0.1 of a thousand. Two commas at the end would have the number displayed to the nearest 0.1 of a million, and so on.
Finally, the _0_) requires Excel to maintain enough space at the right end of a cell for a digit (not necessarily zero and a close bracket). It should be noted that a separate underscore is required for each character that is to be allowed for.
The second section, [Red]$* (#,##0.00,), specifies the formatting for negative numbers. It is similar to the first section, but colours the number red, reports numbers to 0.01 of a thousand and encloses it in brackets.
The third section, [Color 7]\-_._0_0_), specifies the formatting for zero values. This colours zero values “Color 7” which is a delightful pink in Excel’s standard color palette. I am a great believer in using a dash, generated by using \- here, to denote zero as it distinguishes a zero value from something that is approximately zero, which can be useful for error checking, etc. The final four underscored characters, _._0_0_), ensure that the dash will line up with the units value of a positive or negative value.
Finally, the fourth section, [Cyan]@*.”is text”, defines how text is to be formatted. If omitted, text is simply formatted ‘generally’, but here it will be coloured cyan. The @ symbol specifies the relative location of the text within the cell (left-hand side of the cell), the *. will ‘fill’ the cell with period characters and “is text” will add these words to the end of the text, right-aligned (note no ‘&’ concatenation is required since these words appear in the formatting only).
|Example 2 (‘Hiding’ a Cell)|
|Example 3 (‘Conditional Formatting’ in a Cell)|
Above, I mentioned what the four sections ‘generally’ mean. This third example highlights that this is not always the case. Custom number formats allow up to two conditions to be specified. This is because only four sections are allowed for custom number formatting and two are reserved. The fourth section always specifies text formatting and one other section is required to detail how ‘everything else’ (numerically) will be formatted.
The conditions are included in square brackets such that if the condition is true, the following formatting will be applied.
In this example, there are only three sections, so text will be formatted ‘generally’. The first section, [>=1000000]#,##0,,"M", will format all numbers greater than or equal to a million to the nearest million and add an “M” to the end of the number.
The second section will only be considered if the first condition is not true, so the order of the two ‘conditional formats’ needs to be thought through. Here, the second section, [>=1000]#,##0,"K", will format all numbers greater than or equal to a thousand (but necessarily less than a million) to the nearest thousand and add a “K” to the end of the number.
The third and final section, 0, will format all other numbers (every value less than 1,000) to the nearest integer without thousands separator(s).
Use with Caution
Using lots of custom number formats in a single workbook uses considerable memory and can slow down the calculation time of an Excel file unnecessarily. Many of these formats are created accidentally. Each time a custom number format is edited, it will generate an additional listing for Custom Category Types. Any custom formats created inadvertently in this manner (that are not being used in the file) should be deleted; good house-keeping is essential.
If you have a query, please feel free to drop Liam a line at [email protected]
About Liam Bastick
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.
He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments. Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.