Financial models can get very big and very complicated. You may have a 10 sheet model that runs from a single assumption driver. Formatting is therefore very important to making an effective model that can actually be understood.
Almost all of your formatting in Excel is going to take place in the Format Cells menu (see picture below). This can be accessed by using the Ctrl-1 shortcut over any cell or highlighted group of cells.
To be effective, it is important to know how to navigate this menu without using the mouse. Here’s how it works. You have different menu ‘areas’ which can be navigated using the Tab key. You can see which area you are in currently by a little dotted box. When navigating within a certain area, you use the arrow keys.
The tabs in the Format Cells menu are as follows: Number, Alignment, Font, Border, Fill, Protection.
I will outline what each of these does below.
The number tab allows you to change how your text appears. There are a series of presets, but you can also use the custom option to make your own. When doing this, here are a few shortcuts.
# – puts your entire number in the cell as you have entered it.
0.00 – puts your number up to 2 decimal places. The more zeros you add, the more decimal places your number will be displayed to.
“cfjsu” – Adds text to the display. This is useful if you want to add something like ‘x’ onto the end of you multiples, or ‘A’ for actual and ‘P’ for predicted. This maintains the integrity of the number so that it can be used in formulas.
You can also add a semi-colon (;) to have different formats for positive and negative numbers.
As an example, here is the formatting for a positive and negative number, to two decimal places, using brackets to denote a negative and a space at the end of the positive numbers to align the columns.
The alignment tab is not used extensively, apart from center aligning text, or including an indent. A good tip to use when making tables and adding a heading, is to not merge cells to center the heading. This can create difficulty when selecting cells. Rather, under the Horizontal text alignment option, use Center Across Selection (you will need to choose this selection before opening the menu). This will make it look like you have merged the cells, without actually merging the cells.
Wrap text can also be useful when adding long column headers, so that they do not flow into neighbouring cells. When making row headers that include some sort of hierarchical list, (like a balance sheet) you can use the indent selector here to add the indent, rather than hitting the space bar every time.
The font tab is largely useless if you know your keyboard shortcuts, however if you are in the menu, it can be used to change the colour of your font, the font itself, and the size. It can also be used to make superscripts and subscripts, although this is uncommon to need to use them. (Referencing should be done using comments, unless you are making your spreadsheet for printing.)
The border menu is useful to make formatting easier to read. I would recommend however, setting up macros to do your common border tasks. Good ones to set up are:
- Thick and thin borders on each side.
- Clear all borders
You will learn through experience your preference for using borders.
The fill tab is quite simple – you choose the colour of your cell. Again – set up your macros for the different colour cells you want before starting work, and you won’t have to look at this tab much. It is very important to use cell fills, but don’t be spending hours doing the same task over and over – use some shortcuts.
This will be used very rarely. Essentially, you have two options
- Lock the cell from editing
- Hide the formulas used the cell.
You are not going to use these very often. The only time, really, is if there are cells which will break your model completely if the are changed the slightest bit. But even then, you can colour them bright red and make a note of it.
This is essentially the basics of formatting cells in Excel, apart from the use of conditional formatting. But that is the topic of next week’s post!