Filter Pivot Table by Cell Format

This can actually be done, if you have the right data in your table. The trick is to create a pivot table in a tabular view with no more than one value column, if at all possible. If you can do that, you should be golden to embark on the magnificent task of being able to filter your pivot table by background or font color – even conditionally formatted colors!

The trick is to create your pivot table, like so:

Task Resource Start Finish Remaining Work
Task 2 Edmund 5/16/2014 6/25/2014 5
Task 55 Susan 5/17/2014 6/26/2014 74
Task A Lucy 5/15/2014 6/12/2014 10
Task Something Peter 5/18/2014 6/27/2014 4

Then create some conditional formatting – in this case let’s say we are doing a “duplicate value” check on another column (in this case G) to see if the resource in question is unable to do her work because she’s lost in Narnia. (or something, use your imagination)

Next, go into your Pivot Table Tools => Options and remove the header fields. Now make sure there is an empty row right above the pivot table and you can add the fields in manually:


Select that whole row and go to the ribbon and select Data => Filter

Now go to the filter drop-down on the column in question and you will see that you can filter on Font or Cell color.


Booya. You’re welcome.

2 thoughts on “Filter Pivot Table by Cell Format

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s