ICAEW.com works better with JavaScript enabled.
Exclusive

Excel How To

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

Author: Simon Hurst

Published: 16 Aug 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

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.

'How to' series

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.

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

Speed up formatting

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:

Excel screenshot

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:

Excel screenshot

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:

Excel screenshot

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:

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250