Pivot Tables & Survey Results

Unfortunately, you have an HR-style database: strung out horizontally as in a final report instead of organized in a normal format. At least the data are all on one sheet.

Fortunately, this is a common problem and there is a solution to normalizing the data: https://www.excelforum.com/tips-and-...zing-data.html. What I did was download the spreadsheet in the link and copied your data into the source data sheet. Then I followed the instructions on the the word document in the download.

I ran the Normalized macro and it converted the raw data to normalized data on the Normalized Data sheet.

Then I made an Excel Table out of this. Excel tables know how big they are so if you add rows or columns to them, formulas, pivot tables, charts, etc. built on them still work. No guessing on how many rows to use. The table figures it out for you. Here is more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

I added some helper columns only to provide some "cosmetics."

I then created the pivot table off this table.

The row headings are Strongly agree, Agree, etc. (Helper 1), the column headings are the questions (Helper 2) - I've could have used more user-meaningful names like Response and Question, but I wanted to make it obvious that these were helper columns.

In the Values grid, I used the count of the Values Column from the normalized table (again, if you really want to, you could changes these names). I used the values twice. Once as a normal count, and the second as a percent of column total.

You can "prettify" the results by typing in "Number of Responses" where it says "Count of Value" and "Percent" where it says "Count of Value 2."

To the right are slicers. Slicers are fancy filters that work on pivot tables or Excel Tables (but not both at the same time). They can also be used to control multiple pivot tables at the same time as long as the pivot tables are built from the same data source. One nice thing about slicers is that they can control a pivot table without actually being part of the table. Do a web search on slicers. They are very easy to implement, quite intuitive and very powerful.

About the only other thing I had to do to the pivot table was move the row labels to the order in which you want them to appear. They came out alphabetically originally.

It's up to you to keep or discard the Total Counts (Grand Totals).