Creating a gauge chart

This chart resembles a speedometer gauge and displays a value between 0 and 100%.
The gauge Chart in Excel 2016

This example shows a pie chart set up to resemble a gauge. Although this chart displays a single value (entered in cell C16), it actually uses three data points (in D13:D15). Cell C16 contains a formula that calculates the percent of goal:

=C14/C13

As you enter new data in column C, the formulas display the current results:

The gauge Chart in Excel 2016

Cell C12 consists of 50%, and that slice is hidden.

The other two slices are apportioned based on the value in cell C16. The formula in cell D14 is

=MIN(C16,100%)/2

This formula uses the MIN function to display the smaller of two values: either the value in cell C16 or 100%. It then divides this value by 2 because I'm only dealing with the visible half of the pie chart. Using the MIN function prevents the chart from displaying more than 100%.

The formula in cell D15, shown below, simply calculate the remaining part of the pie:

=D13-D14

To create the chart like this one, do the following:

    1.    Select cells D13:D15 and create a pie chart:

Pie Chart in Excel 2016

    2.    Remove the legend.

    3.    Open Format Data Series on the Format task pane:

In the Series Options section, change the Angle of first slice setting:

Series Options in Excel 2016

    4.    Open Format Data Point on the Format task pane for the slice 50%:

Series Options in Excel 2016
  • On the Fill section, select No fill.
  • On the Border section, select No Line:

You can then make any other adjustments to get the look you desire. See also How to create dependent on a volume chart title.