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:

"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:


  • Hmm, it might be easier with COUNTIFS instead of pivottable, since the pivottable doesn't know about the class widths, right? Can you perhaps give a snapshot of your current data table? – Jerry Aug 22 '13 at 18:43
  • @Jerry - Apparently I can't include pictures yet since this is my first question and I only have a 6 reputation. But just to be clarify, the data doesn't need to be classified, the "001 to 025" values are text. So the pivot table doesn't need to classify the data into bins, it just needs to count the number of occurrences of each text value (e.g. "001 to 025"). – Liza Aug 22 '13 at 20:33
  • I can't give a "walkthrough" answer due to limitations, but I believe you can get the result you want with =COUNTIF(interval;NOT(ISERROR(OR(MATCH($col row;first col of data;0);MATCH($col row;second col of data;0);MATCH($col row;third col of data;0))))) – Doktoro Reichard Aug 23 '13 at 10:40
  • @Liza You can still upload a picture to an image hosting site and put the link! ^^ – Jerry Aug 23 '13 at 12:11
  • @Jerry - thanks for the suggestion. Here is a link to pictures of my data with captions explaining what i would like to do. picasaweb.google.com/105380569660384078356/… – Liza Aug 23 '13 at 14:54

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

  • 1
    That's superb :)) I just wonder, why this feature is "hidden". – ZygD May 24 '15 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.