Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Using pre-defined styles 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.
PivotTable Styles199.6 KB PivotTable Styles - Completed
199.3 KB
Quick reference
PivotTable Styles
Using pre-defined styles with a PivotTable.
Where/when to use the technique
Dressing your PivotTables up with a bit more color and style to make them show off nicely.
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 one you like
Style options
- Under the PivotTable Tools > Design tab, there is a PivotTable Style options group
- Check/uncheck the items to customize your style
- Options include highlighting
- Row headers
- Column headers
- Banded rows
- Banded columns
- HINT: Selecting these items in advance, changes the style appearance in the Styles gallery!
- 00:04 What we'll look at now is how to give our pivot tables a little bit different style
- 00:08 to what they have right now.
- 00:10 Every pivot table comes with a built-in default style.
- 00:13 What's a style?
- 00:14 It's essentially just a pre-packaged set of formatting that's applied to the actual
- 00:18 pivot table itself.
- 00:19 Now, the cool thing is that we're not stuck with this light blue, not a chance.
- 00:24 If we actually go up to the pivot tools Design tab,
- 00:26 you'll notice that there's actually a styles gallery here.
- 00:29 And if we click the little drop down arrow near the bottom, you can see there's
- 00:32 a whole bunch of different styles that you can actually play with for
- 00:35 your pivot tables.
- 00:36 There's light ones, there's dark ones, there's medium ones.
- 00:38 And the cool thing here is if we decide that there's one that we like to look at,
- 00:41 we can just go and say, hey, let's click on it and
- 00:43 see how it looks on our pivot table.
- 00:45 Now, the other thing though, that's interesting about the way this works is
- 00:49 that you have some pivot table style options over in the corner here.
- 00:52 So I could say uncheck Row Headers.
- 00:55 You'll notice that at this point,
- 00:57 it's actually stopped the shading on the alcohol and food here.
- 01:00 I could say uncheck the Column Headers, and
- 01:03 at that point my pivot table header goes back to this light blue.
- 01:06 You'll also notice the style gallery is actually showing the changes
- 01:10 live inside there.
- 01:11 So we can actually play around with these and say, okay, well after we start making
- 01:15 changes to the options, what does it actually do to the actual gallery?
- 01:19 What if we wanted Banded Rows?
- 01:21 We could click on that.
- 01:22 Now, in this style, it shows some kind of strange pieces here.
- 01:26 But what would happen if we chose a different style?
- 01:28 Could we see something slightly different?
- 01:30 Like maybe how about this one here?
- 01:33 And now you'll notice that we actually have banded rows showing up.
- 01:36 Let me turn the Row Headers back on.
- 01:38 It goes into bold in the style, and
- 01:40 the Column Headers will bring the header back on the pivot table.
- 01:43 What about Banded Columns?
- 01:47 There we go.
- 01:48 We've now got banded columns and
- 01:49 the interesting thing is we don't have to use these together either.
- 01:53 I can actually turn off Banded Rows and leave just the Banded Columns on.
- 01:56 These techniques can be super, super helpful for
- 01:59 when you're working with really really large pivot tables you have large sets of
- 02:02 data banded rows are really nice to be able to say hey this way I can trace it
- 02:06 across without having to put a ruler on the page.
- 02:08 And the same with the columns.
- 02:10 The key thing really is that when you actually take a look through the styles
- 02:13 gallery, the styles gallery is reacting live to all of the changes that you're
- 02:17 making on the left hand side.
- 02:18 So, you can always come back and say hey,
- 02:20 this dark style is way down near the end here.
- 02:22 This one looks like it has banded columns on it.
- 02:25 But if I go and change this out to banded rows and get rid of the banded columns,
- 02:29 let's go down and see does that still fit with what I actually want?
- 02:32 So in this case, the dark blue pivot 27 gives me a format that looks like this.
- 02:38 Ultimately, the color choice is years, but
- 02:40 there's a lot of different ways that you can actually play with this.
- 02:43 Don't be afraid to toggle those little checkboxes and see what happens, because
- 02:46 it's definitely going to have an impact on the way that your pivot table looks.
Lesson notes are only available for subscribers.