About this lesson
A classic cost-accounting trick to display variance in a easy to read way (and avoid watching your audience do math in their heads at each review)
Download this lesson’s related exercise files.Displaying Variances with Custom Number Formats.xlsx
30.9 KB Displaying Variances with Custom Number Formats - Completed.xlsx
Displaying Variances with Custom Number Formats
Using custom number formats to show data in a better way.
When to use
When you want to customize your dashboard display to make things easier to read, add industry lingo, or just force a number format that you prefer. This specific example shows how to create variances marked with F (favorable) or U (unfavorable) instead of + or -.
Global syntax of custom number formats
- A custom number can hold up to four different formats, separated by semi-colons
- The general setup is: positive format; negative format; zero format; text format
Reserved characters used in this example
- 0 returns a number (if there is one to fill the space) or a 0
- # returns a number (if there is one to fill the space) and is suppressed if not
- , separates thousands and millions with commas (if the number goes that high)
- “ “ returns anything between the quotes as text
Number format to show Favorable/Unfavorable variances
- #,##0 F;#,##0 U;"- "
Hints & tips
- This pattern could be easily customized to use G for Good, or B for Bad, simply by substituting those letters into the formula in place of F and U
- This function could also be modified to show a red color for negative numbers by inserting [Red] after the first semi-colon as follows: #,##0 F;[Red]#,##0 U;"- "
Lesson notes are only available for subscribers.