Values of slices on pie chart for gauges

[DashboardNovice]

I'm working on gauges here.
http://chandoo.org/wp/2008/09/09/excel-speedometer-chart-download/

I downloaded the Excel file for the dial chart. I shaded the slices on the pie chart to "see" everything. I changed the gauge value to 68. The second largest slice and the slice for the needle are BOTH showing as 68 when both data labels are shown. How can this be?

Also, for the data label for the needle, how do you get it to display right outside of the needle?

Chart attached.

 

[NARAYANK991]

Hi ,

I am not sure about your questions ; can you clarify your first question with some more explanation ?

As far as your second is concerned , can you see this file ?

Narayan

 

[DashboardNovice]

Hi ,

I am not sure about your questions ; can you clarify your first question with some more explanation ?

As far as your second is concerned , can you see this file ?

Narayan

Yes, I can open the file you attached. As far as I can tell it's the same as the one I uploaded.

Why does the data label of 68 appear twice? Also, how do you get the second data label of 68 to appear outside of the graph and also so close to the slice for the needle?

 

[NARAYANK991]

Hi ,

I am no closer to understanding what your problem of 68 is.

The change to the system is to be made in cell E5 ; this reflects in the needle moving to the entered value.

The data label is only one ; the only chart that I can see is Chart 2 ; the others are there only to show you how the final chart has been built up ; they can be deleted.

To get the data label close to the outside of the dial , select Outside End instead of Best Fit , and then increase the height of the chart ; as you increase the height , the size of the pie-chart decreases.

See this file.

Narayan

 

[DashboardNovice]

Hi ,

I am no closer to understanding what your problem of 68 is.

Narayan

Thank you for your reply. Regarding the issue with 68, I downloaded your file, then I moved the pie chart off the doughnut chart. To make each part of the pie chart easier to see, I shaded them. See the attached file.

Notice the small 68 (circled in red) on the orange slice. To get this, I selected the orange slice and added data labels. So far so good.

But also notice that the dark blue slice (the needle) also has a data label with 68 outside of it (circled in red). This is what I was talking about. I don't understand how the needle can have 68 for a data label, when its value is only 1.

I must be overlooking something very obvious.

 

[NARAYANK991]

Hi ,

If you see the data for the pie-chart , the series is defined by :

='gauge chart'!$F$12:$F$14

The Axis label range is defined by :

='gauge chart'!$F$11:$F$13

So what is in F11 ?

You would think the header label Indicator is there , but see that E11 and F11 are merged ! For good reason , so that F11 is blank ; thus when you add data labels to the pie-chart , the 68 that you see against the needle is the Category name , while the 68 inside one slice of the pie is the value !

Since the slices other than the needle are hidden , these value labels do not matter.

The data label against the needle is outside while the other data labels are inside because the selection is Best Fit.

Narayan

 

[DashboardNovice]

Hi ,

If you see the data for the pie-chart , the series is defined by :

='gauge chart'!$F$12:$F$14

The Axis label range is defined by :

='gauge chart'!$F$11:$F$13

So what is in F11 ?

You would think the header label Indicator is there , but see that E11 and F11 are merged ! For good reason , so that F11 is blank ; thus when you add data labels to the pie-chart , the 68 that you see against the needle is the Category name , while the 68 inside one slice of the pie is the value !

Since the slices other than the needle are hidden , these value labels do not matter.

The data label against the needle is outside while the other data labels are inside because the selection is Best Fit.

Narayan

I think I'm starting to see it. I am having trouble understanding the category labels for a pie chart. Categories are associated with the X axis, but with a pie chart there is no X axis. So where do the categories show up on a pie chart? This might help me understand this a little better.

 

[NARAYANK991]

Hi ,

In a pie-chart , the categories are just the labels ; for example suppose you wanted to show election results for parties as follows :

Democrats ........... 127 seats

Republicans .......... 343 seats

Others ................. 15 seats

You could have the above data in columns A and B , and creating a pie-chart would make the text Democrats , Republicans and Others the categories.

So , when you add data labels , specifying Category and Value , you would have the following 3 labels correctly attached to the pie slices :

Democrats , 127
Republicans , 343
Others , 15

However , if in creating the pie-chart , you specify only one column , column B , the data itself is used as the categories , then 127 , 343 and 15 become the categories.

So , when you add data labels , specifying Category and Value , you would have the following 3 labels correctly attached to the pie slices :

127 , 127
343 , 343
15 , 15

To add to the confusion , suppose you specify the data range as B2:B4 , and offset the category range ( horizontal axis labels range ) , specifying it as B1:B3.

Now , when you add data labels , specifying Category and Value , you would have the following 3 labels :

, 127
127 , 343
343 , 15

And if you remove the Value portion , specifying only the Category , you would have :

nothing for the first pie slice
127 for the second pie slice
343 for the third pie slice

Narayan

 

[DashboardNovice]

Hi ,

If you see the data for the pie-chart , the series is defined by :

='gauge chart'!$F$12:$F$14

The Axis label range is defined by :

='gauge chart'!$F$11:$F$13

So what is in F11 ?

You would think the header label Indicator is there , but see that E11 and F11 are merged ! For good reason , so that F11 is blank ; thus when you add data labels to the pie-chart , the 68 that you see against the needle is the Category name , while the 68 inside one slice of the pie is the value !

Since the slices other than the needle are hidden , these value labels do not matter.

The data label against the needle is outside while the other data labels are inside because the selection is Best Fit.

Narayan

Ok. Now I understand what you mean. Then at this point, I have only the following questions:

