Save content
Have you found this content useful? Use the button above to save it to your profile.

Give your reports more oomph 1: Number formats

by
16th Jul 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

In a return to spreadsheet design and presentation techniques, Simon Hurst begins his exploration of report formats with a look at numbering conventions.

Introduction

You are likely to have to put a lot of work into preparing the information for your Excel reports and ensuring that it is correct, so it would be a shame if your final report fails to deliver the maximum possible clarity and impact. In this series we will look at a variety of ways to improve reporting, including the secrets of Excel number formatting through to replacing printed pages of figures with interactive on-screen reports. The first part of the series looks at number formats.

Why number formats matter

Number formats may not seem to be the most exciting way to start our quest for management reports with added oomph, but using the most suitable number format can make a significant contribution to the clarity of your reports. Ideally, you want to avoid irrelevant detail detracting from the important information. A couple of obvious examples would be to display figures rounded to an appropriate level of precision. In most cases, if your figures are in the millions, displaying pence is likely to be irrelevant and will reduce the overall clarity. It might even be better to round to thousands or even millions:

Rounding figures in Excel to thousands or even millions

Custom number formatting

It's also important to get the alignment right. As well as displaying unnecessary decimal places, the leftmost column above also uses a format that doesn't display a fixed number of decimal places, this means that the numbers are not evenly aligned. For example, at first glance the figure for Motor and travel on row 47 seems lower than the figure for Telephone and communications on row 46. This is relatively easy to sort out by using a specific number format rather than the 'General' format. However, there is an alignment issue that is less straightforward to deal with: ensuring that when negative figures are shown bracketed using a custom number format, the digits of positive and negative numbers are correctly aligned. In fact, the Excel custom number format has a format code that allows for this. Including the underscore in a format code inserts a space equal to the width of the character following the underscore; so _) will leave a space equal to the width of a closing bracket. The following number format code shows this in use:

#,##0_);[Red](#,##0)

The above format rounds to round pounds. To round to thousands you add a comma after each zero:

#,##0,_);[Red](#,##0,)

For millions, you add two commas:

#,##0,,_);[Red](#,##0,,)

Number format codes have four sections that control four different types of value: positive, negative, zero and text. In the above example we have just entered the codes for positive values and negative values, separated by a semi-colon. The positive value code uses _) at the end to leave our closing bracket space to balance the closing bracket that will be shown for all negative values. We can also see the use of the comma to add comma separators for each group of 3 digits and the use of a colour in square brackets at the beginning of a format section to change the font colour of the number.

Where many of the figures you are reporting on might be zero, it can also help to display zeros as a dash rather than 0. The third section of the custom format takes care of this:

#,##0_);[Red](#,##0);-?

If you wanted your dash to be very precisely aligned you could use the underscore code we've just looked at, but if you just want to move it in slightly from the right-hand side, you could just use the ? to stand for a standard width space.

You might think this would mean that you would no longer see zeros in your list of numbers but, as you can see below, this is not necessarily the case:

Custom formats only apply when the value is zero

The custom format section only applies when the value is zero. For Germany and Austria, the value is not zero and the display is only rounded to zero by the number format. As you can see the custom format section applied is based on the unrounded figure.

It is possible to sort this out by using an obscure feature of custom number formats – the ability to build in conditions for the first two format sections:

[>=0.5]#,##0_);[Red][<=-0.5](#,##0);-?

Custom number formats let you apply conditions for the first two format sections

When using conditions in this way, the first two sections are determined by the conditions or by the default positive/negative if no condition is specified. The third section is applied to all numbers not caught by the first two sections. In our example, rather than just zeros being formatted according to section 3, section three will be used for all numbers between -.5 and .5.

Using custom number formats

