GoSkills
Help Sign up Share
Back to course

Custom PivotTable Styles

Compact player layout Large player layout

Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

  • 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 PivotTable Styles.xlsx
191.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
Login to download
  • 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.

PivotTable Styles
3m:14s
Conditional Formatting on PivotTables
4m: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

© 2021 GoSkills Ltd. Skills for career advancement