How to design your Excel Table reports for better readability
The first rule is: Avoid a color 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.
Featured Course
Visually Effective Excel Dashboards
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.
Find out about other techniques on how to make your tables more readable by visiting our courses.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
