Excel Dashboard Tutorials - How to add up and down arrows to metrics

Overview

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.

Dash1.png

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.

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit