Share [Social9_Share]

Conditional formatting in Pivot Tables

Conditional Formatting in Pivot Tables - 5

Today I am going to speak about a small problem that happens when you are trying to apply conditional formatting on Pivot Tables. It’s not complex rather its nifty!

 

Consider this Pivot Table..

Conditional Formatting in Pivot Tables - 1

We have Product Category, Product ID and its Sales. What if I ask you to make it a little fancy and apply conditional formatting (data bars) on it ?

 

Applying Conditional Formatting on Pivot Tables – Wrong Way!

Conditional Formatting in Pivot Tables - 2

  1. Now if you selected the sales column of the Pivot Table
  2. Went to Conditional Formatting
  3. And applied a Data Bars on it
  4. That fine but the process isn’t complete yet and you are missing out on an additional step

 

Applying Conditional Formatting on Pivot Tables – Right Way!

Conditional Formatting in Pivot Tables - 3

  1. Select the sales column of the Pivot Table
  2. Conditional Formatting
  3. Apply Data Bars on it
  4. Don’t forget to – Click the little icon at the bottom of the data selected and apply the formatting rule to the Pivot Fields

 

What difference does it make?

  1. If you don’t do the last step – Conditional Formatting is applied on the cells selected rather than on Pivot Table Fields
  2. In case your pivot table structure changes, your formatting will go for a toss. Because your formatting was applied to the cells and not to the Pivot Table Fields

 

Little Nuances!

  1. You might have noticed that there were 3 options available when you clicked on the drop down for applying conditional formatting to the pivot table. Here is what they mean!
    • Selected Cells – Will apply the formatting to selected cells only
    • All cells showing “Total Sales” values – Conditional Formatting will be applied to Total Sales irrespective of the pivot table structure. For eg – As of now we have Category, Product ID and Sales, even if you remove the Product ID from the pivot, the formatting will remain intact on the sales
    • All cells showing “Total Sales” values for “Product ID” – If you remove  product ID from the Pivot, the formatting on sales will disappear. Because the formatting is applied on “Sales for Product ID” and not the standalone “Sales” Field
  2. This option will appear for all the built in Conditional Formatting features (Highlight Cells, Top Bottom Rules, Colors, Data Bars, Icons etc..) when applied on the Pivot Table
  3. Just in case you lose the drop down, you can still change the formatting options from the Edit Rule Dialogue box

Conditional Formatting in Pivot Tables - 4

 

DOWNLOAD THE EXCEL FILE USED HERE

 

Which method do you use ?

Have you been using the conditional formatting correctly? Let me know in the comments

 

  • GraH

    During a training session this year, some-one told me she was colouring all individual cells of a pivot. I almost suffered from an instant heart attack. So a few weeks later, after a full recovery from this chock, I posted a similar tutorial @ work.

    • hahaha… There are moments when nerds do get an attack especially when people do arduous work for no reason
      Bro why don’t you write some of your nifty tricks on Goodly. I would love to have your post published!! 🙂

      • GraH

        Too much honour dude! It is very hard to find topics that aren’t yet described by some-one else. I’m more of a re-tweeter 🙂
        Yet though, I once inspired Chandoo to write a topic about bikers on a hill. So sometimes I do get creative. I’ll send you a suggestion by mail on combining 2 topics
        you write about on your blog: Excel and PowerPoint.

        • Sure.. Look forward to it!

          • GraH

            Just give me some (extra) time… Holidays are over and already rather busy.

Chandeep Chhabra

Popular Posts

© Goodly