Categories
Excel

Create a Speedometer Chart

Excel offers many different types of charts except a speedometer chart. In this tutorial, we’ll create a really impressive, workable speedometer chart by using a combination of doughnuts and pie charts.

Preview: The Speedometer Chart – final result

First, set up some data as shown in the figure:

Data set up for speedometer chart: Press CTRL+` to show the actual formulas (highlighted) on the worksheet.

Next, create a doughnut chart as shown in the figure. A doughnut chart works a bit like pie chart, but it can contain multiple series, whereas a pie chart cannot:

  1. Highlight the range C2:C5
  2. Go to the Insert tab and click the Recommended Charts icon in Charts group. The Insert Chart dialog box will be displayed.
  3. Choose Pie from the All Charts tab.
  4. Select the doughnut as shown in the above figure.
  5. Click OK button to insert the chart on the worksheet.

Select the doughnut chart, slowly double-click the largest slice to select it, and then right-click on the selected slice and choose Format Data Point from the context menu. It will shows the Format Data Point pane on the right side of the workbook:

Animation: Rotating the chart and hiding the largest data point

Click the Series Options from the Format Data Point pane and set the angle of this slice to 90 degrees.

Then click the Fill & Line icon and select the No fill and No line from the Fill and Border options as shown in the above animation.

The doughnut chart should look like the one in the figure below:

Doughnut chart with 90% angle and no color or border on the first slice

You need to add another series (Series 2) of values to form the slots for the small dial chunks. So again highlight the chart, and delete legends and chart title, then follow these steps:

  1. Right-click on the chart and select Select Data option. The Select Data Source dialog box appears.
  2. Click the Add button from the Legend Entries (Series) tab. , which will opens the Edit Series dialog box.
  3. Under the Series Values box, select the range B2:B13. Click the OK button.

Click OK again to close the Select Data Source dialog box.

Again, slowly double-click the largest slice, Point 1, of Series 2 to select it, and then right-click on the selected slice and set the fill and border to No fill and No line from the shortcut menu as shown in the above figure.

The doughnut chart should look like the one in the figure below:

Add another series (Series 3) of values to form the slots for the dial labels:

  1. Right-click on the chart and select Select Data option.
  2. Click the Add button to add a third series (Series 3) to create the needle.
  3. Under the Series Values, select the range D2:D5. Press OK button to return back to the Select Data Source dialog box.
  4. Click Edit button on right tab Horizontal (Category) Axis Labels.
  5. Under the Axis label range, select the range A2:A13 and click OK.

The doughnut chart should look like the one in the figure below:

Doughnut chart with three series

Highlight Series 3 (outer series), then right-click it and select Change Series Chart Type:

Change this series to the default pie chart. Yes, it looks strange. But rest assured, if the pie chart overlays the doughnut chart, you have done this correctly. See following figure:

Next you need to hide all the slices of pie chart you just laid over the doughnut except the smaller one.

  1. Select one section of the pie chart (two slow clicks on the desired slice will do this)
  2. Right-click on it and choose Format Data Point from the context menu. This will open the Format Data Point pane on the right side of workbook.
  3. Click Fill & Line and select No fill from the Fill section and No line from the Border section.
Hide all slices by selecting each slice individually, excluding the small slice, use No fill and No line options from the Format Data Point pane.
  1. Next, select the smallest slice and format it as shown in the following figure:
  1. While the smallest pie slice selected, choose Series Options icon from the Format Data Point pane and change the “Angle of the first slice” to 120 degrees:
Speedometer chart with only the third series of pie chart showing color
  1. Next insert data labels: select the chart, go to the Chart Design tab, click Add Chart Element drop-down and select Data Labels and then choose Data Callout:
  1. Right-click on a data label and choose Series 1 Data Labels to select them, then press the Delete button on the keyboard to delete them.
Right-click a label, select Series 1 and Series 3 data labels from the menu, and delete these data labels

Repeat the above step (step 7) to remove the Series 3 Data Labels.

  1. Now you will only have Series 2 data labels on the chart. Slowly double-click on the bottom data label (showing 0 and 50%) and delete it.
  2. Click outside of the chart to de-select it, then right-click one of the label it will select all labels, choose Format Data Labels from he context menu.
  3. Uncheck the Percentage box from the Format Data Labels pane. See following figure:
  1. Next, select No Fill and No Line from the Fill & Line section:

Finally, adjust the position of labels as shown in the following animation:

The final result:

Preview: The Speedometer Chart – final result

Charts and Graphs