Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Posts Tagged ‘Excel Charts’

Create a Gauge Chart in Excel Part 2

In this article, guest blogger Nick Williams extends creating a gauge chart. Nick is an Access tutor based in the UK.

Here you’ll learn to create a slightly more advanced kind of Gauge chart with will actual have a reference gauge and pointer to indicate the desired value. You can refer to the advanced Gauge Chart tab of the embedded Excel file.

Let’s get started.

  1. Let’s assume we want to represent 140 as a pointer in the range of 180 and we have 3 categories in the gauge at intervals of 60 each. For this setup, we will create the data table accordingly
  2. Insert 60 in B2, B3 and B4 for each category in the gauge chart
  3. Add sum of B2:B4 in B5. Add the pointer value in C2(in this case 140). 1 or 2 in C3 which defines pointer width and enter this formula in C4: SUM(B2: B5)-C3-C2
  4. The formula helps in creating the remaining value of the Pie chart

  5. Now Select the range A1:C5 and Inset a Pie chart by navigating to the Charts section of the Insert Ribbon. After inserting the chart, in the Design tab of Chart Tools ribbon select Change Chart Type
  6. Select combo chart from bottom left of the pop up window generated
  7. Select Doughnut chart for Range series and Pie for the Pointer series. Make sure the Secondary Axis check box is ticked for the Range series. Then Click OO to plot the graph

  8. Remove the legend and the title or modify the title as per your requirement
  9. Right click on the chart area and select Format Data Series

  10. This will open a section to Format the Data series on the right
  11. Make the angle of the first slice as 270° of both the Doughnut and Pie Chart. You can switch between the series by clicking the respective graph and set the Doughnut hole size to 60

  12. Now click on the bottom half of the Doughnut chart to modify the format of only that slice (You might have to click twice if the slice isn’t selected). Click on the paint bucket icon available on the right. In the Fill section select no fill
  13. You can select No border in the border section, remove the border from all the other slices
  14. Repeat the same steps for the Pie Chart as well. One additional step for the Pie Chart would be to remove the fill of the initial slice that is the pointer value (the other big slice)
  15. Now change the fill of the remaining 3 slices of the doughnut to indicate different categories. I’m formatting them to Green, Orange & Red for Low, Medium & High respectively
  16. Now the chart should look something like the following:

  17. Again, Select Change Chart Type from the Design tab of the Chart Tools ribbon. Here change the Secondary Axis to the Pie Chart or the Pointer series and click OK. Change the fill of the Slicer of the pointer to desired color (I’ve selected black)
  18. Now, Select the entire chart area and in the fill, select no fill and No line in the border section
  19. Position the graph in so that a cell aligns to the center space and reference that cell to the actual percentage we have plotted (here 135)
  20. Remove Gridlines by unchecking Gridlines from the Show section of View ribbon

  1. Now we have an advance gauge chart created:

Vary the pointer value in the initial table to see the graph change. As mentioned earlier even a formula can be written to determine the position

Create a Gauge Chart in Excel Part 1

We welcome back guest blogger Nick Williams. Nick is an Access tutor based in the UK.

In this article, you will learn how to create a gauge chart in Excel.

Let’s assume we would like to represent % value in a gauge chart. I’ve embedded the Excel workbook used for this article. Feel free to refer to it as you read through the article.

Let’s begin by creating a suitable data to plot the chart.

  1. I’m selecting 80% as the percentage to represent (I’m plugging that into cell B2), the same can be changed to any desired value or even a formula can be used to determine the value
  2. Then subtract the value (in this case 80%) from 1 or 100%, use relevant formula to do this in Cell B3
  3. Add 100% in the cell B4. Later you’ll understand why this is necessary

  4. Now Select the range A1:B4 and Inset a Pie chart by navigating to the Charts section of the Insert Ribbon
  5. Select the option for a Doughnut chart, you’ll get chart similar to the one shown below

  6. Remove the legend and the title or modify the title as per your requirement
  7. Right click on the chart area and select Format Data Series

  8. This will open a section to Format the Data series on the right
  9. Make the angle of the first slice as 270° and set the Doughnut hole size to 60

  10. Now click on the bottom half of the Doughnut chart to modify the format of only that slice (You might have to click twice if the slice isn’t selected). Click on the paint bucket icon available on the right. In the Fill section select no fill
  11. You can select No border in the border section, remove the border from all the other slices
  12. Fill the smaller slice with a lighter color to denote the remaining value to achieve 100%
  13. Now the chart should look something like the following:

  14. Now, Select the entire chart area and in the fill, select no fill and No line in the border section
  15. Position the graph in so that a cell aligns to the center space and reference that cell to the actual percentage we have plotted (here 80%)
  16. Remove Gridlines by unchecking Gridlines from the Show section of View ribbon

  1. Now we have a gauge chart created:

In Part II, we will discuss making a more advanced version of gauge charts.