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 = N6
Pull 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
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:
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:
This is also language-dependent so translate the word “color” to the language of your MS Excel and put that text in.
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.