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 PivotTable Styles.xlsx191.8 KB Custom PivotTable Styles - Completed.xlsx
192.2 KB
Quick reference
Topic
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 Pivot
- 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 is controlled 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 rows 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 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.