The Lesser Spotted Styles Manager

One of the core principles of Best Practice Financial Modelling in Excel is the use of purposeful styling. Font size, style and colour, cell fill and borders should indicate the purpose of a cell rather than being applied haphazardly as “make-up” on a spreadsheet. Formatting should convey useful information to the user, such as which cells contain inputs, where sections start and end and which rows contain totals.

Using the Styles Manager for consistent formatting

When delivering training, one of the first things I check when we start building a case study model is whether participants are familiar with Excel’s built-in Styles Manager functionality. I am always surprised by how few regular Excel users are aware of this tool, let alone are actually using it.

The Styles Manager allows you to save pre-defined styles in the back-end of an Excel file, enabling any user of the file to access and apply consistent styles across the workbook. Styles can also be easily copied to other workbooks, enabling consistency across spreadsheets used by a team or organisation.

Where to find the Styles Manager

The Styles Manager can be found in you ribbon under Home -> Styles -> Styles Manager.
You can also access it by using the shortcut Alt + H + J.

Customising the Styles Manager

The Styles Manager of any new Excel workbook contains some pre-populated styles (e.g. ‘good’, ‘bad’, ‘neutral’, ‘input’, ‘calculation’, etc.). We find these of limited value in financial modelling and prefer to use our bespoke, in-house template.

To create a bespoke style, simply choose any cell in a workbook and apply the formatting (e.g. fill, font, borders) which you would like to include in your defined style. Next, select the relevant cell, open the Styles Manager (using the down arrow in the bottom right corner of the Styles group) and select ‘New Cell Style’. Alternatively, you can use the shortcut Alt + H + J + N.

The Style menu which pops up allows  you to name your new style, as well as select which properties from the selected cell should be included in the style. In the below example, we have unchecked Font and Number, which means when we apply the style to a range, those properties will not be amended, but the Alignment, Border and Fill properties will be amended according to the parameters of our Style (in this case, a light blue fill and outside borders).

To use this newly created style in your workbook, simply highlight the relevant cell(s), go to the Styles Manager (you can use the shortcut: Alt + H + J) and use the arrows to select the style.

Top Tips

• You can easily copy styles between workbooks. Simply copy a cell where the style has been used and paste it into the destination file. The style will automatically appear in the Styles Manager.
• You can save a workbook containing your customised Styles Manager as a template in your Custom Office Templates folder. This way, whenever you create a new workbook, you can select this template from the ‘Personal’ section under ‘New’ as shown in the screenshot below.
  

A word of warning

Whenever you copy a sheet or section into a new workbook, all used Styles will automatically be incorporated in the Styles Manager of the destination file. This can lead to corrupt Styles Managers which cause performance issues, especially when files are used for a long time. A corrupt Styles Manager can contain thousands of meaningless styles as shown below. Often the number of styles can run into the tens of thousands, far more than can be manually deleted (I learnt this the hard way many years ago!). If you do encounter a corrupt Styles Manager, the best way to resolve this is by using a simple VBA subroutine to delete unused styles – you can find these online or if you reach out to us we’ll happily share ours.

That’s it for now…

If you found this article useful, follow our page on LinkedIn to be notified of our future posts. Be sure to keep an eye out for our Tuesday Tips, we’ll be sharing one of our favourite Excel tricks every week.