How To Analyze Completed Projects Using A PivotTable In Excel

In this tutorial we’ll take a look at how to add a column with a formula outside of our PivotTable for project analysis that pulls results from a Table, then use Conditional Formatting to dress it up.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have a table that contains our data. The table is called “Projects” and contains 7 projects, those who are assigned to it, the phases and costs of each phase, along with whether that phase is complete:

91115-1

I’ll click anywhere in the Table, go to the Insert tab and click on PivotTable:

91115-2

I’ll click OK and it will put the outline of my PivotTable on a new worksheet. I’ll then put the various headings in these quadrants:

91115-3

Next, I’ll make the following changes:

a) Change the Report Layout type to Tabular Form
b) Select to Repeat All Item Labels
c) Format the values as Accounting

And now our PivotTable looks like this:

91115-4

Next I’ll add a column to my table that concatenates the Project # and the Phase:

91115-5

…and I’ll add one next to my PivotTable called “Completed”, where I’ll enter my formula:

91115-6

The formula I’ll be entering is:

=IFERROR(INDEX(Projects[#All],MATCH(CONCATENATE(Sheet2!A4,Sheet2!B4),Projects[[#All],[Concatenate]],0),5),””)

….and here are the results:

91115-7

One last step is to add conditional formatting so that Yes will appear in bold green and No will appear in bold red:

91115-8

Now, when we filter for any of the project leaders, the PivotTable and formula column adjust automatically:

91115-9

Let’s take a quick look at our formula and break it down a bit. Again, the formula we used is:

=IFERROR(INDEX(Projects[#All],MATCH(CONCATENATE(Sheet2!A4,Sheet2!B4),Projects[[#All],[Concatenate]],0),5),””)

It’s just an INDEX/MATCH formula wrapped in an IFERROR function so that if there is an error at the subtotal rows or when the PivotTable is filtered and there is no data, it will just return a blank. You need to enter the formula in the maximum number of rows when the PivotTable is not filtered.

The INDEX formula uses the table as its array, the MATCH function for the row number, and column 5 for the column where it will pull the Yes or No from.

For a bit more detail on using INDEX and MATCH functions together, check out my tutorial here.

Take a few minutes to watch the video and see exactly how I constructed the formula.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , , , ,

No comments yet.

Leave a Reply