🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Custom PivotTable Styles

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
199.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
Login to download
  • 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.

PivotTable Styles
03m:14s
Conditional Formatting on PivotTables
04m:55s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement