I'm using Excel pivot tables to create a report that will be shared with others. I don't have any VBA or PowerQuery or PowerBI or coding experience, but am decent with Pivot Tables.

My source data for the pivot table is from an Excel file like this (fake example for privacy):

Project # Task Person Who Performed Task Person is internal employee?
1 Paint car John Yes
1 Quality control Chris Yes
2 Paint car Matt No
2 Quality control Chris Yes
3 Paint car Wilson No
3 Balance Tires Jeff No

I want to be able to answer the following question:

For projects that had a task of "paint car" done by someone who was NOT an internal employee:

  1. How many of these projects had the task of "quality control" performed as well?
  • With the table above, the answer should be "1 project" (project #2)
  1. How many of these projects did NOT have the task of "quality control" performed as well?
  • With the table above, the answer should be "1 project" (project #3)
  1. List the project numbers that meet the criteria for question #2 (above).
  • With the table above, the answer should be "project #3"

The point is to find out which projects have the task of "paint job" being done externally (by a contractor, not an internal employee), and yet are not having any quality control applied, as this represents a quality risk for the business - we aren't sure how good our external contractors are.

Using the table above, is there any way to answer those questions easily with a pivot table? Obviously my data is much more voluminous so I want a pivot table method.

I've tried messing around with stuff in the pivot table but Excel seems to get messed up by the fact that the Project Number never has the task of "Paint car" and "Quality control" in the same row in the data - when I try this, it always says "0" for the "Count of Project #".

New contributor
OsakaDreaming is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • It's simple Filter at Label using the Pivot Table !! 21 hours ago

2 Answers 2


Indeed a very intersting question, but I fear that your data structure makes a PivotTable approach quiet challenging. An alternative to this would be to use a formulaic approach that allows you to answer all of your questions accordingly.

Assuming your data is stored in the range A1:D7, you can put the following LET() statement somewhere on the sheet:

data, $A$2:$D$7,
IFERROR(INDEX(data, MATCH(proj&"Paint car", INDEX(data,,1)&INDEX(data,,2), 0), 2), ""),
IFERROR(INDEX(data, MATCH(proj&"Paint Car", INDEX(data,,1)&INDEX(data,,2), 0), 4), ""),
IFERROR(INDEX(data, MATCH(proj&"Quality control", INDEX(data,,1)&INDEX(data,,2), 0), 2), ""),
IFERROR(INDEX(data, MATCH(proj&"Quality control", INDEX(data,,1)&INDEX(data,,2), 0), 4), ""),
IF(internal1="yes", "internal",
IF((internal1="no")*(internal2="yes"), "checked", "to be checked")),
HSTACK(proj, task1, internal1, task2, internal2, criteria)

Basically, you are specifying your data set followed by project numbers. Then you wanna extract the "Paint car" tasks and whether it is performed internally or not; the same idea for "Quality control". Based on this data, you define your criteria to answer your questions based on an IF() statement that checks whether the paint was "internally" and flags it as such. If the paint was non-internally and was checked it is flagged as "checked" and otherwise it needs "to be checked".

In theory, you could also apply all formulas in individual columns and drag it down, but the LET() statement allows you to specify everything in one go and returns an array of the output. This implies that you need to have the formula only in one cell, which makes it quite maintainable. In addition, it should be also feasible to make it more dynamic in terms of the activities if needed. If you want to return only the project # and the criteria you can simply adjust the arrays in HSTACK().

The final output looks as follows (headers are added manually):


Thus, to answer your questions:

  1. Project #2 is already checked
  2. Project #3 needs to be checked
  3. The list of projects is automatically given by this approach, once you filter only for the projects that need to be checked

Don't be afraid of power query!

When I first started playing with power query, I was angry, thinking of all the times I'd needed it and had no idea it would be so easy to do ñ

Most of what you need to do can be done just by clicking around. Save a back-up of the original and remember: there's nothing you can do that you can't recover from. 🙃

First query

Highlight the table and, from "Get and Transform" section of the Data ribbon, make a query that reads the table. A new "query editor" window should appear. In the right margin of that window, change the name of that query to whatever makes sense for you.

Query the query

In the home ribbon of the query editor window, there's a button called "Manage..." and when you click it, one of the child-choices is "Reference". Click it, and you'll start a new query that uses your first query. Rename this second query to "Paint car" and then use the buttons on the right of the columns to filter this table so that it's only "Paint car" tasks.

Then, go back to your first query and do it again (Manage... Reference) and rename this third query to "Quality Control". Click the filter options on the Task column header to add a filter to only show Quality Control tasks.

Merge them

Still in the query editor window, in the list of queries on the left side, click on the "Paint car" query and then create a new query from that (using Manage... Reference), and then, somewhere in one of the ribbons, there's a button to merge queries. Find it, click it, and merge in the "Quality Control" query. In the dialog box that appears, click the project # column for both tables, and use full outer join.

Then click the button on the right of the "Quality control" column we just added to replace that column with columns from matching Quality control rows.

Rename your column names and you can have a table with columns of

  • Project #
  • Painter
  • Is painter internal employee?
  • Quality technician
  • Is quality technician internal employee?

Play time!

Save and load to your worksheet or data model and you could pivot table from there. Another option you now have is to keep going in power query. For example, from that query you could create yet another query and filter for only paint car tasks done by non-internal employees where no quality control was done, and load that into a separate worksheet. Within power query you can pivot, merge, ... heck, you can even *un"pivot!

Once set up, change the source data, click "data - refresh all" in the Excel ribbon, and all the queries recalculate.

Your Answer

OsakaDreaming is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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