• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Conditional Formatting PivotTables

You are here: Home / Excel PivotTables / Conditional Formatting PivotTables
Conditional Formatting PivotTables
April 29, 2014 by Mynda Treacy

Conditional Formatting in PivotTables has its ups and downs. Unfortunately I find them mostly ‘downs’ but let’s not dwell on the negative as the few ‘ups’ might still make it worthwhile depending on your needs.

The Upside of Conditional Formatting PivotTables

When you apply Conditional Formatting to the Values area of your PivotTable the formatting will automatically expand/contract as you add new data or make changes to the filters, rows or columns.

In other words, the Values area becomes a dynamic range for the Conditional Format without you having to do any extra work. Nice....and just as it should be.

Here’s a little diagram so you know what I mean by ‘the values area’ etc.:

PivotTable Values Area diagram

In the above PivotTable I have applied Conditional Formatting to highlight the top month for each column (except the Grand Total… in some instances that’s a downside since you might want the top month overall highlighted too).

Here’s a step by step how to:

1. Select any cell in the values area of your PivotTable

2. On the Home tab of the Ribbon select Conditional Formatting > Top/Bottom Rules > Top 10 Items:

Conditional Formatting PivotTables

3. Set the value to 1 and choose your format:

set format for Top 1

4. You will now have an icon beside the cell that you have applied the formatting to. Click on it and select ‘All cells showing “Sum of Sales “ values for “Month” and “Category”:

CF Icon

5. Then click on the Conditional Formatting button on the Home tab of the Ribbon and select Manage Rules. This will open the Rules Manager dialog box:

manage conditional formats

Notes on the image above:

a) Notice how the rule has a PivotTable icon at the far left. This is an indicator to let you know that this rule is applied to a PivotTable. If you’re familiar with Conditional Formatting you’ll know that rules applied to regular cells don’t have this icon.

b) The other thing to note is the ‘Applies to’ is a list of the PivotTable fields as opposed to a range of cells. When you see this you know that the range will dynamically update with any changes you make to the PivotTable.

6. Ok, moving on, click on the rule and then click the ‘Edit Rule’ button (labelled 'c' in the image above) which opens the dialog box below:

edit conditional formats

Tip: here you can also change the ‘Apply Rule to…’ we did back in step 4.

7. Click on the down arrow beside ‘all values’ and choose ‘each Row group’ > click OK > click OK again.

This will result in the top sales for each column (except the Grand Total) of your PivotTable being highlighted like so:

highlight top 1 result

Now when you change the filters, add new data, or move the column/row fields around, the Conditional Formatting follows.

For example, changing the PivotTable Date Grouping to include Quarters results in the Top 1 result for each column per quarter being highlighted, without me changing the Conditional Formatting rules at all:

highlight top 1 result per quarter

Why stop there? Actually you should stop but this is an example of what you can do, not what you should do so I’m going to make it a bit gaudier and apply formatting to the top 3 quarters next:

highlight top 3 results for each quarter

Now I have 2 rules in my CF manager; one for the top 3 quarters and one for the top 1 result for each column every quarter:

CF manager multiple rules

Want More?

Ok, I’ll give you more, but be warned the overuse of formatting might burn your audience’s retinas so proceed with caution.

You can actually use any of the built in Conditional Formatting rules covered here, except these ones you can see greyed out in the menu below:

excluded rules

The Down Side of Conditional Formatting PivotTables

What say I want to highlight the ‘Sum of Sales’ amount for all instances of Qtr2 in the Row Labels, as I have done below:

downside example

My rule for the above formatting looks like this =$A3=”Qtr2” as you can see below in the CF Rules Manager:

CF logic rules

a) Notice the absence of the PivotTable icon in the far left. This is the first indication that Excel doesn’t recognise this as being applied to a PivotTable and,

b) The ‘Applies to’ range doesn’t have the PivotTable fields listed. Instead it is an absolute cell range. So as you change the PivotTable layout or add new data, this range is likely to become fragmented and you may get inconsistent results.

The Bottom Line

If you want to use any logic rules that rely on the Row or Column labels be prepared to update the ‘Applies to’ range each time you refresh the PivotTable or make any changes that alter the size.

This is a pretty big downside since some of the most useful Conditional Formats are applied based on the TRUE/FALSE outcome of formulas. Sure the built in rules are ok, but I mostly tend to use formulas to define my formats.

