I’m a late adopter of Cell Styles in Excel. Before I started doing financial modelling and consulting work for other people, I didn’t really appreciate how useful they were.
I wish I’d learned them a lot earlier as they would have been helpful in earlier roles, too.
Using Excel Templates gets you part of the way there
I didn’t use Cell Styles for ages because I thought I had everything I needed with Excel Templates. With an Excel Template you can set custom fonts, number styles and a colour palette.
This means that you can incorporate all of these Excel design tips for non-designers into new workbooks without having to start from scratch each time.
Creating a custom template is easy – you create a document then save it as an XLTX file. You can also set your settings so it defaults to opening from this if you want.
This worked when I was only working for one organisation at a time. I could create a really specific template using that organisation’s colours, fonts and other aspects of brand guidelines, as well as design elements to make it more usable.
However, when I started working for multiple organisations at the same time I found myself creating these template documents each time. So I thought it was time to explore Cell Styles.
Cell styles – functionality as well as design
Cell Styles can be found on the Home Tab and the default set follow Microsoft’s default colours and fonts.
The picture shows my customised version. I’ve deleted what I don’t use, and set up custom cells to cover most modelling eventualities. In this example, I actually have three “calculation” cells. A light one, a darker one for subtotals, and a dark one with horrible orange lines to highlight an inconsistent calculation.
What you can’t see in the picture is features like default number format and default cell protection. For example, I usually want calculation cells to be locked and input cells to be unlocked. Excel’s default is to make all cells locked, so you have to manually unlock them if you are using Sheet Protection. But if you use Styles then you can allocate cell behaviour at the same time as formatting it.
And this is really important, which I didn’t realise until I started to experiment. Using Cell Styles is as much about building in functionality as it is about visuals.
The Number Format options towards the bottom are also important. These allow you to change what happens when you press the shortcut formatting keys on the Home Tab. For example, % always defaults to no decimal place. You can change this default here.
Modelling consistency and discipline
One of the most common issues with financial models is mixing up inputs, calculations and outputs. Ideally these should be on separate tabs, but it’s not always possible. Using Cell Styles can help distinguish different aspects on the same tab. I find it really helps enforce the discipline of separation when I’m building the model, as well as making it clearer to end users.
“Reskinning” financial models and other workings
If you change the relevant Cell Style, it will update every cell in your workbook that is in that style. So, if you are disciplined about using Styles when you create a model or set of workings, it is really easy to “reskin” those workings.
All in all, using Cell styles helps me to build better models faster, which cuts down the cost to the client.
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.