Microsoft Office Tutorials and References

In Depth Information

**Creating a Gauge Chart**

Creating a Gauge Chart

In some cases, you might want to depict, graphically, progress towards a goal. One way to do

that is by using a gauge chart, as shown in Figure 138-1. There’s only one problem: Excel doesn’t

have a gauge chart! But Excel does have a pie chart. In this tip, I describe how to use a pie chart

to display a single value that ranges between 0 and 100 percent.

Figure 138-1:
This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.

The chart depicts the value in cell F1, which contains this formula:

=SUM(B4:B18)/500

The pie chart actually uses three data points (in H2:H4).

One slice of the pie — the slice at the bottom, represented by cell H4 — always consists of 50

percent. I rotated the pie so that the 50 percent slice was at the bottom. Then I hid that slice, by

specifying No Fill and No Border for the data point.

The other two slices are apportioned based on the value in cell F11. The formula in cell H2 is

=MIN(F1,100%)/2

This formula uses the MIN function to display the smaller of two values: either the value in cell F1

or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant.

Using the MIN function prevents the chart from displaying more than 100 percent.