Stay up-to-date with the latest business and accountancy news: Sign up for daily news alerts

In the first part of our examination of ways to speed up formatting we looked at the use of Excel Styles. This time, we are going to see how three particular Excel Styles can help you apply the best possible number format with a single click.

In this series we will be looking at the Excel tools and techniques that help you accomplish a range of day-to-day Excel tasks more efficiently and effectively.

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:

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.

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.

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

Is this the end for Excel’s SUMIFS() function?

Access to our premium resources is for specific groups of students and subscribers. If you already belong to one of those groups, simply Log in below to access this content.

This content is available to ACA students. If you want to start the ACA qualification there are several routes you can take

Unlock the power of Excel and reduce your risks with practical guidance and support to improve your spreadsheet skills.

Whether you are in business or practice, access cutting edge technical information on a range of topics.

The Institute of Chartered Accountants in England and Wales, incorporated by Royal Charter RC000246 with registered office at Chartered Accountants’ Hall, Moorgate Place, London EC2R 6EA