Never use simple numbers in your dashboards (bonus tip: how to fix default conditional formatting)

Pop quiz: What is wrong with below report?

At first glance, it looks alright. But if you observe closely, you realize that it is not telling the entire story. Just looking at regional sales numbers, you have not much clue what is going on with them.

So how to improve it?

1. Add context

In order to know whether a number like $120,000 sales in South is good or bad, you need to provide some context. For example, if you include previous month sales figures, suddenly $120k is comparable to some other number. This tells a better story than a simple number alone.

You can also try these,

• Target values
• Same month last year values
• YTD, QTD values

2. Add % Change

When you have 2 numbers like $120k and $110k in a report, anyone looking at them are going to mentally calculate the % change from last month to this month. This is easy for numbers like 120 and 110, but if your numbers are like 36,450 and  43,150 then calculating % change values will take time.

Why force your audience to do this mental math? Instead show these %s on the report.

3. Highlight bad numbers

Another way to enhance your report is to highlight poorly performing regions. Since each region is different, comparing sales of one with another is not good. But you can compare % change (from previous month / same month last year / targets etc.) and highlight poorly performing regions. This can be done with conditional formatting.

So lets go ahead and do it for our report above.

3.1 Add conditional formatting

Just select the %change column, go to conditional formatting > icon sets > and choose an arrow icon set that you fancy.

3.2 The default formatting kinda sucks

We are not done yet. If you look at the default icon formatting, it looks in-accurate. We are seeing red colored, down-ward arrows even when there is a positive change. And, when the % change is negative, we no longer need minus sign (-) because it will be indicated by down arrow.

3.3 Fix the conditional formatting icons

Select the cells again, go to home > conditional formatting > manage rules. Select the rule and edit it (you can double click on the rule to edit).

Change the rule type as shown below.

3.4 Remove the minus sign

Select the %change column once again, go to format cells (ctrl+1) and set the custom formatting code 0%;0%

This will make sure that even when the percentage is negative, Excel will not show the sign (minus symbol).

Related: More on custom cell formatting in Excel.

So there you go. A regional sales report that tells better story.

Key ideas to keep in mind

In your reports, try to provide as much context as possible. This can be done by

• providing comparisons
• including additional statistics (sum, count, median etc.)
• indicating the time frame of the report
• highlighting bad numbers or areas that require attention
• giving user a choice to change report criteria (interactive features).

Do you follow these principles when making reports or dashboards?

I try to observe these ideas in all my dashboards. What about you? Are you using simple numbers in your dashboards?

Go ahead and tell us how you are making your dashboards better, in comments.

Analyze data and make reports / dashboards often?

If your job involves data analysis, reporting & dashboards, then you will love our Excel School program. In this online course, you will learn how to use Excel to analyze data with formulas & pivot tables, highlight important stuff, create stunning charts & tables, make them interactive and put everything together to weave an informative dashboard & more.

Please click here to know more about Excel School program and join us.