In the previous lesson we built a simple ‘list style’ dashboard in Excel.
In this lesson we’re going to add an up and down arrow to each row to indicate the movement of each metric
Setup the movement arrow
Adding arrows is straightforward, using conditional formatting we can set up the arrows to update automatically when new data is added.
Open the Dashboard template from the previous lesson (here)
Select the data in the Movement column i.e E6:E24
Go to the conditional formatting menu on the Home tab. Select Icon Sets, and ‘3 Arrows’
This will add arrows using the default conditional formatting settings, which are incorrect. We need to edit them. Go back to the conditional formatting menu and select Manage rules
Either double click on the rule, or click and select edit rule
The default settings will look as follows, amend them to match the second image.
By default, Excel shows the top ⅓ of results with an up arrow, and the bottom 1/3 with a down arrow. We’re changing this so that anything with a movement greater than 0 has an up arrow and less than 0 has a down arrow.
Click OK, then Apply.
The arrows are now ready to use on your dashboard and will update whenever new data is added.
In the next tutorial we will look at how to add red/amber/green status to your metrics where there’s a target or threshold in place.