Because it’s buried deep in the cell formatting menu and has very little documentation, Excel’s custom number formatting is probably its most underutilized feature. Understanding and mastering this feature can greatly enhance the Visual Design of your Excel workbooks. The key benefit of custom number formatting is:
It allows you to change the appearance of your data without actually changing the data value
Please keep in mind that this is different than conditional formatting which, while allowing you to change the font, font size, text color, and highlighting, does not allow you to change the actual data that appears.
People who don’t understand custom number formatting will often go through the process of changing their data values rather than just the presentation of those values.
For example, consider a user who wants to show 1,000,000 as 1.0 M. Instead of using formatting, the user decides to divide his value by 1,000,000 and concatenates an “M” to the end of it. In doing so, not only does he add an additional step in his model, but such a step is also rarely documented, which can create confusion if the work needs to be handed off to another person.
An even worse case if is the user decides to remove the link between the display value and the actual value altogether. He or she simply hardcodes the value by typing in 1.0 M. At that point, any changes made to the original 1,000,000 value will not translate through to what is displayed. Once you master the custom number formatting syntax, you can avoid both of these issues. Custom formatting is like having your cake and eating it too; it allows you to improve the appearance of your outputs while still maintaining the integrity of your data inputs.
To get a sense of how custom number formatting works:
Select a cell and make it “Accounting” format using the home section of the Excel ribbon
Hit CTRL + 1 to open up the Format Cells menu
Now in the column of formatting options, Select – Custom
The Custom Number Formatting Syntax
The first thing you should notice is that all custom number formats follow a basic syntax:
<Positive Values> ; <Negative Values> ; <Zero Values> ; <Text Values>
Between each of the semicolons is where you define the formatting for each of the conditions specified. Please note that in some special cases, these conditions are not relevant (ie. date and time formats) and that you don’t necessarily have to specify the condition for zeroes or text values.
Accounting Format Adjustments
In this view, you can also see all the custom formatting adjustments that go into creating the Accounting format. (This is also a good exercise if you like the Accounting format, as I usually start with Accounting as a base before I make custom formatting adjustments.)
Now to explain what each of these adjustments do:
|_(||Adds a left indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore|
|$||Special character that will show up in your output without the need to add quotation marks within the syntax|
|*||(Please also note that the code indicated here is both an asterisk and a blank space) The asterisk tells Excel to repeat the subsequent character until the width of the cell is filled; this is why the accounting format has the dollar sign aligned to the left, the number aligned to the right, with as many spaces as necessary in between|
|#,##0||Displays commas in between every three digits, for all values exceeding three digits|
|0||Digit placeholder that displays insignificant digits (ie. digits that are zero)|
|0||The zeros trailing after the first zero indicate how many decimals places you want to show; even if the final digit is zero, it will be displayed in this format|
|_)||Adds a right indent to the cell, so it does not ride up against the edge; the underscore value tells Excel to create a space equal to the character following the underscore|
|“-“||Displays a text value; anything between the quotation marks will be shown|
|?||Digit placeholder similar to 0, but does not display insignificant digits; if the value is zero, it will just be a blank space; in accounting format, because it is in the Zero section of the syntax, it will always appear as a blank space|
|@||Placeholder for text|
Theoretically, there are an infinite number of adjustments you can make the using custom number formatting. Rather than trying to include all of them, I will focus on explaining the most common and most useful implementations of this feature. For the most part, I will try to leave out scenarios that can either be typed in manually or created through one of the default number format categories.
Round by Thousands
The most common use of custom number formatting is to round large numbers greater than 1,000. For example, assume we want to display 1,562,000 as 1.562.
Each comma rounds three decimal places; therefore “,” puts your number in thousands “,,” is in millions and “,,,” is in billions, etc. In front of the comma, we indicate how many decimal places we want to show. Note that you typically want to replicate any adjustments you make on the positive side with the negative one, in case your value switches signs. Don’t forget to include negative notation for the negative number syntax – either a negative sign or a set of enclosed parentheses.
Making this adjustment is especially helpful for charts, where a long number can add unnecessary space to your vertical axis.
Add a Unit Value
In the example above, after you’ve rounded your value, you may also need to add a unit indicator to the end of your new value. To do this, we simply add the text of the new value within quotation marks in the custom number formatting syntax.
Please note, that in this newly formatted cell, I can type whatever numeric value I want without typing “M” at the end of my entry. It will still show up in the same format as 1.562 M.
Positive Numbers Green / Negative Numbers Red
[Green] ; [Red]
This format is useful when you have a dynamic model that will spit out both positive and negative output numbers. In this situation, because we’re only changing the color of the text, not the values that show up, this change can also be made using Excel’s basic conditional formatting.
Add an Indent
The accounting format already adds indents on both sides of the cell for you. Many people like to indent their cells so that their values don’t ride up right against the cell border.
Turn Zeroes into Dashes or Blanks
The accounting format already turns all zeros into dashes.
Making this adjustment is very helpful when you have a large table of data with both zero and non-zero numbers. Turning the zeroes into dashes makes it much easier to see where your non-zero data points are.
Using blanks for zeros is also helpful when labeling dynamic graphs. You could delete the values that are zero, but if the graph values were to change, you’d have to add those labels back every time. Custom number formatting allows you to automate this process.
Add Parentheses to Negative Percentages
While Excel 2010 has a format for negative values with parentheses in both the default “Number” and “Currency” categories, it does not have one specifically for negative percentage values. We can use custom number formatting to create our own. Anytime you show negative values with parentheses, you should also add an indent to the positive value. That way, positive and negative numbers line up when stacked on top of each other.
Show an Error Message
Since custom number formatting allows you to display pretty much whatever text you want, you can leverage it to display error messages when the situation applies. For example, assume you have a model that should only output positive numbers. Anytime a negative value appears, you can have it just display an error message rather than the negative number.
Show Leading Zeroes
Excel’s default formatting cuts off leading zeroes. Leading zeros are usually necessary for any primary key codes that use them.
Display a Date or Time
When choosing a date format, you should always start by picking the standard date format that’s closest to what you want and then make adjustments in the custom formatting option. Below are common formatting designations for dates and times.
|m||month as a number with no leading zeroes|
|mm||month as a number with leading zeroes|
|mmm||three letter month abbreviation|
|mmmm||full name of month|
|d||day as number with no leading zeroes|
|dd||day as number with leading zeroes|
|ddd||three letter day of week abbreviation (Wed)|
|dddd||unabbreviated day of week text (Wednesday)|
|yy||two digit year|
|yyyy||four digit year|
|h||hour as number with no leading zeroes|
|hh||hour as number with leading zeroes|
|m||minutes as number with no leading zeroes|
|mm||minutes as number with leading zeroes|
|s||seconds as number with no leading zeroes|
|ss||seconds as number with leading zeroes|
A Note on Conditions
Custom number formatting also allows you to add conditions to your formatting. However, you’re probably better off doing this using Excel’s conditional formatting feature, which is better designed to handle the task.