Financial Model Formatting – Cell Styles
February 18, 2022
What is “Financial Model Formatting – Cell Styles”?
Cell styles are important because they will save you time…lots of time.
Cell styles allow for repeatable, consistent formatting in seconds. Moreover, all of the cells formatted with a particular cell style are secretly connected, so all of them (perhaps tens of thousands of cells) can be re-formatted at a moment’s notice.
Excel has many cell styles built-in to give you a starting point, or you can create/modify your own.
Key Learning Points
- Cell styles allow for consistent formatting, be it font type, font size, colors, borders, etc., all in one style
- Cell style cells can all be reformatted quickly, saving huge amounts of time
- You can use cell styles already built into Excel or modify them to create your own
How to Apply Cell Styles in Excel
Using cell styles will make formatting your work much, much faster. Also, making changes to the whole file can be done in seconds. If you have a big assignment and many hours of work to go, this can be the difference between going home early or going home tomorrow.
How to Change Cell Styles In Excel?
Click on the Home ribbon, and in the Styles section click on Cell Styles. You can then click on a cell style, and it will be applied to your selected cells.
What Is The Keyboard Shortcut For Cell Styles?
The keyboard shortcut for Cell Styles is Alt H J on a Windows computer.
What Cell Styles Are Available?
Below are two examples of cell styles.
The first shows the cell styles built into the default Excel sheet:
And secondly, we have the cell styles built into a Financial Edge Excel sheet (see the downloadable file accompanying this article. You can use it to practice your formatting).
In the screenshot above, the cell styles have been used several times:
- The Hard Coded style has been used in C12:D12
- The Input style has been used in cells E5:G9
- The Percent style has been used in cells C6:C7 and D5:G7 (which layers on top of the Input style – more on this later)
- The Tertiary Title style has been used in A4 and A11
- And many more…
Rather than building up the formatting piecemeal in each cell (font = Calibri, size = 11, font color = blue… which would take ages), we can instead apply a ready-made cell style with all of these attributes in one place. It makes for fast formatting, and it’s always consistent.
How do Cell Styles enable Lightning Fast Changes?
Imagine your boss asks you to change all of the headings in column A. There are thousands of them, spread over hundreds of sheets, and you can imagine your evening disappearing under a mountain of formatting.
But if all of them were initially formatted using cell styles, all you need to do is modify the original cell style.
How To Modify/Change Cell Styles?
Imagine the boss prefers Inputs to be red rather than blue (weird, but ok). Go to Home / Cell Styles and right-click on the Inputs style, and click on Modify:
Then click on Format:
Then go to the Font tab and change the color to red:
Click OK a few times, and all of your inputs in this file will have changed to red!…if you used the Input cell style to format them blue originally:
Instead of working all night making the changes, we get to go home early.
Cell Styles Are Clearly Amazing, So What Cell Styles Should I Have?
Here is our recommended list, but think about the formatting you use and add/modify your own:
- Hard Coded Historical – Blue font. Historical numbers, or any hard coded numbers, are colored blue
- Input Assumption – Blue font with a colored background – Inputs or assumptions (which are hardcodes that could change) are blue with a colored background
- Percent –set the number format to be a percentage
- Date – set this to your preferred style, maybe dd-mm-yy or mm-dd-yy
- Multiple – this cell style will use the normal number style but then place an “x” after it
Normal is just the regular black cell style used for all formulas, calculations, or functions. You can leave this as it is, or maybe modify it if you prefer it a little different – perhaps you prefer three decimal places vs. the default?
Can I Use Two Cell Styles On Top Of Each Other?
Yes, but only if they don’t have competing attributes i.e., tick boxes.
Here we have clicked on the Home ribbon / Cell Styles and then right-clicked on two different styles. We are comparing the attributes of the Input style and the Percent style:
Notice how the Input style does not have a tick for the Number. This means it will not have any special number formatting.
Whereas the Percent style only has the Number ticked. This means that the Input style and Percent style can be laid on top of each other. It results in a blue Input which also has a percent symbol:
The lesson is only to tick the boxes that you need. Tick the fewest number of boxes possible, and you will be able to layer multiple styles on top of each other.
How to Create A Custom Cell Style?
Click on Home ribbon / Cell Styles, and click on New Cell Style. Here you can tick the attributes you want, click Format, and choose the formatting.
How to Copy Cell Styles In Excel?
If a cell style has been applied to a cell, and you copy/paste that cell (Ctrl+C, Ctrl +V), or just copy/paste the formatting from the cell (Ctrl+C, Ctrl+Alt+V), the newly pasted cell will still possess all of the attributes of the cell style, and will be linked to the cell style too if you choose to modify it. Amazing.
Additional Resources
Learn to build financial models with our financial modeling course. Our online courses will teach you everything you need to know to succeed.