Sometimes, we require to show a speedometer in our presentation to show data differently. We can create the speedometer quite easily using Excel Chart. In this article, we are going to demonstrate the step-by-step procedure to create a speedometer chart with two needles in Excel. If you are curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Step-by-Step Procedure to Create Speedometer Chart with Two Needles in Excel
The step-by-step procedure to create a speedometer chart with two needles is explained below. After completing all the steps, the speedometer will be like the image given below:
Step 1: Create Criteria and Scale Table
In the first step, we will create the Criteria and the Scale Table. We are going to consider our dataset shows the monthly production scale of the industry.
- First of all, we are going to create the Criteria table.
- For that, write down the following title in the merged cell B4 and the heading of this table in the range of cells B5:C5.
- After that, write down the following entities in the range of cells B6:B9 and their corresponding values in the range of cells C6:C9.
- At last, we will show the total of all the values using the SUM function in cell C10.
=SUM(C6:C9)
- Press Enter.
- Similarly, design the Scale Table. For that, write down the following title in the merged cell F4 and the heading of this table in the range of cells F5:G5.
- Now, write down the following scale entities in the range of cells F6:F15 and their corresponding values in the range of cells G6:G15.
- Finally, write down the following formula into the G16, to sum up, all the scale interval values.
=SUM(G6:G15)
- Press Enter.
- Our task is completed.
Thus, we can say that we have finished the first step to create a speedometer chart with two needles in Excel.
Step 2: Design Needle Position Table
In this step, we will design the Needle Position Table. We want to show two needles in our chart. As a result, there will be two-needle particulars in this table. We are going to take the particulars month-wise.
- First, write down a suitable title in the merged cell B12 and the heading of this table in the range of cells C13:D13.
- After that, write down the following entities in the range of cells C14:C16 for the month of January. We have to input the Production and Width manually.
- The value of width is the width of our needles.
- Now, to calculate the value of the End point, write down the following formula into cell D16.
=2000-D14-D15
- Press Enter.
- Similarly, create another slot for the month of February in the range of cells B17:D19.
Hence, we can say that we have completed the second step to create a speedometer chart with two needles in Excel.
Step 3: Insert Doughnut Charts for Criteria and Scale Table
In the following step, we are going to insert the value of the Criteria and Scale Table in the chart.
- At first, select the range of cells C6:C10.
- Now, in the Insert tab, click on the drop-down arrow of the Insert Pie or Doughnut Chart option and select the Doughnut chart option from the Doughnut section.
- The chart will appear in front of you.
- Then, click the Chart Elements icon, and eliminate all other elements except the Data Labels.
- After that, right-click on the doughnut and select the Format Data Series option from the Context Menu.
- As a result, a side window called Format Data Series will appear.
- In the Series Options tab, modify the value of the Angle of the first slice from 0 to 270 degrees. Besides that, reduce the Doughnut Hole Size from 75% to 40%.
- Now, click on the large slice of the doughnut. In the Fill tab, select the No fill option. Moreover, we also choose the No line option from the Border section.
- Then, click on the Data Labels, and in the drop-down of the Label Options, select the Label Options tab.
- Afterward, in the Label Options section, uncheck the Value option, and check the Value Fron Cells option.
- As a result, a small dialog box called Data Labels Range will appear.
- Select the range of cells B6:B9.
- Finally, click OK.
- You will see our desired data labels in the chart.
- Now, to insert the Scale Table value in the same chart, go to the Chart Design tab and select the Select Data option from the Data group.
- As a result, the Select Data Source dialog box will appear.
- In the Legend Entities (Series) section, select the Add option.
- Another dialog box called Edit Series will appear.
- In the Series Name field, select cell F4 as the series name.
- Then, in the Series values field, select the range of cells G6:G16.
- Finally, click OK to close the Edit Series dialog box.
- Again, click OK to close the Select Data Source dialog box.
- Similarly, format the second doughnut like the first doughnut chart.
- Our job is done.
So, we can say that we have accomplished the third step to create a speedometer chart with two needles in Excel.
Step 4: Insert Two Needles Chart
Now, we will insert the needles into our chart.
- Firstly, in the Chart Design tab, click the Select Data option from the Data group.
- As a result, the Select Data Source dialog box will appear.
- In the Legend Entities (Series) section, select the Add option.
- Another dialog box called Edit Series will appear.
- Now, in the Series Name field, select cell B14 as the series name.
- After that, in the Series values field, select the range of cells D14:D16.
- Finally, click OK to close the Edit Series dialog box.
- Again, click OK to close the Select Data Source dialog box.
- The series will appear in our chart.
- Then, in the Chart Design tab, click the Change Chart Type option from the Type group.
- A dialog box called Change Chart Type will appear.
- Now, click the drop-down of the Chart Type of the January series and change the chart type Doughnut to Pie chart.
- Click OK.
- The chart will convert into a Pie chart.
- Then, right-click on the pie chart and select the Format Data Series option from the Context Menu.
- As a result, the side window called Format Data Series will appear.
- In the Series Options tab, change the Angle of the first slice option from 0 to 270 degrees.
- In the Fill tab, select the No line option from the Border section.
- After that, select the large two slices of the pie chart, and in the Fill tab, select the No fill option.
- In the end, select the data labels of the pie chart and press Delete.
- Similarly, add another series for the month of February.
- You may notice that no change is shown in our chart.
- Now, select the Change Chart Type option from the Type group located in the Chart Design tab.
- The Change Chart Type dialog box will appear.
- Scroll down to get the February Series and check the Secondary Axis option.
- Finally, click OK.
- You will notice this time the pie chart will appear.
- Modify this pie chart like our first pie chart.
- Further, format the chart according to your desire. We insert an Oval shape at the center of our chart for a better outlook and set no fill for the shape format to keep the speedometer close to our dataset. Besides that, modify the slice fill color of the needles to distinguish them easily.
- Thus, our speedometer creation task is completed.
In the end, we can say that we have finished the final step to create a speedometer chart with two needles in Excel.
💬 Things You Should Know
At the time of the speedometer chart creation, we kept the width of the needle more than its usual width. We did this so that we could easily select the needle slice when it was required. It will help you with the smooth editing of the charts.
[/wpsm_boxStep 5: Verify Speedometer Chart with Different Data
In this step, we will input different values to check the accuracy of our speedometer.
- First of all, in the range of cells D14:D15 and D17:D18, input the following values, respectively.
- Production: 475
- Width: 5
- Production: 700
- Width: 5
- You will notice our speedometer will respond automatically, and the needles will mark the production value.
Finally, we can say that we are able to create a speedometer chart with two needles in Excel.
Read More: How to Create Meter Chart in Excel (with Easy Steps)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to create a speedometer chart with two needles in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!