Applying Conditional Formats to Pivot Tables

Quick reference

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