Adding Trend Arrows to Excel Workbooks

The following question was put to me last week:

“In the following table there are month on month totals (perhaps for sales). I would like to show the red, yellow and green trend arrows against each value to show the change against the previous month’s value. Using the same logic I need to show the same coloured arrows for the same data expressed as a percentage change.”

In other words, changing this chart...

...into this one

Here is a step-by-step guide to how this was achieved using the Excel Conditional Formatting menu.

1. Insert some extra columns for the indicators to be placed in:

2. Insert the following formula into the empty cell to the right of the Jack May-15 revenue cell (F):

                =IF(ISERROR((E4-C4)/C4),0,(E4-C4)/C4)

3. Copy the formula to all of the other empty indicator cells:

4. Select all of the new cells in Columns D, F, H & J.

5. Select the arrow indicators from the Icon Sets list on the Conditional Formatting menu:

6. With the columns still selected go to the Manage Rules option on the Conditional Formatting menu:

7. Click on Edit Rule:

8. Set the top value to 0 and change the Type to Number and the operator to >. Set the bottom value to -0.00001 and change the type to Number and the operator to >=.

9. Click on OK. Then click on OK on the Rules manager window.

8. With the cells still selected click on the % format option on the Number section of the Home ribbon to format the numbers as percentages.