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 Graphing’

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.

 

Chart Intermittent Dates

Downturn  

Quote of the day

To become aware of the possibility of the search is to be onto something.

-Walker Percy

Natasha wanted to graph photocopier downtimes to show the duration of each downtime in minutes with data points labeled with the date of the down time incident. Her data list included the date of the copier needed service and the number of minutes it remained out of order. This seems simple enough but when she tried to generate a graph for the year, every day of the year appeared in the graph. That wasn’t quite what Natasha was looking for. She wanted to show only dates when there was copier downtime.

Graphing, it seems, assumes that if a series consists of date values then you want a date series showing every date between the first and last dates. Here you see the problem in the graph on the left, and a solution in the graph on the right. In the original data, the first entry is February 12 and the last, December 14. Notice how the graph is display dates from February 12 to December 12.

image

Because of the size of the graph, the labels display only one date for each of the eleven months.

The graph on the right, on the other hand, has data points only for each of the dates in its data series. (The size of the graph may cause alternate data points to be un-labeled, so the graph might need to be widened somewhat so that each data point has a label. You may also have to format the axis.) Because the X-Axis data is self-explanatory, I have removed the legend from both graphs.

The secret, to paraphrase Fried Green Tomatoes, is in the formula. Actually, it is in a function that the formula uses. TEXT() is a very versatile function, well worth adding to your Excel arsenal

In this example, I have used TEXT() to convert the date values in column B to text values that include the name of the weekday on which the date occurs.

The TEXT() function converts numeric values to text . It takes two arguments, a reference to a cell containing a numeric value and a string to indicate how the text should be formatted.

For example, the formula in cell J3 is:

=TEXT(B3, “ddd mmm dd”)

The format string here specifies that the text should begin with the three character abbreviation for the day name followed by a space, the three character abbreviation for the month name, another space, and finally a two digit number for the day.

There are many variations on the format string. Search the Excel help files for Text Functions or point your browser to Office On-line. The help article gives full details of how to specify the format string (the second argument of the function) depending on the numeric value you are trying to convert to text.

The point in this example is that by converting the dates in column B to text, and using the data in column J for the series labels, we can fool Excel into displaying date without having it automatically treating the dates a continuous series from the earliest date to the latest date.

Thanks Natasha for raising the question.