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.

However, 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:

The 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

The chart will probably look something like the one below:

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.

                                

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.

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”

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

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

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.

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.

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:

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

Related articles

• How To Make Awesome Ranking Charts With Excel Pivot Tables (moz.com)
• Dynamic Charts using Excel Filters (alesandrab.wordpress.com)
• Tired of crowded bars? The dot plot hits a sweet spot! (inspari.dk)
• Creating More Professional-Looking Graphs Using Microsoft Word And Excel (camerona2650.wordpress.com)
• Australia Excel Bar Chart (visual.ly)