Conditional Formatting on PivotTables

Quick reference

• 00:04 In this video we're going to look at conditional formatting and
• 00:07 how it can add a little bit of business intelligence to our pivot tables.
• 00:12 All conditional formatting is accessed through the Home tab
• 00:16 by going to Conditional Formatting.
• 00:18 And to apply conditional format to a pivot table is very similar to the way we would
• 00:22 do it with a range except that there's a couple of other
• 00:25 idiosyncracies that we have to be aware of.
• 00:27 So the first thing we're gonna do is we're gonna select somewhere inside our
• 00:31 pivot table.
• 00:32 And we'll go to Conditional Formatting, and
• 00:34 I'm going to go to Top Bottom Rules and I'm gonna select the Top 10 Items.
• 00:40 And for some strange reason, Microsoft decided the default for
• 00:44 top ten should be light red.
• 00:46 Personally, I think, it should be green cuz that means good.
• 00:49 So we're gonna say that the Top 10 Items, we're gonna fill green with green and
• 00:53 we're gonna say, OK.
• 00:55 You'll notice that it has not given me the top ten,
• 00:58 it hasn't even given me the top one, it's just highlighted a single cell.
• 01:02 But it's thrown up this funky little window beside it.
• 01:04 And if I go and click on that, it actually asks me would I like to see it for
• 01:10 all cells showing sales dollar values?
• 01:13 Well sure.
• 01:15 And you'll notice now that it's highlighted the top ten values, but
• 01:18 they're all in my totals, and that is hardly what I'm looking for.
• 01:22 I'm looking for the information inside the values areas for my top ten.
• 01:25 That's gonna be much more useful to me.
• 01:27 I know I know the totals are gonna be big.
• 01:29 So I'm gonna go back to this little window again,
• 01:31 and I'm gonna change this to the very bottom item.
• 01:33 And this is what I usually use when I'm setting up my pivot table items.
• 01:37 What it asks is, all cells showing the Sales $ values
• 01:42 where both a Category and a Week have been applied.
• 01:45 So here's a category, here's the weeks.
• 01:47 So it's everything in the middle areas, not the subtotals.
• 01:51 And that reaches through all of the different areas, both with alcohol and
• 01:55 with food, and it highlights the top ten items for me, and that's kind of cool.
• 02:00 But what if I wanted to go and see something else,
• 02:04 like maybe I wanted to apply a different conditional format to show me which
• 02:07 the biggest numbers were going across the subtotals.
• 02:11 Well, let's go to Conditional Formatting > Top bottom Rules > Top 10 Items.
• 02:15 I'm going to go in this case with the top three, I don't think I need all top ten.
• 02:19 And I'll do them in a slightly different color just so that we can tell them apart.
• 02:23 Yellow fill with dark yellow text, and say OK.
• 02:26 And once again it's applied it to a single cell.
• 02:30 If I click on this and say all cells, it's gonna go through the entire table,
• 02:35 which will pick up the totals because they're the largest in the entire thing.
• 02:40 That's not ideal, or all cells showing Sales $ values for Class and Week.
• 02:46 So this is now class where the previous ones were the category.
• 02:50 So we'll say OK.
• 02:52 And you'll notice the top three values are down here.
• 02:55 Now this is great but, that's all as good as you having this little icon.
• 03:01 When you click somewhere else, it's still showing here.
• 03:03 But as soon as I go and put something else away into a cell,
• 03:08 you'll notice that no matter where I click, that icon's gone.
• 03:12 So how do I modify this pivot table?
• 03:14 Well, here's the other alternative.
• 03:16 We can go to Conditional Formatting and we select a cell somewhere in the pivot
• 03:19 table, go to Gonditional Formatting and go to Manage Rules.
• 03:24 When we get in there we have the option to show us the formatting rules for
• 03:28 this pivot table, those are the ones that we want.
• 03:31 And that gives us a list of all of the different rules that we have here.
• 03:34 So we can click on the rule, and say Edit Rule.
• 03:39 And once we're in there, does this look familiar?
• 03:41 It's applying this rule as its target to B12.
• 03:44 That's where we started,
• 03:45 that's why the marching ants are walking around this particular cell right now.
• 03:49 It also gives the same three options that were in that little dialogue box that came
• 03:53 up on the top right hand side.
• 03:54 So If you prefer to work with a full-size window,
• 03:57 you don't have to ever use that little dialog.
• 03:59 You can apply the conditional formatting rule and then go into manage rules and
• 04:02 come into the full-size window.
• 04:04 And within here, you can do all kinds of different interesting formats.
• 04:08 We can even change this out if we wanted a darker green with a white font,
• 04:13 for example, to make something that's a little bit different.
• 04:16 We can completely do this and say OK and
• 04:19 apply and get a slightly different style on our pivot table as well.
• 04:24 Conditional formats are a whole another topic,
• 04:26 its the key piece that we wanna be aware of here is how to apply them and
• 04:30 how to apply them to different areas on the pivot table.
• 04:33 And that whole discussion, all revolves around what happens in here.
• 04:39 Are we selecting all cells or are we only selecting the ones that are in
• 04:44 a subtotaled or in a values area by choosing the last option?

Lesson notes are only available for subscribers.