Free Microsoft Excel 2013 Quick Reference

Pie charts showing values Results


Ive been trying to do this all day now, Ive tried looking thought the posts but i cant seem to find ( or understand the answer). Basically i am doing a dynamic pie chart of leaving reasons for employees. I have a list of all possible reasons and a count for each reason linked from another sheet in the next column.. Obviously some reasons do not yet a value but i need to keep it showing on the table.

I can do the chart easily, However all reasons with a zero or no value still appear on the Legend which i do not want to happen until that particular reason has a value.

I have read changing it to NA(), "", and 0 however none of these work for m. They still show on the chart and legend , Either as a blank name or a #N/A name, its driving me nuts!!

I have checked in the options > chart > plot empty cells. However it is shaded.

I have read earlier that someone used the offset function to do this, however i am unfamiliar with this so do not know how it works can someone explain it ? simple terms

Surely there must be an easy way ?

Any help is much appreciated.

Hi all,

I have created a pie chart to show my data, and when I adjust the values position by dragging them away from the chart segments, to avoid overlapping values, they vanish!

Can any one tell me why, and how to cure it?


I am building a template that will be updated daily.

The template will be used ~50 times, but will be fully automatic as its
data source is dynamic.

What I need to do is set up a pie chart that will ignore cells with
values of zero.

For example, In A2:A6 I have categories.
In B2:B6, C2:C6, D26, etc.... I have numerical values for a different

So for example:
A 5 3 7
B 0 5 1
C 1 4 2
D 9 0 6
E 2 4 0

Only that will go on for about 50 columns.

I need to build one pie chart as a template, that will show the data
for X in a pie chart, ignoring all zero values. (5,1,9,2).

And then, further I need to find a way to lock the colors.

So for example the "A" slice, if its not a 0, will always be red, the B
slice always blue, etc....

How can I go about doing this, or is it even possible?

I am creating a training matrix which shows current status of training
progress. I want the pie chart to always display three equal segments. I want
each segment color to change based on the data input in the source data.
Hopefully this is an adequate definition of what I am trying to do?

Greetings from the UK...

I have a list of around 1000 numbers, all integers whose values can range
from 1 through to 4000. Most of these numbers repeat themselves in the
list .
What I want to do is produce a chart showing the number of times each
descrete number appears , its value and the percentage of times it appears
in the list .
I think ( dangerous for me !) I should be looking at a histogram or a even a
pie chart but so far my attempts (using the data analysis and chart
wizards) are not what I want.

As an example a list of 12 numbers could be 2, 3, 2, 4, 2, 4, 2, 2, 4,
2, 4,3 and what I want to get to is a chart showing that:
"2" appeared 6 times in the list equating to 50% of the 12 entries,
"4" appeared 4 times in the list equating to 33.3% of the12 entries,
"3 " appeared 2 times in the list equating to 16.6% of the 12 entries

value anyones help.

how can I show a pie chart with % of same value entries in a column eg.

values of 1,1,1,1,1,2,2,2,3,3 I want a chart to show 50% are 1's, 30% are 2's
and 20% are 3's

I'm creating very simplistic charts showing just one value. Example: a
pie chart showing 97% satisfaction. The 3% will be there but I deleted
the text showing the value.

My desire is to showcase the 97% - the document will be printed as an
11x17 poster, but I'm doing it in excel b/c we are creating multiple
sheets for multiple posters.

The problem is that I can't make the 97% any larger than 24 pt. font
without the % sign going on a second line. I would like to make the
font 48 pt or larger.

Also, I can not put the 97% in a text box on top of the chart b/c it's
linked to a data sheet and we'll be creating multiple sheets from this

Does anyone know how I can achieve a larger font within the chart? I'm
working with XP Professional - 2003 Excel.

Thank you!


I am following the advice given but my pivot table keeps defining 'Yes' or
'No' as a value of 1. So when it's listing whether employee no. x used a, b
or c it counts 'yes' and 'no' as '1' each so even when employee only used a
and not b or c it still classes them both as a positive (1) and so my totals
are coming out '3' everytime because there are 3 options, even when I have
only put 'Yes' for 1 option. What am I doing wrong?

