Tables are used when you want to bring attention to the actual numbers, while charts are used when you want to visually show these numbers.
However, in order for a table to be effective in presenting the data, proper layout and organization is key.
In the table below, my attention goes to the lines more than the text.
A better way of presenting this information would be to highlight the totals in a light color and bigger font. This way you can get rid of the connecting lines and the table would still be easy to read. Also, the totals can be placed at the top of each section so that one glance already gives you the summary of the numbers with the breakdown just below each section.
Tables with a large amount of data pose a challenge of organization to get the message across. The table below nails it on an organizational perspective, but I personally think that the lines are a bit distracting since they attract my attention first before the text.
One way to reorganize this table would be to bring focus on the sections and the labels. Instead of using lines to connect the categories to the values, white space can be used. I would also use a light shaded background to highlight the totals of each section.
This table can be further enhanced by adding a bit of color to indicate key implications.
This can be done using data bars or symbols.
Featured Course
Fundamentals of Financial Analysis
Using data bars
Conditional formatting is used to add data bars to the table. To start, add a new column that contains the values of the cells you want to use. In this example, we want to visually present the increase and decrease % (column N). Do this by using direct cell references to column N, and setup the formula where it shows as blank when there is no value in column N.
Cell P6 = IF(N6=””,””,N6)Pull this formula down to the last row of the table.
Keep these cells highlighted and go to Conditional Formatting under the Home tab.
There are predefined formatting options which you can use, as well as an option to define the format yourself by clicking on the More Rules option.
In the New Formatting Rule window you will be asked to specify the appearance of the bars, including the colors of the positive and negative values. To change the color of the positive value, change the color under the Bar Appearance section. For the negative value, click the Negative Value and Axis button.
In this example, we use light green for the positive values and light orange for the negative values, and have set the axis color to white. To hide the values in the cells and only display the bars, put a tick mark for Show Bar Only.
These data bars give color to the report and bring attention to the bigger positive and negative differences.
Using symbols
An alternative to the data bars would be to use symbols to highlight the positive and negative deviations.
This is done directly through custom formatting.
First off, decide on the symbols you want to use.
Go to Symbols under the Insert tab and you can pick the symbols you want to choose.
In this example, I used the Geometric Shapes subset of the Arial font.
Click on the symbol and click Insert to add it to the sheet so that you can easily copy and paste them.
Put values in column O by using a cell reference to column N. You can skip the part of adding an IF () statement for blank cells since you use Excel’s custom formatting to define how to show the zeros. (Here is a link to the detailed walkthrough of custom formatting)
Cell O6 = N6Pull the formula down to the last row. Highlight these cells and do a right click and select Format Cells.
Another way to do this is to press CTRL + 1.
In the Format Cells window, go to the Custom category where you can define the formatting of your cell.
There are four sections of the formatting rules:
- Positive values
- Negative values
- Zeros
- Text
These sections are divided by an Excel separator, the semicolon (;). You have the option to fully decide on the formatting of all four. If you remove all the defined formatting and leave the semicolons, you will notice that the values in column O are no longer visible even if the numbers are still there.
In this example, want to use ▲ for positive values and ▼ for negative values. Highlight the same area in column O and go back to the Format Cells window (CTRL + 1). Define the formatting by writing this in the Type box:
▲;▼;The last part is left empty since we don’t want the zeros to be displayed in the table. After clicking OK, it should now replace the values with the symbols.
You can further format these symbols by using colors. To incorporate this, go back to the Format Cells window (CTRL + 1) and add the color in square brackets ([]) before the symbol:
[green]▲;[red]▼;Note that the color names are dependent on the language of your MS Excel. If you are using a different language, make sure to use the color name translated to that language.
If you want to use a color that is not among the standard colors, you can use color codes from this link and incorporate them in the formatting definition.
For this example, use color #43 for the positive value and color #53 for the negative value. Highlight the cells in column O and go to Format Cells (CTRL + 1). Instead of using [green] and [red], replace it with the color code:
[color43]▲;[color53]▼;This is also language-dependent so translate the word “color” to the language of your MS Excel and put that text in.
Click OK.
You can do further formatting changes with formatting (e.g. alignment, font).
As an alternative, the ■ can be used using the same steps to achieve this look:
Here is a before and after comparison of the tables. The latter brings some color to the report and makes the report easier to read. It directs your attention to the positive and negative changes.
Feel free to Download the Workbook HERE.
Further reading
Graph Versus Table
Alternative to Doughnut Charts
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.
