Excel pivot table categorical variables the same in multiple columns (histogram)

microsoft excelpivot table

I am relatively new to pivot tables, and there is one thing that I just can't figure out how to do.

I have a worksheet that has several columns of categorical data. Each column (Groups 1, 2, 3) has the same type of categorical data, i.e. each cell is filled with one of these four values:

"0"
"001 to 025"
"026 to 050"
"051 to 075"

Below is the current format of my data, followed by how I would like my data to be formatted in a pivot table. The pivot table would count the occurrences of each of the four possible values within each group column, and return them in the table. 

Current Format of Data:
              Group 1     Group 2      Group 3  
Record 1    001 to 025   001 to 025   051 to 075  
Record 2    026 to 050   001 to 025       0  
Record 3    051 to 075   001 to 025   051 to 075  

How I would like my pivot table to look:
              Group 1      Group 2     Group 3
0                0            0           1
001 to 025       1            3           0
026 to 050       1            0           0
051 to 075       1            0           2

So far I have not been able to figure out how to do this. I can create a pivot table such that one of the three groups can be displayed (i.e. chart has two columns, one is the row labels, and one is the count values for only one of the groups), but not all three at the same time.

Is what I am trying to do possible? If so, could someone tell me how to do it?

Thanks,
Liza

Additional info:

Here is a link to a google web album of pics of my data with captions explaining what I would like to do:

https://picasaweb.google.com/105380569660384078356/20130823?authuser=0&feat=directlink

Best Answer

What you are looking to do is "depivot" your data, then repivot it to another layout. This link is very useful, for the "depivot" step. I've used this technique for years and it's truly a time-saver!

Here's how to depivot your data.

1.With your layout as shown, hit Alt+D,P. This will bring up the old Excel 2003 Pivot Table Wizard, which looks like this. Select "Multiple consolidation ranges" then "Next": enter image description here

2.Choose "I will create the page fields", and "Next": enter image description here

3.Highlight your range so that it appears in the upper "Range" box, then click 'Add' to copy that range to the "All ranges:" box. Click "Next".enter image description here

4.Choose where you want to put the Pivot table. I usually choose "New Worksheet"; click "Finish". You'll get this:enter image description here

5.Uncheck "Row" and "Column". You'll be left with a one-cell pivot table:enter image description here

6.Double-click the one cell. You'll get your data arranged in a table format on a new sheet. You can now use the table as the basis for your new pivot table layout. Note carefully the placement of the fields in the "Row labels", "Column Labels" and "Values" areas:enter image description here

Related Question