Custom PivotTable Styles

Quick reference

• 00:04 Now we're gonna take styles to a whole another level.
• 00:07 Because you know what?
• 00:08 As many choices as Microsoft gives us by default in the Styles Gallery,
• 00:12 the reality is there probably isn't one that's absolutely perfect for us.
• 00:17 So why don't we make our own?
• 00:21 So, here's the secret on exactly how to do that.
• 00:23 The first thing we do is we scan the gallery to find something that's as close
• 00:27 as possible to what we might wanna start with.
• 00:30 Now, if you're trying to use multiple colors there's probably nothing, but
• 00:33 the idea is to try and figure out well.
• 00:35 You know what, this one's got a nice header row and I like the banding on it so
• 00:38 I'm gonna start with this.
• 00:40 And what I'm gonna do is I'm gonna right click on it and
• 00:42 I'm going to say duplicate.
• 00:46 And at that point it comes back and it says all right let's give it a name.
• 00:49 Do you want to call it pivot style light twenty space two?
• 00:52 That's not really good for me so I'm going to call this one My style,
• 00:56 just to give it a slightly different name here.
• 00:59 And then we'll say okay.
• 01:02 Now, in the gallery, it throws it up top under custom.
• 01:07 If I want to modify it, what I need to do now, is right click and say modify.
• 01:14 And it gives me a whole list of every element that's actually listed and
• 01:18 sitting in this file.
• 01:19 So what I'm gonna do right now is I'm gonna scroll down and I'm gonna change
• 01:23 the subtotal color and see if it looks any different or any better.
• 01:28 So I'm gonna scroll down to take a look.
• 01:29 I've got report filters and first columns and first row stripes.
• 01:32 We'll get into all of that a little bit later.
• 01:34 Subtotal column one, Subtotal, row one.
• 01:38 So when it says row, it means going across.
• 01:40 Column is, of course, going down.
• 01:42 We're going to say format.
• 01:44 And I'm going to change the fill to a nice bright orange and
• 01:48 the font to a dark black, that will work.
• 01:53 And we'll say OK.
• 01:54 It doesn't look like anything here as changed, and
• 01:58 when I say OK it still doesn't look like anything here has changed.
• 02:03 So, what I need to do is I need to go up and actually Look at this.
• 02:07 Look at the band that's surrounding this icon.
• 02:10 This is the style that's actually applied even though I duplicated it and
• 02:14 made a new custom style.
• 02:15 So let's click on, oh look when I live preview this it looks better.
• 02:19 Gonna click on my custom style, and
• 02:21 you can now see that I have blue headers with orange subtotals on there.
• 02:24 That's pretty cool.
• 02:26 All right.
• 02:27 Next thing that I wanna do is go back and modify my style.
• 02:30 You know, it's unfortunate it doesn't show subtotals in this preview,
• 02:33 because that would make it a little easier to actually see what's going on here.
• 02:36 So let's modify this.
• 02:38 And maybe we'll go and change something else in here.
• 02:43 I don't know. What else do we have to play around with?
• 02:45 How about a row subheading?
• 02:47 We'll format that, and let's put this in the same orange And
• 02:52 we'll use the same black font.
• 02:55 And now it changes the little preview and when we say okay, you'll notice that now
• 02:59 that my header bands are coming in and it shows in my preview here as well.
• 03:04 I don't have to apply it again because I've already applied the style to
• 03:06 the pivot table.
• 03:07 So let's right click on this guy again and modify.
• 03:10 What other things can we change here?
• 03:13 We could go with a first row stripe and you'll notice
• 03:19 that I can change the stripe size, so let's change it to a stripe size of 3.
• 03:24 And we'll go to format and
• 03:26 I'm gonna throw a light gray background on this one just for fun.
• 03:31 And OK.
• 03:32 And OK.
• 03:34 And nothing happens in my pivot table.
• 03:35 And this something you have to be aware of,
• 03:37 if you're gonna use the stripe options you have to also turn on the banding section.
• 03:44 And when I do that,
• 03:44 I now get three gray rows starting from the first row of the pivot table.
• 03:49 So this one's gray, even though it's got orange on top of it.
• 03:52 There's three gray rows.
• 03:53 It's got a white row, three gray rows, a white row, three gray rows, a white row,
• 03:57 and this is what's happening it's the way it's staggering out.
• 03:59 Well, that's not ideal I think I'd rather have three white rows in here as well.
• 04:03 So go right-click, Modify, and we'll go to the Second Row Stripe.
• 04:10 And we'll change that to a 3 as well.
• 04:14 And OK. There we go.
• 04:16 We could also, of course, do the same thing by modifying and
• 04:20 maybe looking for a first column stripe.
• 04:24 Do we see that down here somewhere?
• 04:25 It must be up near the top.
• 04:26 I must have missed it.
• 04:27 There we go, first column stripe.
• 04:29 We'll leave this one as one and
• 04:31 we'll just put a light yellow background on it or something.
• 04:33 How about that one there.
• 04:35 And OK.
• 04:36 Okay.
• 04:36 It didn't show up because you need to apply banded columns.
• 04:40 There we go.
• 04:42 So we can control all kinds of elements in a pivot table style.
• 04:45 The secret is, duplicating the one that you find that's closest to what you want
• 04:50 and then applying that style to your pivot table, because doesn't do that
• 04:53 automatically, and then modifying all of the individual pieces.
• 04:57 And if you don't like it at the end, you can just right click and delete it, or,
• 05:01 if you really like it, you can set it as default so
• 05:05 that every pivot table that you create from now on uses that exact same style.

Lesson notes are only available for subscribers.