How To Create A Gauge Chart For Measuring Progress Against A Goal

Get The Completed Workbook

In a previous post I showed you How To View Actual Versus Target With A Thermometer Style Chart and in this post we’re going to look at how to display similar information in a gauge chart. Excel doesn’t have a gauge chart option, but we can use a pie chart to create something that looks like a gauge. In this example we’ll chart the current amount raised against a fundraising goal.

 

 

We’ll need to set up a few fields in our spreadsheet to start.

  1. Current – This will be the current amount that’s been raised so far to date.
  2. Fundraising Goal – This is the goal amount of the fundraising campaign.
  3. Percent Complete – This will be a formula =Current/Fundraising Goal and represents the percentage of the goal raised to date.
  4. Complete – This will be the portion of our pie chart that will represent our percent complete and will be given by the formula =MIN(50%,Percent Complete/2).
  5. Incomplete – This will be the portion of our pie chart that will represent our percent incomplete and will be given by the formula =50%-Complete.
  6. Unused – This will be a constant 50%. We want our chart to look like a half circle, so 50% of our pie chart will not be used. We will end up hiding this part of the chart by formatting it with no fill and no outline.

 

 

Insert a pie chart.

  1. Highlight the data for the complete, incomplete and unused percentages. They will add to 100% so that we have a full pie.
  2. Go to the Insert tab in the ribbon.
  3. Select the small Pie Chart icon from the Charts section.
  4. Select the 2D Pie Chart.

 

 

The result will not automatically look like a gauge, we will need to make some adjustments.

  1. We can change the title of the chart to something more meaningful like Fundraising Progress. Later we can also move it below the gauge.
  2. Delete the legend by clicking on it and pressing Delete.
  3. Right Click on the pie and select Format Data Series. You should see a new window pane with the heading Format Data Series on the right hand side. Change the Angle of first slice to 270 under the three vertical bar icon.
  4. Left Click on the 50% pie slice and you should see the Format Data Series change to Format Data Point. Under the paint can icon change the setting to No fill and No line. Similarily, you can change the colour of the two other slices as desired.

 

 

Add some labels such as 0% and 100% as gauge marks using a text box and we now have something that looks like a gauge that’s been constructed out of a pie chart! It’s a cool pie chart hack that’ll add some style to your dashboards.

 

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