Locked lesson.
About this lesson
Understanding the tips and tricks to get conditional formats working properly on Pivot Tables
Exercise files
Download this lesson’s related exercise files.
Applying conditional formats to Pivot Tables.xlsx49.8 KB Applying conditional formats to Pivot Tables - Completed.xlsx
50.9 KB
Quick reference
Applying Conditional Formats to Pivot Tables
Configuring conditional formats to work with Pivot Tables.
When to use
Use to add conditional formats to a Pivot Table.
Instructions
Creating a Conditional Format
- Select a cell within a value column that you wish to use for your conditional format
- Go to Home --> Conditional Formatting and add the conditional formatting rule you need
Applying the cell to the rest of the PivotTable
- Click the formatting widget and choose to apply to:
- The selected cell(s) only
- All cells showing [measure] values (All rows including totals)
- All cells showing [measure] values for x (Excludes totals)
- If you cannot locate the widget, go to Conditional Formatting --> Manage rules and edit the rule. The options listed above will be at the top.
Hints & tips
- Create separate rules for detail and subtotals, applying them each to the final option
- When you collapse a subtotal section, the rule will be hidden with the detail rows
- Try to create rules for detail and subtotal lines that use differing colors, so it is obvious which rule is being examined
- 00:05 All right now that we have a couple of pivot tables let's look at doing some
- 00:09 things to it like applying conditional formating.
- 00:12 Now, here's what I'm gonna do.
- 00:14 I'm just gonna scroll down,
- 00:15 and we're gonna play around with the month and vendor pivot table here.
- 00:19 I'm just gonna expand March for a second.
- 00:21 And I'm gonna dismiss the field list cuz it's in my way.
- 00:24 All right, now the first thing I want to do is I want to apply a conditional format
- 00:28 to show the portion of sales here.
- 00:31 And I'm going to use a data bar for this.
- 00:33 So I'm selecting my first cell, which is I11, and
- 00:36 I'm going to go to conditional formatting and create a data bar and a data bar.
- 00:40 Here we go, blue data bar, that works.
- 00:43 Now you'll notice that when it applies it applies it to the single cell but
- 00:46 it pops up this funky little widget beside it.
- 00:49 And this little widget gives us the options of how we'd like to apply it.
- 00:52 Would we like to apply it to selected cells or all cells showing sum of sales or
- 00:57 all sales showing sales for month end.
- 01:00 Now I'm going to apply it to all showing the sale's values.
- 01:03 And what you'll see is that it actually applies the rule to
- 01:08 everything including the total.
- 01:10 And that means that all of my data bars look really,
- 01:12 really small especially when I get into the sub data.
- 01:16 Compared to the total, because that total is obviously waiting what's going on.
- 01:20 So that's not really good.
- 01:22 Let me go back and change this little widget, and say,
- 01:26 let's show all four month end.
- 01:28 And now you'll see that things change a little bit here.
- 01:31 Now we've got our totals that are being used, or our subtotals, rather,
- 01:36 that are being used for all these things.
- 01:38 But it's not picking up the total to skew the results.
- 01:42 Unfortunately it's also not picking up the detailed records.
- 01:45 And that's a bit of a challenge, too, because when we close this, it's all good.
- 01:49 But when we open it, we might want data bars showing up for
- 01:52 these guys in the middle as well.
- 01:54 So how do we get those?
- 01:56 Well we go and we apply another data bar.
- 01:58 So let's say conditional formatting.
- 02:00 We'll go to data bars, we'll choose a green one.
- 02:04 And once again, it pops up with this funky little widget.
- 02:07 So again, I'm going to look at it and say, do I want all for sales values?
- 02:11 Well, that replaces everybody, so that's not it.
- 02:14 So I go back and choose Sales Values for Vendor and there we go.
- 02:19 Now the cool thing about this is that when I expand somebody else
- 02:24 the data bars are already on there.
- 02:26 When I close them they go away.
- 02:28 What's also interesting is if you watch these bars, the 26 69 and the 26 92.
- 02:33 You'll notice that because there are bigger values showing up over here these
- 02:37 data bars dynamically react these are no longer the biggest items of the data set.
- 02:41 They're only the biggest items or
- 02:42 were the biggest items based on what you actually were looking at at the time.
- 02:46 They are very dynamic that way which is kinda cool.
- 02:48 Let me just close that back and show you,
- 02:50 yep they are the biggest ones we have expanded.
- 02:52 Pretty neat.
- 02:53 Now, when we're actually applying these things
- 02:56 there's some other things that may happen.
- 02:58 We could go and say let's create a new conditional formatting rule here for
- 03:03 data bars.
- 03:04 And I'm going to apply this guy here to all and then I'm going to go click on this
- 03:08 one and I'm going to make another data part as well.
- 03:13 And we'll assume that we're going to get this one right as well.
- 03:16 But then what happens when we come back over here and I want to modify this rule,
- 03:20 notice that the widget I have is showing up on this side.
- 03:23 If I go and click on this, the widget does not show up at all, so what do we do?
- 03:27 How do we get back to that?
- 03:29 Well, the secret is this we click somewhere inside, any one of these guys
- 03:33 has conditioning formatting rules, go to Conditional Formatting, and Manage rules.
- 03:40 And what you'll notice is this starts to get pretty complicated because we've
- 03:43 actually got four rules that are the same or
- 03:47 at least two of them are the same on each side.
- 03:49 We got blue rows, we got green rows.
- 03:52 The challenge here is we need to learn how to read this for the pivot table.
- 03:56 Notice that this rule is being applied to Sales and Vendor.
- 03:58 This one is being applied to Sales and Month End.
- 04:00 So the Sales column, is it being applied to Vendor, that's the green row, or
- 04:04 just the Month End is the blue row?
- 04:06 Regardless, when we go and click on one of these,
- 04:09 and we say Edit, you'll notice that at the very top of this
- 04:13 box is all of the options that were under that little widget.
- 04:17 So it says, apply the rule to J14.
- 04:19 Do we want to do it just to profit's values?
- 04:22 Which means, everything, including the vendor and the month end?
- 04:25 Or do we wanna apply just to vendor.
- 04:27 So this is kinda a key piece.
- 04:29 Apart from that, all of the conditional rule formatting is exactly the same,
- 04:33 it's just the key of understanding how to apply it to the pivot table, and
- 04:36 what's going on.
- 04:37 For my money honestly most of the time I always choose the last option.
- 04:41 Because I'm trying to setup a conditional formatting rule for
- 04:44 a specific piece of data and I don't want the totals to be impacting it.
- 04:47 So generally I would say I like that to be the default.
- 04:50 Last item, it's the most context, is good to go.
- 04:53 From that point,
- 04:54 that's how we apply conditional formatting rules to a pivot table.
Lesson notes are only available for subscribers.