Conditional Formatting in Pivot Table in Excel based on text field

2
votes

I would like to color code a pivot table based on a text field within the data.

This is a mock up, as I'm at a loss as to how this can be done. One of the tricky parts is that you can't actually display the text field that is the basis for the color.

Thank you, Neil

Contract Month PayStatus Payment
1 Jan-21 Complete 90
2 Jan-21 Complete 95
3 Jan-21 Complete 93
4 Jan-21 Complete 94
1 Feb-21 Pending 91
2 Feb-21 Complete 95
3 Feb-21 Complete 92
4 Feb-21 Complete 94
2 Mar-21 Complete 96
3 Mar-21 Pending 93
4 Mar-21 Pending 94
2 Apr-21 Complete 95
3 Apr-21 Pending 94

Mock-up Pivot Mock-up (color coded)

enter image description here

1
excelvbapivot-table
Have you reviewed this resource or this one? – PeterT
Hello - Those resources do not address my question. Those resources are setting the format based on a relative value. My question is based on the status (in the data table), that is the intersection of the row and column. In the example above, the Jan review of contract #1 is complete so it is green. The Feb review is still pending, so it is yellow. The color is not related to the actual value in the cell. – Haphols

1 Answers

0
votes

You still don't need to use VBA to get the results you're looking for, but there is a little work to do in order to set it up. The solution is to use Conditional Formatting in which the formula is VLOOKUP. To make it work, you need to modify your table slightly.

VLOOKUP works by finding a unique value in the first column of a table or range. The first column values in your table are not unique, nor are the values in the second column. But if you combine the first and second columns, you can create a unique value. A column must be added to the left of your data to create this unique value:

enter image description here

The formula in Column A combines the values in columns B and C:

=B2&"+"&TEXT(C2,"mmm")

(There's a reason to format the date as just the Month, more later)

What we're ultimately looking for in your formatting rules is when any value in the pivot table, for a given month and contract, matches either "Complete" or "Pending". Because we've set up the data with the helper column, we can now create a `VLOOKUP' that can work for us. So we need a formula that does this:

VLOOKUP(unique value based on month and contract,get the pay status) = "Complete"

VLOOKUP(unique value based on month and contract,get the pay status) = "Pending"

The formula in the VLOOKUP has to reference back to the source data (when it's inside the pivot table). In order to lookup that unique value, we have to create it by combining values from the pivot table. If the VLOOKUP formula was in Cell B6 in the pivot table:

enter image description here

Then the formula will be:

=VLOOKUP($A6&"+"&B$4,Sheet1!$A$1:$E$14,4,FALSE)="Complete"

Notice how the "lookup value" is not a single cell, but we're creating the same unique string/value in the helper column.

In your pivot table, click in the values area ("Sum of Payment") in Cell B6, then select Home -->Conditional Formatting-->Manage Rules. Next "Add New Rule" and then make sure your rule looks like this:

enter image description here

Create a second rule in the same manner and apply it to the pivot table.

enter image description here