Side note: You might be thinking that you can just set up a dynamic named range to use in your ‘Applies to’ criteria for the Conditional Format however, as soon as you enter a dynamic named range in the ‘Applies to’ field and press ok, Excel converts the range to cell references rendering it non-dynamic (is that a word?). Ugh! Frustrating, yes, and a pretty big downside.

The only way I know of to get around this is with VBA, but it’s complicated and I’ve run out of time to cover it here. I’ll try to persuade Phil to address this in one of his future VBA posts.

Conditional Formatting PivotTables

More Conditional Formatting Posts

conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
Highlight Selected Cell In Excel and Preserve Cell Format

Highlight Selected Cells in Excel and Preserve Cell Formatting

Use shapes to highlight the selected, active cells in Excel & preserve cell formatting. So you won't lose any conditional formatting, borders, colors etc.
excel conditional formatting gantt charts

Excel Conditional Formatting Gantt Chart

You can build a Conditional Formatting Excel Gantt Chart easily with just a few WORKDAY.INTL formulas and relative references.
Excel Conditional Formatting with Formulas

Excel Conditional Formatting with Formulas

Excel conditional formatting with formulas can be super confusing when you don't follow these three simple rules that ensure they work everytime.
Excel Factor 10 Conditional Formatting Painting by Numbers

Excel Factor 10 Conditional Formatting Painting by Numbers

Excel Factor 8 Highlight Cells Containing Formulas

Excel Factor 8 Highlight Cells Containing Formulas

Automatically highlight cells containing formulas in Excel to reduce the chance of them being deleted or over-written
How to Use Excel Conditional Formatting

How to Use Excel Conditional Formatting

Excel Conditional Formatting examples and workbook.

More Excel PivotTable Posts

show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Display Missing Dates in Excel PivotTables

Display Missing Dates in Excel PivotTables

Filter Excel Pivot Tables by Values

Filter Excel Pivot Tables by Values

Excel PivotTable Month Comparison [Video]

Excel PivotTable Month Comparison [Video]

Excel PivotTable Month Comparison is easy with the Show Values As tool.
Excel PivotTable Add Percentage of Total Column [Video]

Excel PivotTable Add Percentage of Total Column [Video]

Excel PivotTable Add Percentage of Total Column is easy via the Show Values As settings.
Excel Factor 6 Auto Refresh PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor Entry 1 - Reverse PivotTable

Excel Factor Entry 1 – Reverse PivotTable

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.




Category: Excel PivotTablesTag: conditional formatting, Excel PivotTable
Previous Post:Excel Chart TemplatesExcel Chart Templates
Next Post:Is Your Line Chart Lying?Is Your Line Chart Lying?

Reader Interactions

Comments

  1. Martin Nolan

    May 1, 2014 at 10:03 am

    Although you cannot use a dynamic range in the “Applies to” field, you can use one in the “Rule” formula. This allows one to use a small bit of VBA in the form of a UDF to check for a range intercept to turn on the conditional formatting. The same trick can be used with pivot table fields if one doesn’t mind using additional VBA to set the dynamic ranges. The down side is that the conditional formatting area has to be set as large as the largest expected area of the dynamic ranges. I’ll forward an example separately. The alternative, of course, is to use VBA to both set the dynamic ranges and to apply the conditional formatting to the ranges after each update.

    Reply
    • Mynda Treacy

      May 1, 2014 at 12:41 pm

      Thanks for sharing, Martin. I’ve attached your file here if anyone wants to see what you’ve done.

      Cheers,

      Mynda.

      Reply
  2. Bryan

    April 30, 2014 at 8:46 pm

    It’s a shame that PivotTables are so easy to work with and yet… so hard to work with! I love the quick work they make of data summaries, but then trying to visualize that data becomes a pain at best.

    Reply
    • Mynda Treacy

      April 30, 2014 at 8:52 pm

      I know! It feels like when they give with one hand they take with the other 🙂

      That’s when GETPIVOTDATA is your friend cause you can take that ugly PivotTable and make it presentable.

      Mynda.

      Reply
  3. Jeff

    April 30, 2014 at 3:13 pm

    That’s a nice simple tip to quickly create that kind of formatting. Thanks for sharing.

    Reply
    • Mynda Treacy

      April 30, 2014 at 3:16 pm

      Cheers, Jeff 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.