GoSkills
Help Sign up Share
Back to course

Conditional Formatting on PivotTables

Compact player layout Large player layout

Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Basics of applying conditional formats to a PivotTable.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Conditional Formatting on PivotTables.xlsx
199.2 KB
Conditional Formatting on PivotTables - Completed.xlsx
199.8 KB

Quick reference

Conditional Formatting on PivotTables

Basics of applying conditional formats to a PivotTable.

Where/when to use the technique

When you’d like to add some context-sensitive formatting to a PivotTable.

Instructions

Creating a conditional format

  • Click on the column you wish to format
  • Go to Home > Conditional Formatting
  • Pick the appropriate rule
  • Click the little icon to the top right of the cell to expand the format options
  • Make a choice of how you’d like to apply it:
    • Selected cells
    • All cells in the table (includes totals and subtotals)
    • All cells showing “x” values for “y” and “z” (values excluding totals and subtotals)

Modifying a conditional format

  • Select a cell that holds the format to be modified
  • Go to Home > Conditional Formatting > Manage Rules
  • Select the rule to modify and click Edit

Clearing a conditional format

  • Select a cell that holds the format to be removed
  • Go to Home > Conditional Formatting > Manage Rules
  • Select the rule to modify and click Delete
Login to download
  • 00:04 In this video,
  • 00:05 we're going to see if we can add a little bit of intelligence to our pivot tables.
  • 00:09 The challenge that we've got right now is that when we go and
  • 00:11 we take a look at this pivot table, I'm just going to zoom out a little bit so
  • 00:15 we can see a little bit more information.
  • 00:17 There's a lot of numbers here.
  • 00:19 But the challenge is that doesn't really provide us a lot of insight,
  • 00:21 I'd like to see which my top values are.
  • 00:24 But I don't want to filter the pivot table.
  • 00:26 What I really want to do is highlight them,
  • 00:27 so I'm going to use conditional formatting to actually make that happen.
  • 00:31 Now, here's the thing.
  • 00:32 I'm going to go and click on a single cell, doesn't matter which one really.
  • 00:35 But I'm going to start in the top left-hand corner because that's where I
  • 00:38 always start my conditional formats from, just makes it easier to read them and
  • 00:41 maintain them later.
  • 00:42 I'm going to go to Conditional Formatting > Top/Bottom Rules,
  • 00:45 we're going to take the Top 10 Items.
  • 00:48 Now I'm not sure why Microsoft felt that the top ten should be highlighted in red.
  • 00:51 Generally, we kind of look at this and say well, good things are green, so for sales,
  • 00:55 we're going to go with Green Fill with Dark Green Text.
  • 00:58 And when I say OK, you'll notice that it actually applies it not to the top
  • 01:03 ten items in the pivot table, but it actually applies to a single cell, and
  • 01:07 this is a little bit weird.
  • 01:08 It also pops up this funky little widget beside it.
  • 01:12 And if I go and click on this, it gives me some formatting options.
  • 01:15 And it says would you like to apply it to the selected cell, and
  • 01:17 that's what happens by default.
  • 01:18 So when you're working with a range,
  • 01:21 you select the entire range that you want to deal with.
  • 01:23 On a pivot table, we start with a single cell, but
  • 01:27 we've gotta toggle with this little piece here.
  • 01:29 So we don't want it for the single cell.
  • 01:31 Let's try this All showing Sales $ values.
  • 01:34 And when we click on this, it now highlights all of the values in our totals
  • 01:38 and subtotals and you think well, okay, that's weird.
  • 01:41 I kind of knew that my totals and subtotals were going to be the biggest.
  • 01:43 That's not really what I want.
  • 01:45 So let's go back and try and change this again.
  • 01:47 We'll change it this time to All cells showing Sales $ values for
  • 01:51 Category and Week.
  • 01:52 And what is this actually talking about?
  • 01:55 It says, I want you to look at this cell and
  • 01:57 see whether it's been filtered by category and by week.
  • 02:00 The grand totals and the subtotals,
  • 02:01 they don't actually fall into that classification.
  • 02:04 It has to have a specific single line item for either my row or for my column.
  • 02:08 And at that point, it actually now applies nicely to show me my top ten items,
  • 02:12 which is great.
  • 02:14 But what if I do want to filter and
  • 02:16 I want to find out what my top three subtotals are?
  • 02:19 Well, let's try again.
  • 02:21 We'll set up a new rule, Conditional Formatting > Top/Bottom Rules > Top 10.
  • 02:26 And we're going to go and this time, it doesn't make sense to actually figure out
  • 02:29 all ten because there's only ten categories here, so let's go with three.
  • 02:33 And we're going to go with a Yellow Fill, just to make it different and
  • 02:37 we'll say OK.
  • 02:38 And once again, it applies it to a single cell.
  • 02:41 So we'll go back and we'll say, all right, let's go with All again.
  • 02:45 Wait, no, that's not the right one.
  • 02:47 So let's go to All showing the Class and Week.
  • 02:50 Generally, what I find when you're trying to apply a conditional format to a pivot
  • 02:54 table, you want to apply it to the last item on this list.
  • 02:57 Unless you've got averages, then going across the entire thing makes sense.
  • 03:00 But if it's specific values, it usually wants to be the last one.
  • 03:04 At that point, you can that these three are the biggest sellers that we actually
  • 03:08 have, so that's good to know.
  • 03:11 Now, this is great because the pivot table formats will stick as we collapse
  • 03:15 different parts of our pivot table.
  • 03:17 The green obviously goes away because it's not needed at this point.
  • 03:20 And when I expand things, it brings them back, so
  • 03:22 everything's still good there, it all sticks and collapses nicely.
  • 03:25 But the problem is, what if I now decide that I want to reconfigure this
  • 03:29 conditional formatting rule?
  • 03:31 Even if I go back and click the cell,
  • 03:33 I've lost the widget that allows me to change it.
  • 03:35 No, what do I do?
  • 03:37 Well, the answer at this point is we actually go to Conditional Formatting and
  • 03:42 we go to Manage Rules.
  • 03:43 If you ever need to click clear rules, by the way, you can do it here, you can clear
  • 03:47 them from the entire pivot table, or the entire sheet, or wipe them all out.
  • 03:50 But we just want to change one.
  • 03:51 So we're going to go to Manage Rules, which will pop up this dialog.
  • 03:55 When I select my top three, all values, I can now say Edit.
  • 03:59 And what you'll see is that here's all the options from the little widget, so
  • 04:03 they're all there, you don't lose them.
  • 04:04 If you prefer to work in a full-size dialog, you can just come straight into
  • 04:08 Manage Rules after you set it up and configure it here.
  • 04:10 The nice thing is we can also make modifications to our conditional format.
  • 04:14 So maybe I say, hey, I really want to go with actually a nice dark green fill.
  • 04:18 And I want to have a white text on it that's in bold for these guys.
  • 04:23 I can totally set that up to make a custom conditional format
  • 04:26 that looks absolutely stellar like that and there we go.
  • 04:29 So that's the basics of how we actually use conditional formatting on
  • 04:33 a pivot table.
  • 04:34 Conditional formatting is an entire topic all on its own.
  • 04:36 It's something we do teach in GoSkills Dashboarding course, but
  • 04:39 to apply it to a pivot table, you need to know those quick little tricks.

Lesson notes are only available for subscribers.

Custom PivotTable Styles
5m:16s
Basic Sorting
3m:24s
Share this lesson and earn rewards Facebook Twitter LinkedIn WhatsApp Email
Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2021 GoSkills Ltd. Skills for career advancement