Creating a score meter in Excel

As most people who know me will attest, I am a big fan of using gauge and bullet charts and / or altering the standard excel charts to make them more visually appealing.  So when I was asked by a colleague if I could create a gauge style graph, with an arrow as an indicator of the result (kind of like a linear speedometer), my exact words were, challenge accepted.

To make sure I fully understood what they wanted, I asked them to send me a picture.  This is what I received:


After further discussions, we realised that she wanted a percentage scale going from 1 – 100%.  The blue arrow would move based on the indicator for that month.  There are actually several ways that this chart could be made.  I chose in this instance to make a bar chart.  It could also be done with a column chart, though!  You can download my chart and worksheet here.

Step 1: Set up the data

In this instance, my colleague had only 1 data point she wanted to display, a YTD indicator.

indicatorHowever, to set up the coloured scale behind the indicator arrow, we would need more data to be plotted.

For this reason, I set up two more columns:

extra columnsThe first will be used to created the coloured scale.  In this case, the scale is even, but it doesn’t need to be, it is dependent on the visual aspect the user wishes to show.

The second column is a set of two calculations to create the width of the arrow.

  • E2 = B2 – 0.025 (this will find the value of the indicator minus half of the width of the arrow)
  • E3 = 0.05 (the width of the arrow)

Step 2: Create the basic stacked column graph

First highlight the data in column D, then click on Insert > Bar > Stacked Bar

stacked bar

The chart will probably look something like the one below:

1st chart

We need to adjust the way the chart is displaying the data.  Right-click on the graph and then choose “Select Data”.  Click on the “Switch Row / Column” button and press the OK button


You should now have 5 series as shown in the chart below.  Adjust the colours of the series and the chart itself as you wish.  I added a grey gradient to the chart fill, removed the X axis, horizontal lines and legend and then adjusted each series so that it matched the traffic light idea with a white border.

second chart                                3rd chart

Step 3: Add the indicator arrow

For this we need to add 2 more series.  First click on cell E2 and press Ctrl+C.  Then click on the graph and press Ctrl+V.  The graph will automatically adjust to show the new data.

new data

Click on the new series, and press Ctrl+1 to bring up the formatting menu box.

Choose to plot the series on the secondary axis in the “Series Options” tab and give it a gap width of “0”

secondary axisBefore pressing the close button, go to the fill tab and choose no fill.  Then you can close the formatting menu box.  The graph should now look like the one below:

4th chart

Repeat the process, this time selecting cell E3 and copying and pasting it into the chart.  Your graph should look something like this now:

5th chart

The next step is to adjust both of the vertical axes so that the maximum scale is 100% for both.  To do this double click an axis.  In the format axis menu box ensure that the Fixed Maximum = “1”


Repeat for the other axis.

6th chart

You can now delete the top axis.

We now need to add the arrow.  To do this first we insert the arrow shape.  Click on Insert > Shapes > Down Arrow.  Then draw the arrow on the page and adjust it to have the shape you want.  You can see mine in the picture below.  I’ve given it a black outline and an 80% transparent white fill.

down arrow

Then click on your arrow and press Ctrl+C.  Click on your seventh series (which you want to replace with the arrow), and press Ctrl+V.  And voila, the chart is complete:

finished chart

Obviously this is a large chart for just one data point, but you can add other points to it as well…


Have fun!

+Alesandra Blakeston

16 thoughts on “Creating a score meter in Excel

  1. Hello, what if I want to have two indicators on the same graph?
    Lets say if I want to have monthly and YTD status on the same score meter?

      1. yes, or like one picture above it.

        I want to have two different “indicators” (monthly and YTD standings) on the same line. If I try to do the second indicator the same way I did the first one, well, the values are not correct (axis range is not the same anymore)…

      1. Hi again,
        I’ve looked through your file and sent you one back. Basically you are putting a stacked chart on the primary axis and a stacked chart on the secondary axis, so your “indicators” cannot overlap. You can however have 2 as long as you calculate the distance between them and set up your secondary stacked chart accordingly. Hopefully this makes more sense when you get the file. Good luck!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s