"Jon Peltier" wrote:

> Doug -
> Take the survey data, laid out like this:
> color fruit
> 1 red apple
> 2 green orange
> 3 red orange
> 4 green banana
> 5 blue apple
> 6 red grapes
> 7 green apple
> 8 red orange
> 9 green orange
> 10 blue banana
> and rearrange it like this:
> item value
> 1 color red
> 2 color green
> 3 color red
> 4 color green
> 5 color blue
> 6 color red
> 7 color green
> 8 color red
> 9 color green
> 10 color blue
> 1 fruit apple
> 2 fruit orange
> 3 fruit orange
> 4 fruit banana
> 5 fruit apple
> 6 fruit grapes
> 7 fruit apple
> 8 fruit orange
> 9 fruit orange
> 10 fruit banana
> Make a pivot table, putting item and value in the Rows area and Count of value in
> the Data area:
> Count of value
> item value Total
> color blue 2
> green 4
> red 4
> fruit apple 3
> banana 2
> grapes 1
> orange 4
> One pivot table contains all the data. You need to make a non-pivot chart to graph
> only some of this data, select a blank cell not touching the pivot table, start the
> chart wizard. Step 1, select a pie chart. Step 2, click on the Series tab, then
> click Add, and for categories select the range of colors and for values select the
> totals next to the colors. There's your first chart. Make a copy of the chart, click
> on the pie, and drag and resize the purple and blue highlight rectangles to change
> from the colors to the fruits.
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> _______
> Doug VanOrnum wrote:
> > Thanks Jon -- a clarification: One analogy would be a column of "favorite
> > colors". Maybe there are six colors to choose from. A "pivot table" would
> > count the number of instances of "orange", for example, as well as the other
> > colors? Then I could make a pie chart of the pivot table?
> >
> > that means if my questionnaire has 45 questions total, I'd have to
> > set up a separate pivot table for each question, then make a chart or graph
> > for each...sounds pretty inefficient. Is there a better path I need to take
> > to get to the desired end-result?
> >
> > "Jon Peltier" wrote:
> >
> >
> >>So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range,
> >>and you can produce counts of each value. Then make a chart of these counts.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>
> >>_______
> >>
> >>Doug VanOrnum wrote:
> >>
> >>
> >>>I would like to chart and graph responses to a survey that are non-numeric in
> >>>nature, such as a bar graph for "yes" and no" responses to a question. Or in
> >>>particular a pie chart that shows how many people picked option "P" vs. "K",
> >>>"J" or "N". For the pie chart I have a column of data cells, each containing
> >>>one of the four letters.
> >>>
> >>>In short, how do I accomplish charting and/or graphing non-numeric responses?
> >>
> >>

Excel 2007

Basic Question
Is there a way to make a pie chart ignore "empty" cells? Note that the
cells are not truly empty. The display of data in the cells has simply been
"blanked" out using this formula: =IF(B19 1,000.

3) I created a calculated column that shows the value of the category only
when the value is > 1,000.

This gives me two columns that show the category names and values for just
those categories where the value is greater than 1,000. I then built my
chart using the two calculated columns, hoping that the rows which don't
display data would be ignored. Unfortunately, it doesn't seem to work that
way. What I ended up with is a chart that shows blank items in the legend.

How can I make it so that the chart shows only the categories where the
value is greater than 1,000, plus the Other category, and not show blank
items in the legend?


I have created a Bar of Pie chart and everything works great. I even have
named ranges on my data so that the bar portion automatically updates when
the data changes. However the Series1 Label on the pie itself does not
change to reflect the new total so that it is the sum of all the values in
the right column below. Does that make sense?

I have one series and my data looks like this:

Contracts 100
Name1 100
Name2 150
Name3 200
Name4 75
Name5 50
Name6 25

The pie portion has Contracts & Other as labels with the totals of 100 and
617 respectively (the 617 total is from the last time I changed the chart).
The Other label should be 600. The Values named range is all the numbers in
the right column and the Labels named range is all the items on the left.

