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.