Locked lesson.
About this lesson
PivotTables - whether standard or created via Power Pivot - use the same behaviors for formatting. In this module we'll look at how to properly apply Excel's formatting options to PivotTables.
Exercise files
Download this lesson’s related exercise files.
PivotTable Formatting.xlsx1.8 MB PivotTable Formatting - Completed.xlsx
1.8 MB
Quick reference
PivotTable Formatting
An overview of basic formatting that can be applied to Pivot Tables.
When to use
When you want to customize the look and layout of either basic Pivot Tables or Power Pivot Tables.
Instructions
Access these options via the Pivot Tables Tools tab --> Design tab:
- Subtotals
- Do Not Show, Show at Bottom of Group, Show at Top of Group
- Grand Totals
- Do Not Show, Show for Columns only, Show for Rows only
- Report Layout
- Compact View, Tabular View, Outline View
- Repeat all item labels
Format your number values using the Value Field Settings dialog
- Doing so applies the formatting to the entire Pivot Table instead of just the selected cell(s), even when the Pivot Table is expanded (new rows or columns added)
Access these options via right clicking the PivotTable --> Options:
- Autofit Column Widths on Update (uncheck to not resize columns upon refresh)
- For Empty Cells Show (x) (use 0 to replace blank cells with zeros)
Hints & tips
- You cannot create a custom Values field name which uses the same name as an existing field
- To get around this, add a space to the end of the custom Values field name when you set it in the Value Field Settings dialog
- 00:04 Let's look at some pivot table formatting techniques.
- 00:09 What we're gonna do is we're gonna start with the design layout of the pivot table.
- 00:13 And where you can find these is, when you click inside your pivot table,
- 00:17 you'll notice the Pivot Table Tools Design tab comes up.
- 00:21 And over here we have the ability to change the report layout.
- 00:24 The default layout that you're seeing here is in what we call Compact Form.
- 00:29 And you'll see that that's this one right here.
- 00:31 If I click on it, it makes no changes whatsoever.
- 00:34 I also have the ability to move to something called Tabular Form.
- 00:38 Now, the big difference between Compact and
- 00:40 Tabular is what happens in the first column.
- 00:42 You'll notice right now that our groups and our categories here,
- 00:47 or classes, rather, are actually all in the same column.
- 00:51 So if I click over here on design and I go to report layout and
- 00:55 change to tabular form, it actually breaks those two things into separate columns.
- 01:01 Now if I want expenses and
- 01:02 revenues filled all the way down, I can click inside the pivot table.
- 01:06 And on the design tab, I can go to report layout.
- 01:09 And repeat all item levels.
- 01:11 This will repeat all items down as well as all items across the header should that be
- 01:15 necessary.
- 01:17 I can also of cause turn that off.
- 01:19 Now you'll notice in tabular form that we have our sub totals at the bottom.
- 01:26 I also have another form that i can play around with which is
- 01:29 my personal favorite which is called outline form.
- 01:32 And what outline form does, the difference between our outline and
- 01:36 our tabular form is that it actually moves your subtotals to the top but
- 01:41 gives a little bit of white space above the actual group names here.
- 01:45 Now, I'm not a fan of this view, but
- 01:47 what I am a fan of is when I move my subtotals to the bottom of this guy.
- 01:51 So I can come over here and
- 01:53 change my subtotals to show at the bottom of the group.
- 01:57 So the difference now between the tabular form and
- 02:00 the outline with subtotals at the bottom is that I actually have a nice little
- 02:05 white space row here that actually separates those groups up.
- 02:10 We can play around with things like grand totals.
- 02:13 Right now there is a grand total on this guy here which might make sense if I am
- 02:16 summarizing all my departments.
- 02:18 But if I am doing things where I am showing multiple years it might not make
- 02:22 sense to have a grand total on here.
- 02:24 So I can go and say grand totals and I could choose.
- 02:27 Instead of using off for everybody which would remove all the grand totals for
- 02:32 both top and bottom, I could choose to go and say give me on for calls only.
- 02:36 And that will only sum them vertically, leaving me with totals at the bottom.
- 02:41 So that looks a little bit nicer as well.
- 02:44 We have pivot table styles that we can play around with.
- 02:46 If you wanna change the color on your pivot tables.
- 02:49 We can go on to this gallery here and you can choose.
- 02:51 You can mouse over, roll over them, you don't even have to apply them.
- 02:54 You can see what the different formats might look like.
- 02:56 So if you say, you know, I like this guy here.
- 02:58 I can click on it and that'll actually make my pivot table look like that
- 03:02 in the future, which is not too bad.
- 03:06 Numeric values, I wanna talk about these guys here real quick.
- 03:08 One of the things you actually saw me do in a previous video was
- 03:11 highlight the entire column and go and apply a comma style to it.
- 03:15 That works, but it's not the best way of dealing with things.
- 03:18 In order to reformat values in a pivot table,
- 03:22 I'm not a big fan of the way these look right now.
- 03:24 What you should do is you should right click on one of the values
- 03:27 in that specific column.
- 03:28 So this is a value based on the sum of amount column.
- 03:31 Because it's separated out by department there's multiple of them, but
- 03:34 right now it's all about sum of amount.
- 03:36 What we're gonna do is right click and choose value field settings.
- 03:40 Inside here, we can change the name of these guys.
- 03:43 We can change the name to something like amount, except that if I try and
- 03:48 actually do this, you'll see that I get an error.
- 03:50 And that's because amount already exists as a pivot table field name.
- 03:55 So I can make it amount space will allow me to actually make this work.
- 04:00 Now when I say okay, you'll see that sum amount here will change, and
- 04:04 it just calls it amount space.
- 04:06 That's the way to work around that little problem.
- 04:08 In addition, though, inside this dialog of value field settings,
- 04:12 we can also get into the number format.
- 04:15 And in the number format, I could go and
- 04:17 say, I want this to look like a number, I don't want any decimal places on it.
- 04:21 I'd like to use thousand separators and, you know what,
- 04:24 I'd also like to turn negative numbers red.
- 04:26 And at this point, when I say okay, you'll notice that even though I had actually
- 04:32 only selected one cell here it applies to every one for the sum of amount column.
- 04:38 And that's really useful because even when the pivot table expands or
- 04:41 shrinks it's always gonna pick that up.
- 04:43 So that's something that you wanna do as a matter of best practice,
- 04:46 is to apply your formats through the value field
- 04:49 settings not by using the commands off of the ribbon.
- 04:52 You have to get them exactly right in order to make that stick.
- 04:56 One other thing that's really useful to know for
- 04:58 formatting your pivot tables is the pivot table options.
- 05:03 Inside options, there's a couple of really important things here.
- 05:06 Number one of them, auto-fit column widths on update.
- 05:10 As I've been refreshing the pivot table, or as I've been changing things,
- 05:13 you'll notice that I can take something that has a nice little column set up
- 05:16 exactly where I want.
- 05:17 And when I right click and refresh, It throws away all that hard work.
- 05:22 I can change that in pivot table options to say do not autofit.
- 05:27 And while I'm here, I'm also not a big fan of having all these empty spaces
- 05:31 where nothing's been summarized, so I'm gonna choose for empty cells show 0.
- 05:35 And that will use my current number format, and
- 05:39 now you'll notice it fills in with all those arrows.
- 05:42 The table now looks complete.
- 05:43 And if I go and resize all of these columns to get them exactly right,
- 05:48 now when I right click and
- 05:50 refresh my pivot table, everything stays exactly as I've set it up.
- 05:54 So there are some things that are some helpful formatting tricks.
- 05:57 They work on both regular pivot tables and on Power Pivot pivot tables as well.
Lesson notes are only available for subscribers.