I would also like to have the label that shows "Other" to be named
"Programs" but do not know how to do that when the chart is refreshed each
time. Thank you for any help that you can provide!

I've created a pie chart that shows the Category name in the default location
(outside of the pie chart itself). That's the easy part.

I need to show the Value but do not want that Value to appear after the
Category name.

I want the Value to overlay its respective pie wedge, like the Column chart,
which places the Value over each column (which can be moved and edited


Using Excel 2003; I have a pie chart whose chosen Chart Options/Data Labels
are "Category Name" and "Percentage" which in turn are linked to a
concatenated formula showing manager name, dollar amounts and then

When these linked formula values are updated, the chart data labels do not
automatically update and require me to go to Chart Options/Data Labels and
deselect then reselect "Category Name" and "Percentage" to force them to
update and then I have to reformat them, etc, etc.

Am I doing something wrong or does excel really not update these values on
the fly? My calculation is set to automatic.

Please let me know if it is possible to have them automatically update.

Thank you.

Orlando Vazquez

I am using Excel 2003.

I have a Pie chart with the 12 months of the year as my source data. Some months have data, some not yet.

Sample data:
Feb: 65%
Mar: 56%

In Chart Options, Data Label, I checked Category Name and Value.

The months with values are plotted and look fine, but the months (Category Name) with no values are showing on the pie chart on top of each other and it looks messy.

For example, Jan, 45%; Feb, 65%; and Mar, 56% are plotted on the pie chart. But the Category Names of the rest of the 9 months are shown as text boxes on top of each other at the top of the pie. Apr, May, Jun, etc are all in text boxes on top of each other in a unreadable mess.

I know I could make the source data only contain the 3 months with data, but I don't want to have to expand my source data each month.

How can I make the pie chart not show the Category Names where there is no data? Is it possible?


I recently discovered a curious "feature" of Excel. If you create a pie
chart, and label the slices of the pie with data labels that include the
percentage, Excel sometimes adjusts the values in unexpected ways.

As far as I can tell, this only happens when the percentages are formatted
as whole-number percents (e.g., "57%"). In this situation, Excel will
adjust values so that the sum of all of the percentages around the pie is
exactly 100%.

I discovered this situation when I created a pie chart that had 7 slices.
The percentage associated with one of the slices appeared in the data as
"0.572702...". However, Excel displayed the value in the pie chart as
"56%". It did not seem to matter if I generated the pie from actual data,
or if I manually converted the data into percentages. As it happened,
several of the other values had been rounded up, so Excel took it upon
itself to change this value to compensate and make the percentages add to
exactly 100%.

Here are the percentages for each of the slices in the pie. The first
column of percentages is what's displayed if I format for 4 digits; the
second column is the display if I reduce to 1 decimal digit; and the third
column is the display if I show only whole-number percents:

1 - 2.0687% 2.1% 2%
2 - 3.7651% 3.8% 4%
3 - 0.7778% 0.8% 1%
4 - 3.0802% 3.1% 3%
5 - 18.5323% 18.5% 19%
6 - 57.2702% 57.3% 56% <===
7 - 14.5058% 14.5% 15%

Total 100.1% 100%

As you can see, when I format the data labels to display a decimal digit,
then Excel correctly rounds the displayed value for Slice 6 to "57.3%".
With this format, the sum of the percentages around the pie did not add up
to exactly 100% -- the sum of displayed values was 100.1% in this case. If
I formatted the data lables to display no decimal digits, but forced the
display to contain a decimal point, then Excel rounded the value for Slice 6
to "57.%". The unauthorized adjustment occurred only when I formatted for
whole-number percents.

Has anyone else out there observed this kind of behavior?

-- David Benson

Thank you for reading me. I've found a possible problem with Excel 2002 and
would be most grateful if anyone can shed any light on it.

Let's say I want to produce an expanded pie chart showing the incidence of
set data:
2.2 = 30%
4.3 = 20%
5.6 = 23%
6.4 = 27%
= 100%
Highligting the two columns, and requesting an expanded pie chart. Gives
results which don't match. For example the first field 2.2 should occur 30%
but holding the mouse over it tells me that the value 2.2 = 12%

It took me ages to find this group, so would be most grateful if you could
e-mail with any suggestions / explainations:

Many grateful thanks,
~ Peter

Hi all,

My doubt is probably simple for most of you, but I'm pretty new at this so, better to ask you than keep looking through google.

I've edited a code in word to create a pie chart and format it according the name of the colors using the select case. When this code is run in a excel 2007 it runs pretty good, but once I write the same code in a excel 2003, it gives me a runtime error which say: "Object variable or With block variable not set". below in the code I show where this message appears.

My problem is right in this section, how do I solve this issue in excel 2003, once in excel 2007 is working properly.

Thanks in advance

The code is:
Sub Pie_Chart()
'Choosing what type of chart
    ActiveChart.ChartType = xl3DPieExploded
    ActiveChart.ApplyLayout (6)
'Creating the Chart
  With ActiveChart.SeriesCollection.NewSeries
        .Name = ActiveSheet.Range("C2")
        .Values = ActiveSheet.Range("C8:C10")
        .XValues = ActiveSheet.Range("B8:B10")
  End With
'Placing the Chart
  With ActiveChart.Parent
    .Left = 1
    .Width = 215
    .Top = 267
    .Height = 150
  End With
'Formating the Chart
   Dim iPoint As Long, nPoint As Long
  With ActiveChart.SeriesCollection(1).Select 'THE RUN TIME ERROR ON EXCEL 2003 COMES RIGHT HERE
     For iPoint = 1 To .Points.Count
          Select Case WorksheetFunction.Index(.XValues, iPoint)
                 Case "Yellow"
                    .Points(iPoint).Interior.ColorIndex = 6 ' Yellow
                 Case "Red"
                    .Points(iPoint).Interior.ColorIndex = 3 ' Red
                 Case "Green"
                    .Points(iPoint).Interior.ColorIndex = 4 ' Green
           End Select
   End With
End Sub

Greetings from the UK...

I have a list of around 1000 numbers, all integers whose values can range
from 1 through to 4000. Most of these numbers repeat themselves in the
list .
What I want to do is produce a chart showing the number of times each
descrete number appears , its value and the percentage of times it appears
in the list .
I think ( dangerous for me !) I should be looking at a histogram or a even a
pie chart but so far my attempts (using the data analysis and chart
wizards) are not what I want.

As an example a list of 12 numbers could be 2, 3, 2, 4, 2, 4, 2, 2, 4,
2, 4,3 and what I want to get to is a chart showing that:
"2" appeared 6 times in the list equating to 50% of the 12 entries,
"4" appeared 4 times in the list equating to 33.3% of the12 entries,
"3 " appeared 2 times in the list equating to 16.6% of the 12 entries

value anyones help.


I need to create a pie chart ..

i am having value like A 20% B 30% C 50 %
and C 50% is divided into C1 10 % C2 15% C3 25%
now i need a chart to give whole fig of A B C also the division of C in second chart by showing some connectivity. (like pie to pie or pie to chart)
Any one can guide me ..
i am using excel 2007

Thanks in advance

I need to create a pie chart based on the total of the values in the cells, not 100% which is what it seems to be defaluting to - can that be done?

Example, I have two figures, 83 and 11. I need the chart to consist of two slices, one showing 83%, the other showing 11%.

Right now, my chart is showing 88% and 12%, assuming the end must be 100%.

Any thoughts? I scanned the charts forum but didn't see anything that was an obvious posting about this same issue.



I'm trying to create a pie chart showing a percentage breakdown based upon a set of values that includes repeating values.

For example, for the list:


I'd like a pie chart that shows the pentage breakdown of the four unique values, not represent each line as it's own slice.

I'm not sure how to do this in Excel without manually calculating the percentages based. Any suggestions woul dbe much appreciated.

No luck finding an answer? You could always try Google.