Locked lesson.
About this lesson
Creating your own style to use with 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.
Custom Pivot Table Styles.xlsx199.6 KB Custom Pivot Table Styles - Completed.xlsx
199.9 KB
Quick reference
Custom Pivot Table Styles
Creating your own style to use with a PivotTable.
Where/when to use the technique
When you’re not happy with the default styles, and want to customize them for your implementation.
Instructions
Choosing a style
- Select a cell in the PivotTable
- Go to the PivotTable Tools > Design tab
- Mouse over the various PivotTable styles until you find the one closest to what you want
- Right click and Duplicate it
- Apply it to your Pivot
Modifying the style
Elements
- All elements are accessible, the trick is experimenting to find out which does what
- Anything marked Row will go across
- Anything marked Column will go down
- Selecting any element will let you modify or clear the formatting
Row banding
- Select the First Row Stripe and Second Row Stripe elements
- Number of rows shaded is controlled by the “Stripe Size”
- Further shading and customization is done by clicking the Format option
- Turn on the Banded Rows option on the PivotTable (PivotTable Tools > Design)
Column banding
- Select the First Column Stripe and Second Column Stripe elements
- Number of columns shaded is controlled by the “Stripe Size”
- Further shading and customization is done by clicking the Format option
- Turn on the Banded Columns option on the PivotTable (PivotTable Tools > Design)
Other options
- Accessed by right-clicking the style in the style gallery on the PivotTable Tools > Design tab
- Set as default for this document
- Delete
- Modify
- 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.