PivotTable Formatting

Quick reference

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