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

Name

Apr-15

May-15

Jun-15

Jul-15

Jack

1000

1200

1000

800

John

2000

1900

1800

1500

Jim

3000

3300

3600

3600

Jake

4000

5300

8760

9000

Total Sales

10000

11700

15160

15200

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

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

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

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

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

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

1. Click on Edit Rule:

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

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

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

If you would like to learn more about Excel why not take a look, at our Excel Training Courses. Or email us if you have any  questions  at info@ptr.co.uk.