Charts and Dashboards: Charting Example – Extended Case Study Part 2

Welcome back to our Charts and Dashboards blog series.  This week, we continue to take a look at an example of a chart, covering some of the intricacies behind the scenes with its workings.  This week, we consider custom number formatting.

Hopefully, you remember last week’s chart.

This was supported by the following assumptions and underlying data:

This week, let’s consider the formatting we’ve used here.  The formatting of numbers within charts and dashboards is very important.  It is essential to make sure end users know what they are looking at unambiguously.

Here, we’ve ensured that inputs (cell G10, together with rows 12 and 13) are formatted differently to any calculations so that even at a glance you can spot the difference between the assumptions driving the calculations and the calculations themselves.  Whilst sometimes it is recommended that calculations are kept separate from inputs, it can be useful to have certain calculations alongside inputs to allow sense checking of the inputs as they are entered.  When this is the case it is very useful to be able to tell the difference immediately.

Now let’s consider number formatting.  It’s important to note that this only changes the appearance, not the underlying value, of a cell.  For example, if cells B3 and B4 had the number ‘1.4’ typed in both times but were formatted to zero decimal places, then the formula in cell B5 = B3 + B4, would present 1 + 1 = 3 (well, 1.4 + 1.4 = 2.8 anyway), viz.

Excel has many built-in number formats that are fairly easy to understand, e.g. Currency, Date and Percentage.  The default format is ‘General’ where Excel will endeavour to provide the most appropriate format for the contents.  For example, typing ‘3 3/4’ into a cell will result in Excel selecting a mixed format.

This may be seen by Home -> Format -> Format Cells…(CTRL + 1):

Here, we have used custom number formatting in rows 12 to 14.

All clear?  Excellent.  If not, then let’s dive into the wonderful world of Excel custom number formatting.

The ‘Type’ input box here allows up to four aspects of formatting to be specified in a cell.  These aspects are referred to as sections and are separated by a semi-colon (;).  To ascertain what is contained in each section depends on the total number of sections used, viz.

We’ve used all four sections in our formatting of the metrics, so the first set of characters is for positive numbers, the second for negative numbers, the third for any zero values and the final is for text.  But what exactly do all of these characters mean?  The following tables from Microsoft help us to understand these.

For numbers:

For text:

For dates:

For time:

Or for miscellaneous:

In our example, the custom number formatting is as follows:

We’ll want to look closely at some of the Number, Text and Miscellaneous formatting code:

• as discussed above, the first section, _(#,##0_), specifies the formatting for positive numbers

• in this case, positive numbers will be formatted to display no decimal places, showing thousand separators where necessary.  Note the use of _( and _) around the number formatting.  This is not necessary, strictly speaking, but ensures there is space made for an open and close bracket, even though there is no such character shown.  It is done so that positive and negative numbers will still be aligned, making visual comparison easier.  #,##0, ensures positive numbers contain thousand separators (where needed) and display the number to the nearest whole number

• the next (second) section, [Red](#,##0), specifies the formatting for negative numbers.  It is similar to the first section, but colours the number red and encloses it in brackets

• the third section, _(-_), specifies the formatting for zero values.  This will use a dash to denote zero.  Using a dash to denote exact zero values is useful as it will distinguish them from something which is approximately zero

• finally, the fourth section denotes how text is to be displayed.  In this example, we’ve left it blank as text is not expected in these cells and thus should not be displayed.

Using appropriate number formatting will help make your charts and dashboards easier to understand and read.

That’s it for this week.  Come back next week for more Charts and Dashboards tips.