Forum Discussion

CharlineGomez's avatar
CharlineGomez
Copper Contributor
Sep 21, 2022

Conditional formatting within fields pivot table

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!

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 21, 2022

    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?

    • CharlineGomez's avatar
      CharlineGomez
      Copper Contributor
      Sep 21, 2022

      HansVogelaar 

      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

Resources