Core modelling skill 6: Applying number formats
Note: there is no new reference file for this section.
Consistent formatting is an important way of maintaining order in our models. Excel's built-in formatting tools are not very helpful.
This chapter looks at the Productivity Pack shortcuts for applying formatting. By defining the formats we use, we can ensure that the formatting is consistent between models and across a team.
We begin by looking at the number styles available and will then look at customising these styles.
All of the keystrokes listed in this section are Productivity Pack keystrokes.
Default number style
This is the number style that you will use most throughout your model.
By default, this is set to show no decimal places. We can change this; see below on customising styles.
When we apply the default number style to our power tariff input - it looks like this.
Note that we see a zero here. The number styles have been set up to show a zero where a value has been rounded to zero.
If a number is actually zero, it will show a dash. This is to ensure that you can tell when you have zero, vs when you have a value that has been rounded to zero.
Note that pressing Ctrl+Shift+, will toggle the comma separator on and off. This can be useful if you are formatting year numbers, for example.
Add decimal places
This keystroke adds a decimal place. This is a valuable keystroke since this is difficult to do with Excel's built-in ribbon tools.
Remove decimal places
Similarly, this keystroke removes one decimal place.
Percentage style
Format the active cell as a %
Factor style
Formats the active cell with "factor style". By default, this is four decimals places. We would use this for line items like discount and escalation factors, where four decimals are a helpful detail.
Date style
There are two date styles: Long and short. Ctrl+Shift+l toggles between the two styles.
Long date style is dd mm yyyy
Short date style is dd mm yy
How to customise styles
To change the styles applied by the Productivity Pack, first, unhide the Productivity Pack file.
In the Custom Shortcut sheet, you can define the styles you wish to apply. For example, if the standard monetary unit in your model was millions of dollars, you may want to show three decimals by default.
Click on "Save changes and close workbook".
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.