0

Let me first very quickly explain my project:

For work I sent out a survey to 30 organizations in each state plus DC. It is a 5 question, multiple choice survey (A, B, and C).

I've set up an excel sheet that has all of the organizations in column A; their corresponding state in column B; and then Columns C,D,E,F, and G are for Question 1, Questions 2, etc. Here is an example:

Org State Question 1 Question 2 Question 3 Question 4 Question 5

org1 AL C A C C C

org2 AZ C A C A C

org3 AK A A C C C

org4 TX B B B B B

org5 VA B A B C C

org6 WY B A B A B

There are about 1500 rows of this data. Although not all of them are filled in (since not everyone responded)

I've normalized this data so that I can display it in a pivot chart. This chart allows me to see which answer choice is most popular. I can filter by question number and state(s) too. For example, I can see which choice was picked the most in question 4 in NV, TX, and TN.

My question is this: is there a way i can filter this data to ask which state picked A the most (for all questions) (or just for question 1). In other words, look at trends in the data based on state. So far, whenever I play with the pivot table, it creates 50 bar graphs/question and is impossible to read. I'm looking for a simple way to find anomalies. i.e. every single state answered B for question 1 except Alabama.

Sorry for being so long winded. Hope this makes sense. Thanks in advance.

user3700427
  • 1
  • 1
    Having column headings for each question shows that the data as you have shown it is *not* normalised. If the columns were Org, State, Questions, Answer (increasing the number of rows by a factor of 5) then you might find that Excel's pivot table would be more amenable to the type of analysis you need. – MattClarke Jun 02 '14 at 23:45
  • I agree with @MattClarke. If you are looking to normalize data in Excel, this SO answer of mine might be of interest: http://stackoverflow.com/a/10922351/293078 – Doug Glancy Jun 03 '14 at 01:15
  • Sorry, I should have noted that I've already normalized the data - in the the fashion that @user3700285 has used. I then used that data to make the pivot table. I am just curious to know if I can use that pivot table to discover trends across states. – user3700427 Jun 03 '14 at 15:46

1 Answers1

-1

Load your table into PowerQuery, making sure your first row is used as headers. Go to the transform tab and highlight all the question columns and click "Unpivot". This will give you the following table that can be used in a pivot table the way you want.

Org State   Attribute   Value
Org1    AL  Q1  C
Org1    AL  Q2  A
Org1    AL  Q3  C
Org1    AL  Q4  C
Org1    AL  Q5  C
Org2    AZ  Q1  C
Org2    AZ  Q2  A
Org2    AZ  Q3  C
Org2    AZ  Q4  A
Org2    AZ  Q5  C

... and so on

Rename the "Attribute" and "Value" headers in the output to "Questions" and "Answers" and create your pivot table.

user3700285
  • 39
  • 2
  • Sorry, I should have noted that I've already normalized the data - in the the fashion that @user3700285 has used. I then used that data to make the pivot table. I am just curious to know if I can use that pivot table to discover trends across states. – user3700427 Jun 03 '14 at 15:45