Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Suppose I have the following Excel data:

+------+-------+--------+
| Item | User  | Status |
+------+-------+--------+
| MTB  | Bob   | A      |
| QCD  | Bob   | A      |
| MTB  | Alice | B      |
| DFXR | Carol | A      |
| QCD  | Carol | B      |
| MTB  | Carol | B      |
| MTB  | Carol | A      |
| DFXR | Carol | B      |
+------+-------+--------+

I can pivot this data to get a count of items per user by item type, by putting User in the Row Labels box, Item in the Column Labels box, and Count of Item in the Values box. This gives the following pivot:

+-------------+------+-----+-----+-------------+
| Row Labels  | DFXR | MTB | QCD | Grand Total |
+-------------+------+-----+-----+-------------+
| Bob         |      |   1 |   1 |           2 |
| Alice       |      |   1 |     |           1 |
| Carol       |    2 |   2 |   1 |           5 |
| Grand Total |    2 |   4 |   2 |           8 |
+-------------+------+-----+-----+-------------+

I want to create a pivot that will make two separate sets of Count of Item columns: one for Status="A" and the other for Status="B". It would look like this:

+-------------+----------+---------+---------+----------+---------+---------+-------------+
| Row Labels  | DFXR (A) | MTB (A) | QCD (A) | DFXR (B) | MTB (B) | QCD (B) | Grand Total |
+-------------+----------+---------+---------+----------+---------+---------+-------------+
| Bob         |          |       1 |       1 |          |         |         |           2 |
| Alice       |          |         |         |          |       1 |         |           1 |
| Carol       |        1 |       1 |         |        1 |       1 |       1 |           5 |
| Grand Total |        1 |       2 |       1 |        1 |       2 |       1 |           8 |
+-------------+----------+---------+---------+----------+---------+---------+-------------+

I'm flexible about how the column names would appear; the important thing is that the counts of Item get broken out into two sets based on Status. How would I do this? Can it be done with a pivot table?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

This can easily be done with a Pivot Table. All you need to do is set it up correctly:

  1. Select User for your Row Labels
  2. Select Item for your Values
  3. Select Item again for your Column Labels
  4. Select Status for your Column Labels also.

Here's what it will look like:

Pivot with 2 categories

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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