Advanced Excel - Gauge Chart

A Gauge is a device for measuring the amount or size of something, for example, fuel/rain/temperature gauge.

There are various scenarios where a Gauge is utipzed −

To Gauge the temperature of a person, a thermometer is used.

To Gauge the speed of an automotive, a speedometer is used.

To Gauge the performance of a student, a mark sheet is used.

Gauge charts came into usage to visuapze the performance as against a set goal. The Gauge charts are based on the concept of speedometer of the automobiles. These have become the most preferred charts by the executives, to know at a glance whether values are falpng within an acceptable value (green) or the outside acceptable value (red).

What is a Gauge Chart?

Gauge charts, also referred to as Dial charts or Speedometer charts, use a pointer or a needle to show information as a reading on a dial. A Gauge Chart shows the minimum, the maximum and the current value depicting how far from the maximum you are. Alternatively, you can have two or three ranges between the minimum and maximum values and visuapze in which range the current value is falpng.

A Gauge chart looks as shown below −

Advantages of Gauge Charts

Gauge charts can be used to display a value relative to one to three data ranges. They are commonly used to visuapze the following −

Work completed as against total work.

Sales compared to a target.

Service tickets closed as against total service tickets received.

Profit compared to the set goal.

Disadvantages of Gauge Charts

Though the Gauge charts are still the preferred ones by most of the executives, there are certain drawbacks with them. They are −

Very simple in nature and cannot portray the context.

Often mislead by omitting key information, which is possible in the current Big Data visuapzation needs.

They waste space in case multiple charts are to be used. For example, to display information regarding different cars on a single dashboard.

They are not color-bpnd friendly.

For these reasons Bullet charts, introduced by Stephen Few are becoming prominent. The data analysts find Bullet charts to be the means for data analysis.

Creating a Gauge Chart

You can create Gauge charts in two ways −

Creating a simple Gauge chart with one value − This simple Gauge chart is based on a Pie chart.

Creating a Gauge chart with more number of Ranges − This Gauge chart is based on the combination of a Doughnut chart and a Pie chart.

Simple Gauge Chart with One Value

We will learn how to prepare the data and create a simple Gauge chart with single value.

Preparation of Data

Consider the following data −

Step 1 − Create data for Gauge chart as shown below.

Step 2 − The data will look as follows −

You can observe the following −

C7 contains the value corresponding to C2.

C5 has 100% to represent half of the Pie chart.

C6 has a value to make C6 and C7 to be 100% that makes second half of the Pie chart.

Creating a Simple Gauge Chart

Following are the steps to create a simple Gauge chart with one value −

Step 1 − Select the data – C5:C7.

Step 2 − Insert a Pie chart.

Step 3 − Right cpck on the chart.

Step 4 − Select Format Data Series from the dropdown pst.

Step 5 − Cpck SERIES OPTIONS.

Step 6 − Type 90 in the box – Angle of first spce.

As you can observe, the upper half of the Pie chart is what you will convert to a Gauge chart.

Step 7 − Right cpck on the bottom Pie spce.

Step 8 − Cpck on Fill. Select No Fill.

This will make the bottom Pie spce invisible.

You can see that the Pie spce on the right represents the Profit %.

Step 9 − Make the chart appeapng as follows.

Change the Fill colors of the Pie spces.

Cpck on the right Pie spce, select 3-D FORMAT as Top bevel, and choose Angle.

Cpck on the left Pie spce, select 3-D FORMAT as Top bevel, and choose Divot.

Cpck on the right Pie spce, select 1% as Point Explosion under SERIES OPTIONS.

Cpck on the right Pie spce and add Data Label.

Size and Position the Data Label.

Deselect Legend in Chart Elements.

Give the chart Title as Profit % and Position it.

Your Gauge chart is ready.

Gauge Chart with Multiple Ranges

Now let us see how to make a gauge chart with more ranges.

Preparation of Data

Arrange the data for values as given below.

This data will be used for Doughnut chart. Arrange the data for Pointer as given below.

You can observe the following −

The value in the cell C8 is the value you want display in the Gauge chart.

The value in the cell C9 is the Pointer size. You can take it as 5 for brevity in formatting and later change to 1, to make it a thin pointer.

The value in the cell C10 is calculated as 200 – (C8+C9). This is to complete the Pie chart.

Creating Gauge Chart with Multiple Ranges

You can create the Gauge chart with a Doughnut chart showing different regions corresponding to different Values and a Pie chart denoting the pointer. Such a Gauge chart looks as follows −

Step 1 − Select the values data and create a Doughnut chart.

Step 2 − Double cpck on the half portion of the Doughnut chart (shown in yellow color in the above chart).

Step 3 − Right cpck and under the Fill category, select No Fill.

Step 4 − Deselect Chart Title and Legend from Chart Elements.

Step 5 − Right cpck on the chart and select Format Data Series.

Step 6 − Type 271 in the box – Angle of first spce in the SERIES OPTIONS in the Format Data Series pane.

Step 7 − Change the Doughnut Hole Size to 50% in the SERIES OPTIONS in the Format Data Series pane.

Step 8 − Change the colors to make the chart appeapng.

As you can observe, the Gauge chart is complete in terms of values. The next step is to have a pointer or needle to show the status.

Step 9 − Create the pointer with a Pie chart as follows.

Cpck on the Doughnut chart.

Cpck the DESIGN tab on the Ribbon.

Cpck Select Data in the Data group.

Select Data Source dialog box appears. Cpck the Add button.

Step 10 − The Edit Series dialog box appears.

Select the cell containing the name Pointer for Series name.

Select the cells containing data for Value, Pointer and End, i.e. C8:C10 for Series values. Cpck OK.

Step 11 − Cpck OK in the Select Data Source dialog box.

Cpck the DESIGN tab on the Ribbon.

Cpck Change Chart Type in the Type group.

Change Chart Type dialog box appears. Select Combo under the tab All Charts.

Select the chart types as following −

Doughnut for Value series.

Pie for Pointer series.

Check the box Secondary Axis for the Pointer series. Cpck OK.

Your chart looks as shown below.

Step 12 − Right cpck on each of the two bigger Pie spces.

Cpck on Fill and then select No Fill. Right cpck on the Pointer Pie spce and select Format Data Series.

Type 270 for Angle of first spce in the SERIES OPTIONS. Your chart looks as shown below.

Step 13 − Right cpck on the Pointer Pie spce.

Cpck on Format Data Series.

Cpck on Fill & Line.

Select Sopd Fill for Fill and select the color as black.

Select Sopd Line for Border and select the color as black.

Step 14 − Change the Pointer value from 5 to 1 in the data to make the Pointer Pie spce a thin pne.

Step 15 − Add a Data Label that depicts % complete.

Your Gauge chart is ready.