asked on

Pivot Tables in Excel

I have got the following column in a worksheet:

NAME SCALEDESRIPTION OCCUPATION SEX

A A5 MECH M
B A5 CHEM F
C A5 CHEM F
D A5 MECH F
E A6 MECH M
F A6 CHEM F
G A6 MECH F

How can I create a piot table from these
data to summarize the data as follows

OCCUPATION
MECH CHEM
SEX SEX
M F M F
SCALEDESRIPTION
A5 1 1 1 2
A6 1 1 0 1

In other words a pivot table with two Column fields instead of one?

sebastienm

Just drag both fields in the column field area.

In Rows or Columns or Page fields, you can drag as many data field as you want.

Sebastien

Moliere

Highlight the data area and base the Pivot Table off that area.

When setting out the layout, drag ScaleDescription to the left. Then, drag Occupation to the top and follow it by Sex. In the center, drag Name so it says Count of Name.

Finally, double click on the top fields (and the left field) and change Subtotals to None.

The output should look like you want.

starman75

ASKER

I did what Moliere said and it worked fine except that I want to sort the rows in the pivot is a certain manner. Can I do that ( basically I want to move just a single row somewhere else). Also in those cases where there is no record (e.g in the above data ther is no MALE (M) record for A6 in CHEM I get no row in the pivot table). Can I adjust this so as to get a row displaying 0.

Thanks

ASKER CERTIFIED SOLUTION

Moliere

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial