Conditional Formatting a Pivot Table

[OleMiss2010]

Hi all,
It has been far too long since I used these forums. Hopefully, I can find time to use them more often again. I have a question that I'm surprised I've never encountered before.

Is there a way to use conditional formatting in a pivot table based on values outside of the table?

For instance, I have a pivot table in columns A:D (row labels and three value columns) and a binary (0,1) selection in column E. If the binary field has a 1, then I want to highlight the entire row of the pivot table in yellow, otherwise I want the row unchanged. The conditional formating works until I expand/collapse a field in the pivot table. At that point only the row labels maintain the conditional formatting. Is there a way to maintain my formatting for the values?

I'm not sure if this matters, but my table is in compact form.

 

[Luke M]

Hi all,
...The conditional formating works until I expand/collapse a field in the pivot table. At that point only the row labels maintain the conditional formatting. Is there a way to maintain my formatting for the values?....

Welcome back OleMiss2010. Unfortunately, PivotTables have the annoying habit of removing all formats when you refresh/slice them. This is what you are seeing happen with the conditional formatting.

One possible work-around is to record/write a macro of you setting all the formats, and then setting the macro to run when the PivotTable refreshes/changes. This would then re-apply the formatting, giving the appearance of a constant format.

On the other hand, my personal recommendation is to pull the information you need out of the PivotTable (maybe the GETPIVOTDATA function?) into your dashboard/siplay area, and then apply your formatting there. I know this doesn't always work depending on what your data looks like, but thought I'd mention it.

 

[OleMiss2010]

GETPIVOTDATA is what I was thinking would end up being the solution, but was hoping to be able to achieve this within the pivot.

Thanks Luke M.

 

[NARAYANK991]

Hi ,

See if this is useful.

http://chandoo.org/forum/threads/vba-conditional-formatting-for-pivot-tables.12098/

Narayan

 

[WMF]

Hi OleMiss2010,

Have you tried to set the appropriate "Apply Rule to"?

For Example,
Go to the a cell with a value for a Field in the Pivot Table
Go to Conditional Formatting -> New Rule
On the top of the "New Formatting Rule" dialog box, you should see "Apply Rule to:"
Select the last option: "All cells showing "XXX" Value for "Field selected"

Then set your formula for the condition, e.g. $E$1=1; and the format you wish

As you have three value columns, you have to do it three time (one by one for each value column).

However, you cannot set conditional format for row label in this way...

Hope it helps.