Pivottable: summarize multiple columns with similar data

Question

I am creating pivot tables to summarize survey response data in an excel file.

Part of the data shows answers to "How important is x to you?" questions:

| Subject A | Subject B | Subject C | Subject D | Subject E |
-------------------------------------------------------------
| Very      | Not at all| A little  | Very      | A little  |
| Not at all| Not at all| Very      | N/A       | Very      |
| Not at all| Not at all| Very      | Very      | N/A       |
| Very      | Not at all| Not at all| Very      | A little  |
| N/A       | Not at all| Very      | Very      | A little  |

In my pivot table, I would like to summarize the number of occurrences for each answer per subject respectively:

             | Subject A | Subject B | Subject C | Subject D | Subject E |
--------------------------------------------------------------------------
| Very       | 2         | 0         | 3         | 4         | 1         |
| A little   | 0         | 0         | 1         | 0         | 3         |
| Not at all | 2         | 5         | 1         | 0         | 0         |
| N/A        | 1         | 0         | 0         | 1         | 1         |

I have spent some time trying to find a way to do this, but to no avail. Google is not being particularly helpful either. Is this because pivot tables simply do not support this kind of summary, or do I simply need more coffee? Any help would be appreciated.

1

Because I made a typo. :) – Lee White Sep 23 '14 at 10:31

score 0 · Answer 1 · answered Sep 23 '14 at 08:41

You should normalize your data first. Specifically what is described in this article about database normalization as "Eliminate duplicative columns from the same table". Instead of having five answer columns (one for each subject) have each row represent one answer to one question by one subject.

| Question   | Subject   | Answer     |
---------------------------------------
| Question 1 | Subject A | Very       |
| Question 1 | Subject B | Not at all |
| Question 1 | Subject C | A little   |
| Question 1 | Subject D | Very       |
| Question 1 | Subject E | A little   |
| Question 2 | Subject A | Not at all |
| Question 2 | Subject B | Not at all |
| Question 2 | Subject C | Very       |
| Question 2 | Subject D | N/A        |
| Question 2 | Subject E | Very       |
| Question 3 | Subject A | Not at all |
| Question 3 | Subject B | Not at all |
| Question 3 | Subject C | Very       |
| Question 3 | Subject D | Very       |
| Question 3 | Subject E | N/A        |
| Question 4 | Subject A | Very       |
| Question 4 | Subject B | Not at all |
| Question 4 | Subject C | Not at all |
| Question 4 | Subject D | Very       |
| Question 4 | Subject E | A little   |
| Question 5 | Subject A | N/A        |
| Question 5 | Subject B | Not at all |
| Question 5 | Subject C | Very       |
| Question 5 | Subject D | Very       |
| Question 5 | Subject E | A little   |

With a pivot table set up like the following you should get the desired result.

Column Labels: Subject
Row Labels:    Answer
Values:        Count of Question

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Insert a column on the left with =Row() copied down to suit. Unpivot (eg see). Delete left-hand column and pivot with Column for COLUMNS, Value for ROWS and Count of Value for Σ VALUES.

edited May 23 '17 at 12:21

Community

1
1

answered Sep 23 '14 at 10:35

pnuts

58,317
11
87
139

Pivottable: summarize multiple columns with similar data

2 Answers2