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
Or like this
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.
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
- Put the symbol in a specific cell in Excel and refer to it
- 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.
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.
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.
The new Excel Dashboards course is here!
Now available on Udemy
Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.