Locked lesson.
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.xlsx191.8 KB Conditional Formatting on PivotTables - Completed.xlsx
192.2 KB
Quick reference
Topic
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
- 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.