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

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.

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.
Learn More
Excel dashboards course cover

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 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.