Graduation Sale - 20% off! Get the skills they don’t teach in college - Graduation Sale - 20% off

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 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
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

© 2022 GoSkills Ltd. Skills for career advancement