How to Make a Gauge Chart in Excel

Whether you’re building a dashboard or just wanting another chart to add to your disposal, the gauge chart is always a popular choice. In this post, I’ll show you how you can create it painlessly and add it to your disposal next time you want to use a chart. It’s especially useful if you’re looking to compare actual vs forecast or need to track a completion percentage

As long as the version of Excel you’re using has a doughnut chart available, you’ll be able to follow these steps.

STEP 1: Set Up the Data for the Gauge Chart

First, you’ll want to set up two columns: one for the intervals and how big the pieces of the gauge chart will be.

For the intervals, normally, what I’ve seen is a 25/50/25 split, meaning the first and last portions are the same size, with the middle being the largest. The column needs to add up to 200, and so the last piece in this example would be 100.

For the second column, this is where you’ll determine where the marker shows up to track your progress or where your actuals come in at.

  • The first number should be 0
  • The second number the percentage; how far on the gauge chart you want the marker to be. This is where you’ll probably want to use a formula as this is the only number that should move on this chart.
  • The third number is how big the marker should be. In this example, I set it to five, and that’s about the highest I’d suggest it should be.
  • The last number is the remainder – here too, you’ll want the total for the column to add up to 200.

Here’s how my columns look right now:

gauge chart table

To move on to step two, create a chart using the Marker and Interval columns (include the labels).

STEP 2: Select the Two Columns and Create a Combo Chart

On the All Charts tab, at the very bottom, you’ll see an option for Combo. The Interval column should be a Doughnut chart while the Marker column should be a Pie chart. You’ll want them on two different axes, so make sure you have Secondary Axis ticked off as well.

excel combo charts

STEP 3: Format the Data Series on the Charts

Right click on the chart and select Format Data Series and select Angle of First Slice to 270 degrees. You’ll need to do this for both charts. To switch between charts, click on the Series Options button and select the other series.

series options selection excel

STEP 4: Change the Colors

Using the Series Options from above, make sure you have the Marker series selected. Here is where it gets a little tricky – you’ll need to select every part of the chart and make it blank except for the size of the slice – which you’ll probably want black.

If you have trouble moving across the different parts of the chart, use CTRL + left/right arrow keys to move along the sections. Your chart should now look something like this:

bar chart and doughnut excel

Now, switch over to the Interval series. Here you’ll do the same, except now you’ll be changing the bottom half of the doughnut so that it is blank, and everything else you can change to your liking. In my example, I’m going to go from red to light green to dark green. Here is what the chart looks like after those changes:

gauge chart basic

STEP 5: Additional Formatting (Optional)

You can do any additional formatting to the chart to make it look how you want. In my example, I added a bevel and some shadows to it to make it stand out a little more. I also shrunk the size of the slice to two:

gauge chart excel bevel

Save the Gauge Chart for Future Use

If you like your chart and think you’ll reuse it in the same type of layout, what you can do now is save it as a template. To do that, simply right click on the chart and select Save as Template

excel chart save template

Now, if you have the data in the same format you can go back to insert chart and look for the Templates folder which will now have the saved chart template:

excel chart template

That’s all there is to it! Please let me know if you run into any issues or require clarification on any of the steps above.

Creating a Dynamic Dashboard in Excel

Do you want to create a dashboard that will update all of your charts simultaneously based on what filters your users select? Follow the steps below and you can create a professional-looking dashboard without having to use any complex formulas or programming.

Preparing the Data

If you have data in Excel that you want to use to create a dashboard, there are a couple of things you’ll want to do first to make sure everything goes well.

1. Ensure your data is free of error cells, as this will result in errors.
2. Have proper headings setup so that you know what you are referencing in your dashboard. 
3. Setup a named range for your data, ideally a dynamic one. This will make it easier to link everything to your data quickly and easily.

Making sure your data is clean and ready to go is the most important step, but unfortunately the one that is easily overlooked. After all, if you’re data is no good, your outputs won’t be either. Garbage in, garbage out.

You can follow along with my sample data, which can be downloaded here.

Setting up the Pivot Table

First up, let’s look at creating a Pivot Table (see this post for an into into pivot tables). 

I’ve assigned a name of Dataset1 to my data, and this is what I will referencing when I create a pivot table. With a named range, I don’t have to worry about selecting the data before clicking the create pivot table button, I can do it from anywhere.

Once I’ve got my pivot table ready to go, the next thing to do is to select my fields. The fields that I have to choose from in my data set include: date, store, salesperson, and product. 

For my first pivot table I’ll want to look at the date because I want to start from a high level and work my way down. No sense in starting from the detail when I don’t have any context yet.

For the rows, I’ll select Dates, and in the values I’ll select Total Sales. My table now looks like this:

Ultimately, it doesn’t really matter if you want to select columns or rows for this as it’s going to be in a chart anyway. In Excel 2016, my dates were automatically grouped into months, which is what I wanted. If you want to change the grouping, simply right-click on the dates values and click Group

Then select the how you want the dates to be grouped

Next, I want to clean up my formatting so that my total sales have commas and so that the data is easier to read. To do this, I’ll right-click on that field and choose Value Field Settings

Then click on Number Format and then select Accounting.

Creating a Pivot Chart

Now it’s time to convert this into a chart. Select the Insert tab and in this example I’m going to select a regular Column Chart

What you have now is a simple column chart that shows your sales by month. The only thing special about a pivot chart is that you’re able to filter it based on your pivot table.

You’ll notice there are drop downs on your pivot chart that you select to modify your data. I can select only certain months to look at. 

The amount of options you have on here depends on how many fields you added to your pivot table. Whether you make the changes on your pivot chart or pivot table doesn’t matter, the chart will update all the same.

However, for the purposes of a dashboard, I’m going to get rid of these ugly filter buttons on my chart. To do this, click on your chart and click the button for the Field Buttons and this will remove the buttons. 

Now that the pivot chart is ready to go, you can now go about and format it how you like. 

Formatting and Tidying Up

Once you’re done formatting the chart, move it on to another tab. Because you’re creating a dashboard you probably won’t want your original pivot table to show up along with it. For this reason I usually move all the charts onto a separate tab.

Lastly, you’ll probably want to format your chart so that it is more appealing to your users.

Rinse and Repeat

For a dashboard, you’ll want to create multiple charts and so you’ll likely want to create another pivot chart following the same steps as above. In the next chart you can focus on a more detailed analysis, such as sales by store or rep.

In my example, I added three more charts in total and decided to mix it up by using a column chart, a pie chart, a stacked chart, and a bar chart. Mixing it up a little will keep your dashboard more interesting for your users.

Adding Slicers

Once you’ve added your charts, the next thing you’ll want to do is add slicers. Note that slicers are new to Excel 2010 and if you have an older version you will not be able to utilize these features.

For an overview on slicers, refer to this post.

The real advantage of using slicers is that they can be linked to multiple pivot tables and pivot charts. This allows you to now turn your dashboard into a dynamic one that will update as the user selects options from the slicers.

Once you’ve inserted slicers, you want to make sure that each slicer is connected to every pivot table. To do this, simply right-click on the slicer and select Report Connections.

On the next screen you can see all the pivot tables and charts that the slicer is connected to. Ensure that you have ticked off all the ones you want it connected to and then click on OK.

By doing this your slicer will now update all those charts and tables automatically. Repeat this step for every slicer you create.

Your dashboard is now ready to use and anyone that makes a selection on one of your slicers will see all the charts update immediately.