Custom PivotTable Styles

Quick reference

• 00:04 While pivot table styles are very cool, there is a chance that you're
• 00:07 going to go and open up this gallery and you're going to look through all
• 00:10 the configurations, and you're not going to find the one that you want.
• 00:13 So what do you do at that point?
• 00:14 How do you customize it, make it really look right?
• 00:17 You can go through obviously and highlight different rows and
• 00:20 things like that the way you want, but then when you refresh your pivot table
• 00:22 it's going to throw away that formatting or it won't be applied correctly.
• 00:25 So what we're going to focus on now is building a custom pivot table style.
• 00:29 The first thing I'm going to do is try and get as close as I can to what I want.
• 00:32 So I'm going to turn on banded rows,
• 00:34 because ideally I'd like to actually ban these blocks of three, so
• 00:38 three gray ones, three white ones, although I might change the color as well.
• 00:42 Once I've got that set up, I'm going to take the style that I've selected.
• 00:46 So this light blue pivot table style light 20 here,
• 00:49 I'm going to right-click on it and I'm going to choose Duplicate.
• 00:52 And at that point it comes back and gives me the ability to set up my own style, so
• 00:56 I'm going to go and call this MyStyle, so that I have a nice obvious name for it.
• 01:00 And at this point,
• 01:01 I can now go through and start setting some of the properties that are here.
• 01:04 Now what you'll notice is that some of these are bold.
• 01:06 These are the properties that are actually being used on this pivot table at
• 01:10 the moment, and it's based on what you've actually selected through
• 01:14 the Pivot Table Styles options.
• 01:15 So I might look at something like the Header Row here, for example, and
• 01:18 I might say, I want to change this.
• 01:20 So I'm going to click on the Format and I'm going to set it to use a black fill,
• 01:24 and I think I'm going to use a white font on top of the black fill,
• 01:28 and I'm going to make it bold.
• 01:30 And when I say OK, and say OK, you'll notice that nothing happens, what?
• 01:36 And this is one of the tricks around a custom pivot table style is that just
• 01:40 because you've duplicated it and you've made changes to it,
• 01:43 doesn't mean it's been applied to the pivot table.
• 01:45 As a matter of fact, if I open up the Styles gallery,
• 01:48 you'll see that it's sitting here in Custom, but if you notice the subtle
• 01:51 gray outline around this guy this is still the style that's selected.
• 01:54 So we're going to go back and we're going to choose my Custom style and
• 01:57 now it actually applies it.
• 01:59 It's great.
• 02:00 Now, I'm going to go through and I'm going to play and
• 02:02 start modifying some things and see what we can do.
• 02:05 So I'm going to right-click on this and we'll choose Modify.
• 02:09 Now inside here what else could I change with this one?
• 02:11 How about first row stripe?
• 02:13 So first row stripe is the first stripe on the pivot table,
• 02:16 so we can see alcohol here is in light gray.
• 02:19 Notice that the stripe size is set to 1.
• 02:22 Well, I'm going to change that to spread it to 3 rows, and
• 02:25 you can see it gives us sort of an update in the little preview here.
• 02:28 I'm also going to go to the second row stripe and
• 02:32 I'm going to to change that to be 3.
• 02:34 Now this is showing me a preview of banded columns which I don't have on.
• 02:38 But if I go and say OK at this point,
• 02:40 you'll notice now that it gives me a band of three gray, three white,
• 02:44 three gray, and so on all the way down at the pivot table.
• 02:48 The one thing that's unfortunate about this is that we've got a showing blank
• 02:52 rows showing up on this area.
• 02:53 That actually inherits one of these, I wish we could set this for
• 02:56 just the data area of the pivot, but unfortunately we can't.
• 02:59 Let's make some more modifications.
• 03:01 We'll go right-click, we'll go to Modify, and let's take a look at say,
• 03:05 what else would we want?
• 03:07 We've got subtotal columns, subtotal row 1.
• 03:10 Sure, this one looks good.
• 03:11 So we'll go format.
• 03:13 Let's go with a dark blue fill with a white font.
• 03:17 Now that should set up the subtotal for my alcohol total.
• 03:22 I also want to get the heading on this one as well.
• 03:24 So let's scroll down and see what else we have.
• 03:26 Aha, row subheading.
• 03:28 So we'll go to Format > Fill, dark blue.
• 03:32 I'm going to set this one again to white.
• 03:35 There we go.
• 03:37 And while I'm here, I see there's a grand total row option so
• 03:39 let's hit that one, too.
• 03:41 This one I'm going to make similar to my headers, I'm going to go with black.
• 03:44 We're going to go other font, we'll make it bold and we'll make it white.
• 03:48 And at this point when I say OK, and I say OK, you'll notice that it's changed
• 03:53 the entire style of my pivot table to look just the way that I set it up.
• 03:57 Now I might in this case turn around and say, blank rows,
• 04:00 let's get rid of that blank line after each item now.
• 04:03 There we go, it looks a little better, I don't have that strange grey row in there,
• 04:08 and maybe I could even do something else with this where I could go back and
• 04:11 say, right-click, Modify on this.
• 04:13 Let's go and take a look at our Subtotal row 1, Format, and
• 04:18 see can we put a border on this one?
• 04:20 And it looks like we can.
• 04:21 So let's go with a black border, and we'll put it just on the,
• 04:26 now let's put on the bottom and the top and the left and the right, why not?
• 04:31 We'll do that, and when we say OK, and OK,
• 04:34 at this point it draws a nice little black box around our subtotals in place as well.
• 04:39 So overall that doesn't look too bad.
• 04:42 The key thing I want you to recognize if I turn off Banded Rows,
• 04:45 it's going to turn off that part.
• 04:46 If I turn on Banded Columns, it'll inherit this because I didn't reconfigure it.
• 04:50 It's going to be a single stack, but I could obviously go back and
• 04:53 change the stripe size on these things, too.
• 04:55 So I'm going to set it back the way I want, there we go.
• 04:57 I've built my own beautiful custom pivot table style to look exactly the way
• 05:01 I want.

Lesson notes are only available for subscribers.