pie-charting non-numeric data

[Doug VanOrnum]

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?

 

[Jon Peltier]

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]

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]

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/
_______

 

[Gina O'Brien]

Jon,

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

 

[Debra Dalgleish]

If your source data is like this:

Name Question Response
Joe a Yes
Joe b No
Joe c No
Sam a No

In the pivot table, put Name and Question in the row area, Response in
the column area, and another copy of Response in the Data area, where it
will become Count of Response.

 

[Gina O'Brien]

Ok: having tried your Debra advice I think what I many need is slightly
different than I thought! Because my responses have more than one choice,
e.g.,

Name Consultant Physio Other Treatment
Joe Ys No No
Jane No Yes No
Sam No No Yes
etc.

I think that is what is confusing the pivot table as what I'm putting in is
not a count up of 1 column of yes or no's but 3 options (consultant, physio &
other treatment) or yes or no's. At the moment it's just giving me a
confused jumble! Can it be done?!

 

[Jon Peltier]

Gina -

What you're doing is determining the numbers or percentages of each
answer to each question. Sample data:

Name Ques Response
Joe a Yes
Joe b No
Joe c No
Joe d 1
Sam a No
Sam b Maybe
Sam c No
Sam d 3
Bill a No
Bill b No
Bill c Maybe
Bill d 2
George a Maybe
George b Yes
George c Yes
George d 1

There are many ways to proceed.

Pivot Table 1, drag Question then Response to the Column area, then drag
another copy of Resopnse to the Data Area:

a b c d
Yes Maybe No Yes Maybe No Yes Maybe No 1 2 3
Total 1 1 2 1 1 2 1 1 2 2 1 1

Plot each separate piece of the table, e.g.,

a
Yes Maybe No
1 1 2

in a regular pie chart. To make a non-pivot chart from a pivot table,
select a blank cell away from the table and run the Chart Wizard. In
step 1, choose a chart type as always. In step 2, click on the Series
tab, and define each series independently here. Then continue with the
Wizard.

Pivot Table 2, drag Question to the Row area and Response to the Column
Area, then drag another copy of Resopnse to the Data Area:

1 2 3 Yes Maybe No
a 1 1 2
b 1 1 2
c 1 1 2
d 2 1 1

You can make a stacked column chart (or stacked 100% column chart), and
even a dumb Pivot Chart is passable. Each question is listed along the
bottom of the chart, and the proportions of each response are stacked up.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Gina O'Brien]

Thank you Jon, I've finally cracked it!

Gina.

Gina -

What you're doing is determining the numbers or percentages of each
answer to each question. Sample data:

Name Ques Response
Joe a Yes
Joe b No
Joe c No
Joe d 1
Sam a No
Sam b Maybe
Sam c No
Sam d 3
Bill a No
Bill b No
Bill c Maybe
Bill d 2
George a Maybe
George b Yes
George c Yes
George d 1

There are many ways to proceed.

Pivot Table 1, drag Question then Response to the Column area, then drag
another copy of Resopnse to the Data Area:

a b c d
Yes Maybe No Yes Maybe No Yes Maybe No 1 2 3
Total 1 1 2 1 1 2 1 1 2 2 1 1

Plot each separate piece of the table, e.g.,

a
Yes Maybe No
1 1 2

in a regular pie chart. To make a non-pivot chart from a pivot table,
select a blank cell away from the table and run the Chart Wizard. In
step 1, choose a chart type as always. In step 2, click on the Series
tab, and define each series independently here. Then continue with the
Wizard.

Pivot Table 2, drag Question to the Row area and Response to the Column
Area, then drag another copy of Resopnse to the Data Area:

1 2 3 Yes Maybe No
a 1 1 2
b 1 1 2
c 1 1 2
d 2 1 1

You can make a stacked column chart (or stacked 100% column chart), and
even a dumb Pivot Chart is passable. Each question is listed along the
bottom of the chart, and the proportions of each response are stacked up.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[sonal patel]

Dear All

My question is somewhat same like Mr.Doug

I want to place non-numeric data in data field of Pivot Table..i.e name
Is it possible?

 

[Jon Peltier]

The data field displays numbers. However, you can use non-numeric columns to
generate those numbers.

Start with a list of labels in A2:A20, and a header ("Labels") in A1. Create
a pivot table from the data in A1:A20. Drag the Labels field to the Rows
area and to the Data area. You will end up with a pivot table with a list of
unique labels in the Rows area, and a count of each label in the Data area.

See this short tutorial for an illustrated example:
http://peltiertech.com/Excel/ChartsHowTo/ConsolidateData.html

- Jon