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:
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:
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);-?
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:
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:
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.