Days are gone when data are presented in the simple bar and pie chart only. Now, it can be also presented in other types of charts like Hierarchy, Water fall, Scatter, Gauge, Thermometer etc.
Out of these, I have discuss the Gauge chart here in this article. Gauge chart will use needle to display the data on the chart, something like this.
Gauge chart is also known as Speedometer chart ( the reason is obviously visible from the above image).
This chart is created created using Doughnut and Pie chart option available under Insert menu of the excel.
For your reference, I have attached the pre-made file consisting data and charts below.
So, What are we waiting for? Just put your seatbelt on and get ready to know how to create speedometer chart in excel?
The below article will share the detailed and step by step instruction on creating ultimate and free Speedometer chart in excel easily.
Create Speedometer Chart In Excel
As mentioned earlier, the Speedometer chart is created using Doughnut and Pie chart together. This both types of chart options are available in Excel under the ‘Insert’ menu.
I am using Speedometer or Gauge chart to visualize achievement or growth rate creatively i.e KPI ( Key Performance Index). Trust me, this type of chart looks good for KPI report.
Note: For making this tutorial easy to understand, I have divided the entire process in various steps. I request to please strictly follow the below steps to create beautiful and attractive speedometer charts for your presentation.
#1. Create Two Additional Columns With Data
This is the first step to create speedometer charts in excel. In this step, you need to create two additional columns ( anywhere in the sheet) with below data.
Of course, you can change the above numbers and percentages as well. The only thing that you need to taken care of is: Only one value must be used across rows. It means if you are using 20 instead of 10 then you must change all the remaining 10s to 20 as well.
Of course, in such case, the total of the rows will get change to 200.
Note: The value section will control the different blocks of the graph. Just go back and refer the image of Speedometer attached earlier in this article. In that image, you will observe 10 blocks created with equal size ie, 10 each.
#2. Insert Doughnut Chart and Select Data
The second step is to insert ‘Doughnut’ Chart from the Insert Menu.
Next, right click on the chart and click on ‘Select Data’ option. Next, A popup will appear asking to select the data source.
Now, click on ‘Add’ option to select the data source. For proper clarity, please refer to below attached image.
Next, an another popup will appear asking to type the data source name and range.
Here, you can type any name for the data source. In our case we are using ‘Values’ as name of the data source. But, you can named it anything as per your wish.
Next, select the additional column created in previous step as a ‘Series Value’ . For more information, please refer to the below attached image.
Note: The range or series value must be selected including total amount that is 100. This is because, we need to hide the half of the chart for creating semi circle look.
Now, the doughnut chart will look something like this (the below image). You can delete the labels and names given on the bottom and top of the chart.
#3. Format Data Series Of Doughnut Chart
Now, right click on the above chart and select ‘Format Data Series’ option. In data series option, change the ‘Angle of first slice’ to 270 degree.
Now, the chart will look like this.
Next, select and right click on half of chart and go to ‘Format Data Point’. From there change the background and border color to ‘No Fill’.
This will hide the half of the chart completely and it will look like this.
#4. Add and format Data Labels
Now, once again right click on the chart and add ‘Data Labels’ to it. This will add ’10’ as data label to all the given blocks.
Next, select and right click on data labels and click on ‘Format Data Label’ option.
Under ‘Format Data Label’ option, disable the values and enable ‘Value from cell’. Here, you will select the range (20%, 30%…..) created earlier in step 1.
Now, the chart will look like this.
#5. Change the colors of Chart
Now, change the color of the chart as per your requirement.
Note: This needs to be done for every single blocks created in the chart separately. In my example, (starting from left side), I have selected the first block and change it into ‘Green’ color. Next, I have selected the second block and change it into ‘Green’ color using Gradient fill. Similarly, all the blocks are selected and then color are changed.
Now, it look something like this.
#6. Create Pivot Table Of Your Data
Next, you will require to create a pivot table ( any where in your file) and add slicer to it.
Note: At the end we will use this slicer for our chart.
#7. Create Another Two Additional Columns With Below Data
Next, create two another additional columns (with below data) anywhere in the excel. These data will be required for our second chart.
|Results||Value will come from Pivot Table using Slicer|
#8. Once Again Select Data Source
Once again right click on the chart and click on ‘Select Data Source’.
Next, click on ‘Add’ and select the range created in Step 7 above. The process to add range is already discussed in Step 2.
This will create another chart and it will look like this.
#9. Change Chart Type
Now, right click on the second chart and select ‘Change Chart Type’.
Click on ‘Combo’ the left side and fill the the appropriate options as shown in the below image.
#10. Format the Second Chart
Now, format the second chart created using ‘Pie Chart’ option. The following changes needs to be made to second chart.
- Change the ‘Angle of first slice’ to 270 degree.
- Change the color of the chart to ‘No Fill’ except small portion of the chart representing 4%.
That’s it. This will create the Speedometer or Gauge chart.
Note: The needle will display the amount appearing against the ‘Result’ field, created in step 4. Further, the amount appearing in ‘Result’ feild are coming from pivot table using slicer. This is because, if you want to change the value then select the appropriate option in the slicer . For more information please download the file ( given at the starting of the article) and refer it properly.
#11. The Final Touch
It’s a time to give final touch to the chart created.
I have add the following changes to the chart.
- Add a circle in the middle of the chart
- Add text to the circle (linked to value in the ‘Result’ feild, created in step 4)
- Changed the background of the chart and slicer as well.
- Placed the slicer beneath the chart created.
- Select both Chart and Slicer, right click on it and then group them together.
Over To You
Above all steps will create the beautiful speedometer or gauge chart for you.
Henceforth, whenever you need to present any KPI or achievement or growth related data to your boss or team, give a try to this chart. Trust me, everyone will get amazed by your presentation.
At last, feel free to comment or share your feedback in below comment box. You can also share your feedback or query below or contact us directly through ‘Contact Us’ page.