Free Microsoft Excel 2013 Quick Reference

- Dynamic pie Chart Help!
- Pie chart values vanish when moved?!
- Having a pie chart ignore data fields equal to "0"
- Change Pie Chart segment color based on value in source data
- Histogram or pie chart or?
- How do I set up a pie chart to show % of same value entries
- Font Size for Values in Charts
- Pie-charting non-numeric data
- Aggregating Data in Other Category on Pie Chart
- Dynamic Bar of Pie Chart: Series1 Label & Value Issue
- Pie Chart Data Labels
- Pie chart data labels not updating
- Pie chart not show Category Names where there is no data
- Pie Chart Labels
- Excel doesn't add up Pie Chats correctly
- Help with a pie-chart format in a VBA code
- Histogram or pie chart or?
- Pie - Pie Chart
- Pie Chart Troubles
- Creating a percentage chart based on values

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.

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?

Paul.

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

variable.

So for example:

X Y Z

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?

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?

--

Doug

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.

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

'template'.

Does anyone know how I can achieve a larger font within the chart? I'm

working with XP Professional - 2003 Excel.

Thank you!

~Jaci

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?

Gina

"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

> http://PeltierTech.com/

> _______

>

>

> 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?

> >

> > Hmmm...so 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

> >>http://PeltierTech.com/

> >>_______

> >>

> >>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?

> >>

> >>

>

>

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?

--Tom

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!

(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

independently.)

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

percentages.

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

--

Orlando Vazquez

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:

Jan:45%

Feb: 65%

Mar: 56%

Apr:

May:

Jun:

etc.

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?

Thanks!

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

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: futon_mouse@hotmail.com

Many grateful thanks,

~ Peter

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 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xl3DPieExploded ActiveChart.ApplyLayout (6) ActiveChart.ChartArea.Select '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 Next End With End Sub

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

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.

Thanks!

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:

1

1

1

1

2

2

3

4

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.