Having gone to the trouble to understand and set up the custom formats that you want to use, it makes sense to make these formats easily available. Where a custom format exists in a particular workbook, you can make it easier to apply by using an Excel Cell Style. If you look at the Cell Styles gallery in the Styles group of the Home ribbon tab, you will see at the bottom of the full gallery a section for Number Format Styles. These Styles can be applied using the existing commands in the adjacent Number group of the Home ribbon tab:

Number styles can be selected from Number group of the Home ribbon tab

As an example, rather than using the Comma Style command to apply the default number format, you can modify the Style to use your own custom format so that it can be applied just by using the existing Comma Style button. To do this, you need to right-click on the Comma option in the Number Format section of Cell Styles and choose Modify. You can then use the Format button to display the Format Cells dialogue and in the Number tab choose the Custom category and choose an existing custom format or set a new one up:

Custom category options can be found in the Number tab of the Format Cells dialogue box

Number formats and templates

So far so good, but our custom number format is still only available in the workbook in which we set it up. To make it available to future new workbooks we would have to include it in any relevant templates. To make it available to future 'Blank' workbooks we could add it to the Excel default template. Unlike Microsoft Word, Excel will work quite happily without a default template, but if you save a template with the name Book in the XLSTART folder, this will become the default template that is used whenever you create a new workbook using the New button or the Control+n keyboard shortcut.

Next time

In the rest of the series we will be looking at Conditional Formatting, graphics including Sparklines and the Excel Camera, and using a range of Excel tools and features to create interactive reports that allow the user to explore the relevant information for themselves.

Replies (9)

Please login or register to join the discussion.

avatar
By Alf
20th Jul 2015 10:17

Great article

Thanks

Thanks (0)
Simon Hurst
By Simon Hurst
20th Jul 2015 11:04

Glad you liked it

Thanks Alf - always nice to know whether or not an article has been useful.

Thanks (0)
avatar
By scorbett
20th Jul 2015 13:33

Problem with formatting 000's

This is great - I have always put a formula in to divide by 1000 and an if statement to round, however when I tried to alter the format to show anything under 500 as a '-' rather than a '0' I got some strange results.  The format I used was

[>=500]# ##0,_);[Red][<=-500](# ##0,);-?

However a figure of 400 shows as "-400" and -400 shows as "--400".  What am I doing wrong?

 

Looking forward to the next article on this.

 

Thank you

Thanks (0)
avatar
By scorbett
20th Jul 2015 13:36

Correction to above

Hi, just played around a bit and noticed if I put a ',' after the '?' it corrected by problem.

Sorry

 

Thanks (0)
Simon Hurst
By Simon Hurst
20th Jul 2015 20:55

Conditional number formats

Well worked out - there is something definitely odd about how the conditional number format behaves with rounding to thousands.

Thanks (0)
avatar
By janesorganic
22nd Jul 2015 11:10

Conditional number formats

Thanks Simon, that's really useful. I too have used rounding formulae; and have "cribbed" some formats our auditors use in their templates.Now I understand what all those squiggles were doing. I too look forward to the next article.

Thanks (0)
avatar
By DMBAcc
21st Aug 2015 16:29

When you live in Cornwall ....

...it is not easy to get to excel training - certainly not cheaply, so to hear from a practitioner who understands what "simple" people like me really need is extremely helpful - thanks.  Just read your second posting on conditional formating - didn't realise I had all that power at my finder tips !!!  Thanks again

Thanks (0)
Simon Hurst
By Simon Hurst
21st Aug 2015 16:51

I've always wanted to do another lecture in Cornwall...

Thanks DMBAcc. Glad you found the posts useful. It's a while since I actually worked in practice although most of my clients are accountancy practices which helps me know what is useful. Many years ago I lectured at Carlyon Bay a couple of times. I'd always be happy to travel down to the West Country if there was enough demand!

Thanks (0)
avatar
By DMBAcc
21st Aug 2015 16:59

you'll get my vote...

not the best place in Cornwall - you could always try one of the hotels out of season on the North Coast - great views

Thanks (0)