Udemy

Learn how to create speedometers (or gauge chart) in Excel

A free video tutorial from Andreas Exadaktylos
Best Selling Teacher | Msc Computer Scientist
Rating: 4.6 out of 5Instructor rating
8 courses
41,720 students
Speedometer-Gauge Chart

Lecture description

In this lecture we will create an amazing workable speedometer or gauge graph.

Speedometers are often used in professional dashboard and provide key performance informations about current indicators.

Learn more from the full course

Excel Charts, Infographics & Data Visualization Masterclass

Master 20+ Advanced Dynamic Excel Charts & Create Impressive Excel Graphs, Infographics & Data Visualization

07:06:05 of on-demand video • Updated February 2022

Create 40+ Powerful Unique Graphs and Infographics that communicate your information in the best manner
Learn Infographics in Excel that you'll not find in ANY other Excel course, guaranteed
Learn next-level dynamic interactive charts with the help of form controls and formulas, and a ton of real-life examples.
Improve your graphs with conditional formatting, custom number formatting, and shapes.
Master Dynamic Charts and understand When, Why and How to use them
Learn from a Best-Selling Instructor with over 22 years of experience with Microsoft Excel, teaching thousands of students in his own computer learning school
Learn the most current version of Microsoft Excel 365
LIFETIME Access to Project Files, Quizzes, Homework Exercises and 1-on-1 Support
English [Auto]
When it comes to understandably presenting data in an Excel chart, stand out there and few charts are specific and can be used to present a specific kind of data, speedometer gauge is one of those charts. An Excel speedometer chart is just like a speedometer with a needle that tells you a number by pointing it out on the gauge, and that needle moves when there is a change in the data. It is a single point chart that helps you track a single data point against its target. Here are the steps to create a speedometer gauge and excel which you need to follow. As I said, we need to insert two donut charts in a pie chart. But before you start to create a speedometer, you need to arrange data for it. In the below worksheet, we have three different data tables, two for donut charts and one for a pie chart. The first data table is to create a category range for the final speedometer, which will help you understand the performance level. The second data tables for creating labels ranging from zero to 100. You can change it if you want to have a different range. And in the third data table, we have three values that we will use to create a pie chart for the needle. The pointer value is the real value that you want to track. The rest value is the sum of these two totals, minus the pointer value and the thickness of the pointer. First, go to the Excel ribbon, click on Insert and go to the Charts Election and under the dropdown click on the donut chart. With this, you'll get a blank chart. Now, right click on the chart and then click on select data in the select data source window from the legend entries. Click on the Add button. Enter category in the name of the import bar. After that, select the value column. From the first data table. That is Selby, three to be seven. Click OK. OK, again, once this is done, you'll get the following donor chart, but you'll notice that this is not a semicircle and it's facing the wrong way. The next thing is to change the angle of the chart for this, right click on the chart and then click on Format Data Series. In format data series pane enter 270 degrees in the angle of the first slice and hit enter. This rotates the chart the right way. Next will hide the data series in the lower half by removing its color, adding data labels and formatting the chart appropriately. So you need to hide below half of the chart for this. Click on only the part of the chart, right click on the bottom half of the chart and select format data point from the bucket icon. Select No Fill. Next, change the colors of the other data series if you feel it's necessary for the rest of the force data points I used for different colors red, yellow, blue and green. Once you're done, you should have something that looks like this. Now, let's add data labels. Right click on the chart and select format data labels. Sunlight values from cell. And slowly, like the labels in the first table. We're finally done with the first chart now for the second doughnut chart. Right click on the chart and choose Select Data. Click on Add Button. A series named Type Legend Entries. As Series Values Select Values column from the second data table. Press, OK. OK, again, once you do that, you will repeat the same steps as the first chart we created. Again, you need to hide low half of the chart using no fill for color. Then the format in the chart colors you feel are most appropriate. Now, add the labels. Once you're done, your chart should look like the one below. It looks like the labels at the top all show the number 10. This is wrong. You need to change the labels to display the labels from the amount labels. Second table. So right click on any of the numbers. Check the box value from cells. As range, select the cells from D3D 13. Also, unchecked, the value blocks. We're in the homestretch. Just one more thing needs to get done, and that is building the pointer and linking it to a data point so that it moves dynamically along with the tables data. Right click the chart again and choose select data. Click on the ad, just like this one before S-series series name type pointer. Select the values from the last table that is cell age three to age five. Once you click OK twice, your chart will look like this. That looks nothing like a pointer. We'll fix it. Don't excel default, do a doughnut chart, so we must change it to a pie chart. Well, you click on the chart and select Change Series Chart type. Now, change the drop down on the third chart to PI. If the angle is not correct, there is a chance, make sure to change it to 270. The chart has three sections the gray area, the blue area and the orange sliver. You'll need to remove the color from Sections one and two of the pie. Select both large data parts of the chart and apply no fill color to hide them. After this, you'll only have the small part left in the pie chart, which will be our needle for the speedometer. Next, you need to make this needle bit out of the chart so that it can be identified easily. For this, select the needle and right click on it. And then click on format, data point and format data point, go to series options and then add five percent in point explosion at this point. You have a ready to use pedometer. All done. Or are we? There is one more thing I like to do when I create a speedometer chart. I had a text box that automatically updates with the numerical value represented by the speedometer. This minor addition makes the chart much more intuitive and easier to read. Go to the Insert tab in the ribbon and select text box. Draw the text box to the centre of the chart like this? Select the text box and in the Formula Bar, enter an equal symbol. Now reference the cell with the pointer data in my sheet itself. H2 next format the text box to whoever you feel is appropriate. When the data in the tables gets updated, the text boxes value will as well delete the legend of the chart. In the end, you will need to move all data labels to end corners. Your first gauge pedometer chart is ready to rock. Thanks for watching.