Excel dashboard detailed report

Data tables in reports are often difficult to read and analyse. To make out which product or business areas are performing better than estimated, can often require some time going back and forth between the numbers. The smart use of symbols and colour can considerably assist in analysing data by pointing out areas or sections that need attention. People are also more likely to remember the numbers this way.

How to design your Excel Table reports for better readability

The first rule is: Avoid a colour collage in your Excel reports. Instead define thresholds for formatting.

Sometimes I have seen people use an all-or-nothing approach. Either they have reports that look like

Excel traditional report data table

Or like this

Excel report too much conditional formatting

In order to avoid a patchwork outcome, direct the eye to sections that fall out of a specific threshold. It’s much easier to examine the data and also points out the areas that need attention, leaving the rest alone. Too many colours deflect the attention from the figures to the formatting rather than directing the analysis to key areas.

Incorporate dashboard techniques in your Excel reports

The below example takes out the best out of the two worlds.  Notice that parts of the table don’t have any colour or symbol formatting. This is dynamic and controlled based on a given threshold. The figures that fall within this threshold will not be formatted, thus avoiding a colour collage.

Excel dashboard detailed report

You can achieve this with a simple formula that most Excel users are familiar with. A simple IF() does the job, add your choice of threshold and stir in the symbol of your liking and you have a report that can be easily interpreted by anyone.

How to use symbols in your Excel formulas

The use of symbols in Excel formulas is often overlooked by most users. It’s quite easy to get them in. There are two methods

  1. Put the symbol in a specific cell in Excel and refer to it
  2. Copy and paste it directly in your formula

From Excel Ribbon, Insert / Symbol, pick the symbol of your liking and Insert. Now you have the option to use method 1 or method 2 above.

Excel how to insert symbols in Functions

Get Excel’s conditional formatting effect without using conditional formatting

To make your thresholds more transparent and easily maintainable you can use a simple formula that does the following:

IF the difference between Actual and Budget is less than 10 (The threshold defined in cell F1) then show symbol in cell C2, otherwise show nothing. A similar formula is written in the I column, except the signs are changed.

Dashboard report in Excel using symbols

Using this method instead of the conditional formatting approach has the advantage that you can easily update your threshold if it changes. For large reports, conditional formatting can slow down your file, making the use of formulas more advantageous.

Find out about other techniques on how to make your tables more readable by visiting an Advanced Excel course or a Dashboard course that is customized to your reporting needs.

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!