About this lesson
Learn to build your own custom number formats to display dates, values and variances how you want to see them.
Download this lesson’s related exercise files.Custom Number Formats.xlsx
14 KB Custom Number Formats - Completed.xlsx
Custom Number Formats.
Building your own custom number formats to display dates, values and variances the way you want to see them.
Where/when to use the technique
Custom number formats are a helpful tool that allows you to control how numbers are formatted, even though Excel may not have actually built in a number style that you want.
Creating ISO compliant dates
- Select A4:A9, right click, choose Format Cells, and select the Number tab
- Click Custom, and enter yyyy-mm-dd in the Type box
- Notice that the sample (at top) updates to show what the currently selected data will look like
- Click OK and notice that the dates are all represented in the ISO YYYY-MM-DD format
Showing leading zeros
- Select C4:C9, right click, choose Format Cells, and select the Number tab
- Click Custom, and enter 00000000 in the Type box
- Notice that all the numbers in the column now display with leading zeros (a 0 in the custom format will put in a value or a 0 if no data exists for that character place.)
- Select F4:F9, right click, choose Format Cells, and select the Number tab
- Click Custom, and the following in the Type box: #,### F;#,### U;”-“??
- The # will put in a value if one exists, and leave it blank if not
- The , will put in a thousands separator (but only if there is a value to the left)
- The ; separates the key pieces of the format (positive format; negative format; zero format)
- The “-“ puts in a dash as text
- The ? pads from the right with a space
- The F and U have no special meaning, so are treated as text
- The net effect is that positive numbers are displayed with an F after them (Favorable), negative numbers are displayed with a U next to them (Unfavorable), and zero values are displayed with a dash that is lined up with the rest of the values in the column
- This is a useful trick as you can build reports that never leaves someone wondering if a negative variance on an expense is a good or bad thing!
Lesson notes are only available for subscribers.