Table of contents
Introduction
Speedometer graphs, also called tachometers or gauge charts, are very popular in dashboards for their ability to quickly represent a KPI in relation to a target. They display a needle illustrating the level of performance pointing to a colored zone (usually green, yellow and red) that sometimes match target levels for that KPI.
These charts can be a popular choice for striking visual reports with simple indicators, particularly in sales or operational performance tracking.
Excel doesn't offer this type of charts natively, but it's something not too complicated to build! In this article, we'll see how to create them from scratch.
Create a speedometer chart in Excel manually
Overview
Creating a speedometer chart in Excel is not really intuitive, but it is pretty easy once you know how to do it. Since Excel does not have a standard speedometer chart, you will have to build it with other type of charts.
We will need 3 charts: 2 doughnut charts and 1 pie chart. What for? Because the standard speedometer chart is usually composed of 3 different parts :
The pointer
The color scale
The value scale
Here is an example:
Now, of course different types of speedometers exist but today we're going to see how to build that type of speedometer from scratch.
Step 1 : Create the color scale
The color scale is supported by quantitative data. Each color is linked to a value that determines its length. The omitted is for the bottom of the chart that we will make disappear later.
Select your table and insert a doughnut chart as showed below:
The purple section is the bottom of the chart that we will we want to later omit, the current priority is to put the color scale at the top of our chart. We need to change the rotation by opening the Format Data Series feature by selecting the graph and pressing Ctrl + 1. Once you've done that, change the Angle of first slice to 270° as demonstrated below:
Here you have your first doughnut chart with the color scale!
Step 2 : Insert the value scale
To insert the value scale, it is kind of the same procedure. Meaning that it is also supported by quantitative data which is:
Do the exact same manipulation as before to create another doughnut chart, but this time select the whole table. Don't forget to change the Angle of first slice to 270°.
You will have this graph:
Now, we want to merge this doughnut chart with the already existing one containing the color scale. To do so, select it, copy it using Ctrl + C and paste it within the color scale doughnut using Ctrl +V.
Here is what you should have for now:
Step 3: Change the colors of the outer doughnut
We can see that the outer doughnut has the same colors as the inner one, we want to change that to differentiate the two bands and chose a coherent set of colors for the scale : select the outer doughnut (the scale) go to Chart Design, Change Colors and choose a monochromatic palet going from light to dark. Here we chose blue but you can chose another one :
And then manually reapply the correct colors (red, orange, yellow, green) for your inner doughnut (the color scale) by clicking through each section and choosing the colors in the Format Data Point feature:
Step 4: Delete the lower parts of your doughnuts
Once you've formatted the right colors, it's time to make your doughnut charts truthfully look like the different parts of your speedometer chart. To do so you need to make the bottom part of your charts disappear.
Select the lower parts of your doughnuts (one at a time) and once again go to Format Data Point and choose the No fill option :
Do the same for the outer doughnut and this is what you should have by now:
Step 5: Finish formatting the doughnuts
Make your speedometer thicker by changing the Doughnut Hole Size to 40-50% in the Format Data Series feature, the lower the percentage the thicker the doughnut will be:
You can also chose to remove the legend if need be.
Step 6: Create the needle
Now, we will need to create the last element of your speedometer chat which is the needle. It is also supported by quantitative data which is:
By the way, the "Rest" is the total of the color band (200) minus the pointer minus the thickness.
Now, to create your pointer, you need to insert a doughnut chart the same way you've done twice before. But before pasting your needle chart into your already existing speedometer chart, make sure that it is under the right format, that is to say:
The right angle (270°)
The right colors (monochromatic, from light to dark)
The right thickness (40-50%)
Here is the graph you should have before pasting it into your speedometer chart:
Step 7: Format the needle
To properly format the needle, it is necessary to transform the doughnut chart into a pie chart. To do so, select the chart needle and go to “Change Series Chart Type” (right click):
Press on "Ok" and your Pie chart will appear. Select it and go to Format Data Series to change the angle of the first slice to 270° as you've done a few times now.
You also want to make the blue sections disappear, meaning showing only the needle part. Go to Format Data Point and set the Border to No line and the Fill to No fill.
Here is what you should have by now:
If you want to make the needle appear thicker, don't hesitate to change the value of the "Thickness" within your table.
Step 8: Apply labels
Select your outer chart, go to Chart Elements and select Data Labels then click on more options:
Select Value From Cells , select the data label from your scale table from 10 to 0 and deselect “Value”:
Here is what you should have:
Don’t forget to manually place the labels at the top of each segment.
Step 9: Show the value of the pointer
To place the cherry on top, you can show the value the needle is pointing at. Insert a text box , and simply enter the cell that contains the value of the pointer in your table in your formula bar. Don't forget to use the "=" first.
You can format the text and you’re done ! Enjoy using your speedometer chart !
Here are some different examples:
Insert ready-made speedometer charts in Excel, PowerPoint or Word using the Power-user add-in
A speedometer chart ready to use, in seconds
You can easily see that this process is not that complicated, and for a one-time need it's fine. But it's not something you want to redo too often as it takes time and effort.
This is where Power-user can make your life easier. Our Microsoft add-in lets you insert a speedometer chart at the click of a button, without having to worry about calculations or manual adjustments. What's more, you have multiple charts with multiple designs and it automatically adapts to your corporate design, ensuring perfect visual consistency with your other reporting elements.
In just a few seconds, you'll have a speedometer chart that's ready to use, customizable and dynamic, effortlessly. Less time spent on formatting, more time to analyze and present your data effectively!
More charts you can instantly create with Power-user
Speedometers are just one of 100 charts you can create with the Power-user add-in. The tool has 100 ready-to-use charts like Mekko charts, Sankey charts, Gantt charts, Waterfall charts, and more-- so you can focus on insights rather than formatting.
But Power-user also goes far beyond just charts. It is actually packed with dozens of features to make your life easier and enhance productivity on Microsoft Office:
Thousands of templates, icons, pictures, maps, etc.
Excel automations to clean and organize data and formulas, Unpivot tables, arrange charts, etc.
Link Excel and PowerPoint to automate PPT reports from Excel
Formatting automations for PowerPoint to create tables of content, fix inconsistencies, replace colors or fix titles across the entire presenattion
Etc.