Conditional formatting within fields pivot table

Occasional Contributor

Good morning everyone, 

 

I am currently stuck on something that I am not even sure is doable...

 

I would like to get to highlight values within the pivot table depending on fields, my table is as followed:

 

Raw data is a split per customer of the purchases per region so 14 different lines per customers. But we want to check specific regions so there's Exports America and Rest of the world focus. Either those lines are filled with a number that a formula consider as "Updated" or with a dash considered as "Empty". What I want to do is highlight in the pivot table per customer if "Empty" for Exports America equals 12 and if "Empty" Rest of the world equals 2. I tried some conditional formatting but it applies to the whole table and if Exports America = 2 then it's highlighted when it's in fact normal. 

 

Is there anyway I can make two specific conditions ? 

 

Thanks in advance for your help!

 

9 Replies

@CharlineGomez 

Conditional formatting in a pivot table is tricky since the range of the pivot table can change when you filter or update the pivot table.

 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 

I'm very sorry, it says I can load files or link... I don't know what's happening. The best I can do for know is this ! 

 

This is what the TCD looks like and this is below the raw data! Hope it can helps further

 

Row Labels Count of forecast
Customer 1 
Exports Americas11
Rest of the world

2

Customer 2

 

Exports Americas

11

Rest of the world

2

 

 

 

 

 

 

 

Account NameYearScopeTrade OrionW39W40W41W42W43W44W45W46W47W48W49W50W51W52forecast check
Customer 1Current YearRest of the worldWORLDWIDE--------------EMPTY
Customer 1Current YearExports AmericasUS-----------

-

 

 

 

 

--EMPTY
Customer 1Current YearExports AmericasCANADA--------------EMPTY
Customer 1Current YearExports AmericasMEXICO--------------EMPTY
Customer 1Current YearExports AmericasBRAZIL--------------EMPTY
Customer 1Current YearExports AmericasARGENTINA--------------EMPTY
Customer 1Current YearExports AmericasCHILE250190190200200225225230235235235235235230UPDATED
Customer 1Last YearRest of the worldWORLDWIDE--------------EMPTY
Customer 1Last YearExports AmericasUS--------------EMPTY
Customer 1Last YearExports AmericasCANADA--------------EMPTY
Customer 1Last YearExports AmericasMEXICO--------------EMPTY
Customer 1Last YearExports AmericasBRAZIL--------------EMPTY
Customer 1Last YearExports AmericasARGENTINA--------------EMPTY
Customer 1Last YearExports AmericasCHILE--------------EMPTY
Customer 2Current YearRest of the worldWORLDWIDE--------------EMPTY
Customer 2Current YearExports AmericasUS--------------EMPTY
Customer 2Current YearExports AmericasCANADA--------------EMPTY
Customer 2Current YearExports AmericasMEXICO--------------EMPTY
Customer 2Current YearExports AmericasBRAZIL--------------EMPTY
Customer 2Current YearExports AmericasARGENTINA--------------EMPTY
Customer 2Current YearExports AmericasCHILE36003600360034003400340034003400340034003400340034003400UPDATED
Customer 2Last YearRest of the worldWORLDWIDE--------------EMPTY
Customer 2Last YearExports AmericasUS--------------EMPTY
Customer 2Last YearExports AmericasCANADA--------------EMPTY
Customer 2Last YearExports AmericasMEXICO--------------EMPTY
Customer 2Last YearExports AmericasBRAZIL--------------EMPTY
Customer 2Last YearExports AmericasARGENTINA--------------EMPTY
Customer 2Last YearExports AmericasCHILE--------------EMPTY

@CharlineGomez 

Perhaps like this?

Hi Hans! Thanks a lot for your help although it's a bit more complicated than that as I'd like both to be formatting separately, like each values as to be highlighted separately. I don't know if that is possible !

Thanks again

@CharlineGomez 

This way?

Hi Hans, I am sorry but I see no difference in this file compared to the previous one ?

@CharlineGomez 

I highlighted the value of EMPTY instead of the customer.

If this is not what you want, please explain in detail what you do want.

Hi Hans, thanks a lot! Now I see the difference!

In fact, I'd like both values to be highlighted "Export Americas" and "Rest of the world" as their values are not connected to one another. What I want to see through this table (as we have here more than 150 customers) is which one as 14 empty for Export Americas on which one as 2 empty for rest of the world (cause those values means we didn't get any profit from them!). I don't know if it's clearer ?:)

@CharlineGomez 

You originally asked to highlight 12 EMPTY for Exports America; that's what the rule currently does (but since the number is 11, Exports America isn't highlighted). You can easily change the rule to highlight if the number is 14 instead of 12. The formula is now

 

=OR(AND($A4="Exports Americas",$B4=12),AND($A4="Rest of the world",$B4=2))

 

If you want 14, change it to

 

=OR(AND($A4="Exports Americas",$B4=14),AND($A4="Rest of the world",$B4=2))