Creating a Gauge Chart in Excel

​By Nathan Rush, Institutional Research Analyst, Wheaton College (MA)

Given all the talk on our campuses of “moving the needle” on one metric or another, a gauge or speedometer chart is a convenient way to simultaneously display several pieces of information. A gauge chart created in Excel is actually a combination of two charts: a doughnut that displays the range of possible responses and a pie chart with one narrow segment as the needle that indicates a specific data point, such as a student response or a group average. In the example below (Figure 1), the needle indicates one response to a question with four categorical responses:

gaugechart1.JPG

To begin creating this chart in Excel 2013, create a data table organized like the example in Figure 2, with three columns:

gaugechart2.JPG

As previously mentioned, the scale of responses is a doughnut chart, which is organized, shaded, and rotated in such a way that only half of it is visible and used in the finished gauge. The four response categories fill one half of the chart and are of equal size, with a fifth invisible segment as the bottom portion. Disregard the values in the Needle column for a moment, highlight the entire data table, and select a doughnut chart from the Ppie charts menu on the Insert tab. The chart should look like Figure 3.

gaugechart3.JPG
 

The outer ring in Figure 3 will be the needle of the finished gauge, and everything but the smallest segment (orange) should be set to No Fill and No Outline. Choose another fill and outline color for the smallest segment, such as black. For the inner ring, the left segment (dark blue) should be set to No Fill and No Outline, while the four other segments can be filled with different shades of blue (or any other color scheme) and set to No Outline. Adjust the width of the rings by reducing the doughnut hole size from 75% (default) to 25% under the Format Data Series menu. Also under this menu, rotate the chart by increasing the angle of first slice from 0° (default) to 270°. Next, select the chart legend and delete it. The result should look something like Figure 4:

gaugechart4.JPG
 

Right-click the chart and select Change Series Chart Type. From the pop-up menu, check the box to move Needle to the secondary axis, and change its chart type to Pie (Figure 5).

gaugechart5.JPG

Unfortunately, changing the chart type also resets the angle of the first slice to 0 for the needle. Right-click the needle and set the angle for this axis back to 270°. The simplest way to add labels to the gauge is to use text boxes.

Returning to the Needle column in Figure 2: technically speaking, a needle that touches any part of one of the segments would be understood to indicate that particular segment. The examples in Figure 6 all indicate a response of “Afternoons,” but aesthetically, the center of a gauge segment is probably the best choice. Because each gauge segment is 25, the center value of “Mornings” will be 12 or 13 (12.5 if you want to be exact), the center of “Afternoons” either 37 or 38, and so on.

gaugechart6.JPG
The 1 value in the Needle column controls the size of the needle. The third value is a formula, =200-C3-C2 or 200 minus the other two values in the column, so that when the response value (cell C1 in the example) changes, the position of the needle automatically adjusts.

Instead of equal-sized categories, the gauge can be set up to show the proportion of each response in the data set. Figure 7 shows another example with four response categories sized according to percentage of responses. The second table calculates the midpoint of each segment.

gaugechart7.JPG
Once you have an understanding of the role each value plays in controling the appearance and behavior of the gauge, you can adjust them to customize your chart: incorporate a second needle using an additional pie chart to compare a previous response to a current one, or expand the gauge beyond a semi-circle if it suits your application. Use fill colors to add another layer of information: in Figure 7, perhaps “1 to 2” is the preferred number of weekends a student should go home during their first semester. Color that segment green to show viewers this is the preferred response. A gauge chart is an eye-catching and versatile addition to the IR toolkit.

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 5
 
Lee posted on 3/17/2016 12:34 PM
Thanks Nathan for a great tip! This could be very helpful in producing dashboards as well as other graphical presentations of data. (However, readers should be cautioned that if you're using an earlier version than Excel 2013 you may not have some of these capabilities.)
William posted on 3/17/2016 1:40 PM
Excellent job, Nathan. We are proud of you.

Bill Knight
Daniel posted on 3/17/2016 2:46 PM
This is a REALLY great tip. Thanks for sharing!
Meg posted on 3/17/2016 3:45 PM
Very good. Thank you for sharing. I can really use this.
Steve posted on 9/1/2016 11:40 AM
Hello,

I found an automated solution @ https://exceldashboardschool.com