Among the different ways to chart and visualize data, gauge indicators can show how a single measure compares against a set standard. Excel does not offer a ready-made gauge chart, so here is how you can make one yourself.

The approach we will use is to overlay two graphs on top of each other. Newer versions of Excel make this easy through the Combo Chart feature. Older versions require you to graph both sets of data, then manually change one. Note that the directions below are designed for recent versions of Windows-based Excel. This can be performed in older versions and in an Apple environment by adapting the instructions.

Start by creating the following data table. All numbers are typed except one formula: D5=200-D4-D3. Highlight the blue sections:

CREATE THE BASE GRAPH

• Create a Combo graph. A combo graph is exactly as named—a combination of two graphs overlapping to present one image.
  • With the blue table highlighted, click menu ribbon Insert and choose the combo icon from the Charts list [red circle in image below] and Create Custom Combo; alternatively, open the entire charts mini tab [green circle below], click the All Charts tab, and the select the last choice, Combo.

    

  • Select Series Name Donut. Select Chart Type dropdown and choose the fourth option under Pie, the donut [red circle below].
  • Select Series Name Needle. Select Chart Type dropdown and choose the first option under Pie, the donut [green circle below]. Then put the Needle on the secondary axis by clicking the box under the heading, Secondary Axis. [orange circle below]
  • Click OK

If you have an older version of Excel that does not offer the “Combo Graph” option, or simply do not want use the Combo option, you can do this manually.

• With the blue table highlighted, click menu ribbon Insert and choose the Donut option from the All Charts list.
• Click on the ribbon menu Design, then Change Chart Type, and select the first Pie option.

CREATE THE NEEDLE

• The “needle” is the dynamic part of the gauge. From the needle column of the data table, the first number represents the proportional scale of 0-99 where you would like needle to land, and will be colored with No Fill. It is the key variable in this table, and it places the needle itself, which is the second number in the table. The third number represents the remainder of the pie that makes it a full circle, and should also be invisible.
• While going through the steps, it is helpful to have open the menu ribbon Format, and then monitor the Current Selection dropdown on the far-left side because it will identify the Slice Name as identified in the data table. Steps:
  • Right Click on the graph and choose Format Series data to open the formatting window pane on the right side of your screen (older versions: pop-up window)
  • Make the first slice invisible. Change the Series Option to “Needle” either through the Format Data Series pane or ribbon bar / Format or the Current Selection drop down. Click Ctrl + right arrow to select “Needle” Point 1; change the fill to No Fill. Color the Needle. Click Ctrl + right arrow to select “Needle” Point 2, change the fill to Sold fill and choose black (or other). Color the remainder invisible. Click Ctrl + right arrow to select “Needle” Point 3, change the fill to No Fill.Rotate pie to put to the starting slice at the right point on the circle. With any part of the Needle selected, in the Format Data Point pane, change the Angle of first slice to 270 degrees under the 3 bars icon [image below].

            

CREATE THE BACKGROUND AND THE GAUGE

• The “donut” is the static, background part of the gauge. The donut chart defaults to a full 360 degree circle; we need to make a few modifications to hide half of it.
  • Make half of the chart invisible. From the data table, note that the invisible slice is equal to 100, or half of the sum of the visible slice. Excel will assign this to 50 percent of the donut. We need to color that invisible and make it disappear.
    • Right Click on the graph and choose Format Series Data to open the formatting window
    • Change the Series Option to “donut” either through ribbon bar Format then Format Data Series pane, or the Current Selection drop down. Click Ctrl + right arrow to select “donut” Point 1; change the fill to No Fill.
  • Color the slices. With the remaining slices, Ctrl + right arrow to select the next slide, then click Solid Fill from the formatting window, and choose the color you want.In the Format / Current Selection drop down, you will see the highlighted “donut” Point 2, Point 3, etc. To go back to a previous slice, click Ctrl + left arrow.
  • Rotate pie to put to the starting slice at the right point on the circle. With any part of the donut selected, in the Format Data Point pane, change the Angle of first slice to 270 degrees under the 3 bars icon.

    USING THE GAUGE

• This gauge is designed to be used on a 0-99 scale, where zero is on the left and 99 is on the right. For example, assume that your company tracks defects on a process, has 9 in one week, and has established the following scale rating scale:To normalize the 9 defects on this chart on a scale, use the total errors divided by range of the scale, or 9/(25-0) = 36percent. The gauge is set up with actual numbers, not percentages, so multiply this by 100 to convert to a whole number. This number should be linked to the data table cell D3!

ADD EMBELLISHMENTS

• Add a title. Click on the menu ribbon Design and select the first option under Quick Layout
• Add specific data elements. Move the chart where you would like it on the worksheet. Right click on a blank part of the chart and select Format Chart Area; choose No Fill to reveal the cells and grid underneath the chart. You can put specific data elements in the cells underneath the table and they will show through. If you do this, I recommend that you also turn off the border (Format, Border, No Fill).
• Add titles to the table as you would for any chart, and use Page Layout, Print Area to define which segment of the spreadsheet will be printed.

Bryan Lapidus, FP&A, is Director, FP&A Practice for AFP. Reach him at [email protected].  

For additional insights on FP&A, subscribe to the AFP monthly newsletter, FP&A in Focus.

Download AFP’s guide, Making Excel Work for FP&A, underwritten by Vena Solutions, to examine the spectrum of ways to apply Excel, including recent advances, add-ins and third-party solutions/offerings, to help you get the most out of this ubiquitous application.