Conditional Formatting on PivotTables

Quick reference

• 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.