Excel Pivot Table with multiple columns of data and each data point in a column can have multiple values

Question

I have a raw data report in Excel 2016 that has hundreds of records, each record contains: a person's name, date, case number, site, and supervisor, along the results of a quality assurance monitoring. The monitoring has about 40 categories, and each category can have a rating of bad, fair, good, N/A.

I need a Pivot table that can filter by person's name, date (month), site, and supervisor. I need the 40 categories to be in the rows, and columns for bad, fair, good, N/A. For each category/row, I need to count the number of times "bad" was entered for that category in the bad column. Then the same for the fair, good, and NA columns for the specific category.

I can't post pictures yet as I am new here, but the links below show a picture of sample table data and the format of what I want the Pivot Table to look like.

Answer 1

You don't mention which version of Excel you are using but assuming you have Excel 2013+ I would load the data into Power Query where you can unpivot the data so that each row is

Person Name | Date | Case | Site | Supervisor | Category | Value

To do this:

1. Enable PowerPivot if required (if using 2016 it is enabled by default otherwise see this link)
2. On the Data ribbon click 'From Table/Range'
3. In Power Query go to the Transform ribbon
4. Select all columns from Person Name to Supervisor ctrl and click on each column or click Person Name and, while holding shift, click Supervisor)
5. Click on the arrow next to unpivot columns and select 'Unpivot Other Columns'. This will melt your data into a tidy format.
6. Close and load this data to the excel sheet (or the data model)
7. Create a pivot table and add Attribute as a row and Value as columns. Any variable can be used as a count in the values section as long as it is present for all records.

The benefit of this approach is that it is one pivot and can be easily refreshed when/if data is updated.

Answer 2

I've had this problem before. It's rather annoying in Excel (plug for Pandas/Python). This solution will work, but comes with a caveat that can be worked around if you're willing to use slicers.

To start, I replicated your dataset and set it up as a table:

Then I made multiple Pivot Tables, filling the Columns and Values Pivot Table Fields with one Category of each of your categories. This will produce a Pivot Table with 3 rows. The first row will read Column Labels with a filter dropdown. The second row will read all the possible values of the column. The third row will be the count of each value in the above column.

Repeat the process in the next available blank cell for the next category, which will produce something like this:

Hide the first row of the first Pivot table, and the first two rows of each of the next Pivot Tables. This will result in the data structure that you are looking for:

The caveat here is that each Pivot Table is it's own, not one Pivot Table of everything. This could slow things down on your system depending on resources, and/or be annoying to keep up with as new data points (in your case, Categories) are added.

To filter the Pivot Table like you're looking for, try using slicers. To do this:

1. Click on the first Pivot Table
2. Click Analyze in the ribbon (menu) up top.
3. Click Insert Slicer.
4. Setup slicers for Name, Date, Site, and Supervisor.
5. Right click each slicer and look for something like Connections. Click this.
6. It should bring up all the possible Pivot Tables in your sheet. Check each relevant Pivot Table (you might have other Pivot Tables in your workbook that don't need this slicer).

Now, every time you click on a setting of a Slicer, your Pivot Tables (remember, we did not setup one giant Pivot Table) will update.

In closing, the general approach to this is not fun. But, it works. In addition, Slicers are a great feature, especially when trying to create Excel Dashboards. I recommend YouTube for inspirations, especially if your project is going to expand! With a few button clicks and some nice colors, you can have some nice data visualization going.