1. How do you set the X categories so that they are from F11:F13? Even though I saw only 1 data series when I expected to see 3, I attempted to Edit the horizontal axis. Since F11 is merged with E11, I couldn't grab it. So I selected F12:F13 at first, then I changed F12 to F11. So my horizontal axis label now reads F11:F13 like it should. But the preview didn't catch the blank is F11 so it didn't work.
2. In order to answer, #1, first I tried plotting the values in F12:F14. This results in 3 different colors/slices. Therefore doesn't this mean 3 different data series? But then I selected one of the slices, right clicked, Select Data, so I can click Edit on the horizontal axis labels. But to my surprise, when the Select Data Source window appeared, I only saw one data series, not three like I was expecting.

 

[DashboardNovice]

Hi ,

In a pie-chart , the categories are just the labels ; for example suppose you wanted to show election results for parties as follows :

Democrats ........... 127 seats

Republicans .......... 343 seats

Others ................. 15 seats

You could have the above data in columns A and B , and creating a pie-chart would make the text Democrats , Republicans and Others the categories.

So , when you add data labels , specifying Category and Value , you would have the following 3 labels correctly attached to the pie slices :

Democrats , 127
Republicans , 343
Others , 15

However , if in creating the pie-chart , you specify only one column , column B , the data itself is used as the categories , then 127 , 343 and 15 become the categories.

So , when you add data labels , specifying Category and Value , you would have the following 3 labels correctly attached to the pie slices :

127 , 127
343 , 343
15 , 15

Great explanation. I understand now. But when I plotted only column B, I found that the numbers themselves did not become categories. Instead, the categories defaulted as 1, 2, and 3 so that the data labels showed up as:

1,127
2,343
3,15

How did you get the numbers themselves to appear as labor categories? I am guessing that the default is 1, 2 and 3, then to get them to appear as 127,127 etc. you had to edit the horizontal axis labels? Or did you get 127,127 etc. to appear as the default as soon as you graphed this? If so how?

 

[NARAYANK991]

Hi ,

Let us start from the beginning !

1. Suppose our data is in column A , in the range A2:A4 , as follows :

A2 : 30
A3 : 1
A4 : 300

A1 is blank.

2. Select the range A2:A4 , and click on Insert , Pie , 2D Pie. A pie-chart using the above data is created.

3. Click on Data Labels , Best Fit. The slices will be correctly labelled 30 , 1 , 300 ; the 1 alone will be outside the pie , while the others will be inside the pie.

4. Right-click on the data labels , Format Data Labels , click in the checkbox labelled Category , and uncheck the checkbox labelled Value. The slices will now be labelled 1 , 2 , 3.

5. Click on the chart , and click on Select Data , Edit Horizontal ( Category ) Axis Labels , and enter the range A1:A3.

6. You should now see one slice which is without any label , the needle slice , which earlier was showing 1 then 2 , should now be labelled 30 , while the third slice should be labelled 1.

The order of steps 4 and 5 can be interchanged.

Narayan

 

[DashboardNovice]

Hi ,

Let us start from the beginning !

1. Suppose our data is in column A , in the range A2:A4 , as follows :

A2 : 30
A3 : 1
A4 : 300

A1 is blank.

2. Select the range A2:A4 , and click on Insert , Pie , 2D Pie. A pie-chart using the above data is created.

3. Click on Data Labels , Best Fit. The slices will be correctly labelled 30 , 1 , 300 ; the 1 alone will be outside the pie , while the others will be inside the pie.

4. Right-click on the data labels , Format Data Labels , click in the checkbox labelled Category , and uncheck the checkbox labelled Value. The slices will now be labelled 1 , 2 , 3.

5. Click on the chart , and click on Select Data , Edit Horizontal ( Category ) Axis Labels , and enter the range A1:A3.

6. You should now see one slice which is without any label , the needle slice , which earlier was showing 1 then 2 , should now be labelled 30 , while the third slice should be labelled 1.

The order of steps 4 and 5 can be interchanged.

Narayan

Thank you for taking the time to write such a thorough explanation. I understand each of these steps.

On my post (#9) on this topic, can you determine why I can't grab F11 (question #1).

Also, regarding question #2, do you know why there is only one data series listed after selecting Select Data even though 3 data series appears on the pie chart?

 

[NARAYANK991]

Hi ,

A pie-chart can have only one series ; each slice of the pie is a data point , and together , all of the data points form one series.

Regarding your question , you yourself seem to have answered it , so I do not know whether any explanation is needed ; since E11 and F11 are merged , you cannot select the range F11:F13 by dragging ; the only way is to manually enter F11. Or , you can unmerge E11 and F11 , using Center Across Selection ; this will allow you to specify F11:F13 using the mouse.

Narayan

 

[DashboardNovice]

Hi ,

A pie-chart can have only one series ; each slice of the pie is a data point , and together , all of the data points form one series.

Regarding your question , you yourself seem to have answered it , so I do not know whether any explanation is needed ; since E11 and F11 are merged , you cannot select the range F11:F13 by dragging ; the only way is to manually enter F11. Or , you can unmerge E11 and F11 , using Center Across Selection ; this will allow you to specify F11:F13 using the mouse.

Narayan

I tried this a few more times and here is what I found out. When I reset the X labels, initially I will select F12:F13 (since I can't grab F11 because it is part of a merged cell), then change F12 to F11, click ok. The chart doesn't update.

Then I have to go in to edit the X labels again. Although I changed F12 to F11, when I go back in, it says F12. So once again, I change F12 to F11, then click ok. Now the chart updates.

So I have to change F12 to F11 twice instead of once. I don't know why.

I hope this helps other people who had the same problem.