Custom Number Formats in Excel

It is essential to define number formats in Excel in order to improve the usability of your model and the readability of its reports. This article explains how to do that.

The following screenshot shows you where in the "Format Cells" dialog you can define custom formats:

Excel: Format Cells, Custom

Basically, an Excel number format definition contains four separate parts, divided by a semicolon:

  • one part for positive numbers;
  • one part for negative numbers;
  • one part for zero (0);
  • one part for text.

A widely common format could be written as:

#,##0.0_);[Red](#,##0.0);" - "_);@_)

And, aligned to the right, it would look like this (note that the text entered is actually "n/a" - it always displays the text entered):

Excel: Number Formats

Another advantage of that format is that not only are negative numbers instantly recognised as such, but when listing positive and negative numbers in a list, they are aligned nicely so that you keep a good overview:

Excel: Number Formats 2

There is a virtually endless array of possible ways to format a cell. A comprehensive overview is given on office.microsoft.com.

With this knowledge in mind, another very common usage amonst financial modellers are year suffixes. You will have seen something like "2011A", the "A" indicating that the numbers are actuals, "2012B" with a "B" for budgeted numbers and "2013F" for forecasts and similar things. The problem is that if you just write that into a cell, you will not be able to use the date information in a simple way because it would be processed as a text string. What you can do, however, is use different number formats for those years:

YYYY"A "
YYYY"B "
YYYY"F "

You can then enter a date and it will only be shown as the year (four digits) followed by the suffix defined in that respective cell. The following two screenshots show what this can look like with three different cell formats - including the usage of a the EOMONTH formula as "proof" for the functionality:

Excel: Year Suffix (1)

Excel: Year Suffix (2)

 

2 Replies to “Custom Number Formats in Excel”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.