Excel how to: speed up formatting using Excel Styles. Part 2

Introduction 

As part of each article, we will be scouring the extensive Excel Community archive to provide links to additional details and ideas.

This is the story of the series so far:

Speed up formula entry

• Excel how to: speed up entering formulae part 1 - using the dollar signs to fix all or part of a reference
• Excel how to: speed up entering formulae part 2 - using Range Names to make formulae easier to enter and understand
• Excel how to: speed up formulae - using Excel tables – creating dynamic references by referring to Table columns and using Table structured references to make formulae easier to understand
• Excel how to: speed up formulae - using Excel tables part 2 – adding calculated columns to an Excel Table
• Excel how to: speed up entering formulae – Dynamic Arrays – how the recent introduction of Dynamic Arrays can dramatically reduce the number of formulae you need to enter in the first place

Speed up formatting

• Excel how to: speed up formatting using Excel Styles – using Excel Styles to format Excel cells with a single click and to help implement the ICAEW 20 Principles for Good Spreadsheet Practice.

Excel Number Styles

Last time we looked at the use of Excel Styles in general. This time we are going to focus on the built-in Number Format Styles. Beyond the obvious consideration that they apply specifically the number element of a cell format, the Number Format Styles have another useful attribute – they are linked to buttons in the Number group of the Home Ribbon tab. As we can see here, the comma button in the group applies the Comma Style:

Hovering over the % button will show that it applies the Percent Style. However, hovering over the other, dropdown, button displays a help tip headed ‘Accounting Number Format’. In fact, like the other two buttons, this button will also apply a style: in this case, the Currency Style.

The fact that these buttons are linked directly to styles means that it is easy to customise what they do in order to apply your chosen number format. As we saw last time, we can modify any existing style by right-clicking on it in the Style Gallery and choosing Modify. The Number tab allows the number format to be set using any of the number format categories, including Custom:

We have discussed the importance of number formatting, and the details of how to set up a custom number format, many times within the community. Links to two of the articles in the Accountants’ Guide series are included in the Related links section below.

We are allocating our Custom Number format to the Comma Style. Note that there is a Comma and a Comma [0] style. The [0] versions of the Comma and the Currency styles indicate versions of the formats that exclude any figures after the decimal point. The custom number format used includes brackets for negative numbers which are also shown in red, and replaces zeros with a dash:
#,##0_);[Red](#,##0);-?

The first article in the list of links explains in detail what each element of the custom format does, but, in brief, the three sections of our format are delimited by the ; and represent the formatting to apply to positive, negative and zero values respectively. The # signs are used as placeholders so we can show that we need to use a comma as a thousand separator. The 0s indicate that, where there are figures after the decimal point and the value is less than 1, the results should be shown as 0.xx rather than just .xx. The underscore causes Excel to leave an amount of space equal to the character that follows, so our _) leaves exactly the amount of space after a positive number for it to line up with a bracketed, negative, value. Finally, -? causes zero values to be displayed as a dash, inset one space from the right-hand side:

Note that modifying one of the special number styles will change the format of all cells that have previously been formatted by clicking the associated button in the Number group.

Styles and templates

Useful as Excel Styles are, unlike Word Styles they are only set for the active workbook. If you want to make a style more widely available, you will need to include it in a suitable template. The second article in the Related links section explains this more fully.

Related links:

• Accountants' guide to Excel - mastering number formats part 1
• Accountants' guide to Excel - mastering number formats part 2