Formatting PivotTables

Quick reference

• 00:04 In this video we're going to look at some of the different formatting that we can
• 00:07 apply very quickly to a pivot table as well.
• 00:10 Now, you'll notice right off the bat when I look at this particular pivot
• 00:15 table my decimal places are all over the place, and that's really not ideal.
• 00:18 I'd like that to look a little bit better, so in order to fix that,
• 00:22 what I'm gonna do is I'm gonna right click on the pivot table, and
• 00:25 I'm going to go down to field settings.
• 00:28 Now before we go into the actual number formatting, you'll notice that we actually
• 00:31 have the option to change the summarize by setting from sum to something else.
• 00:35 So if I wanted to get, say, a count on the number of adorable kitty cats that
• 00:40 were sold, I could just change this to count, and when I say OK, the pivot
• 00:44 table will re-aggregate and show me how many individual items have been sold.
• 00:49 So that's kind of a neat thing to see.
• 00:52 Again, if I go right click and go to field settings,
• 00:55 I could quite easily set this back to sum, and when i click OK, it'll actually put
• 00:59 the sum back in there to give me the count of how much commissions we actually had.
• 01:04 Now, what I'm going to do at this point, is I'm going to click on Number.
• 01:08 And I'm going to go to Accounting, and I'm gonna change this to not use any
• 01:14 currency indicator, and we'll go with a two decimal place.
• 01:16 You can see the sample of how my number should look.
• 01:19 So when I say OK, we're gonna flip back from the count of commissions to the sum
• 01:23 of commissions, and you'll notice now that my decimal places are all nicely lined up.
• 01:29 Now why would we go through field settings to do this rather than just go to the Home
• 01:32 tab and format this with whatever number style we want?
• 01:35 The answer to that is that when you actually go and control your field setting
• 01:39 things and go through and set up your numbers the way that I just did,
• 01:42 that's now in the pivot table's style.
• 01:45 So as the pivot table expands or shrinks,
• 01:49 all new numbers will actually inherit that particular style for that column.
• 01:52 So that is an important thing because that may not happen if you
• 01:55 are using the home tab to deal with things.
• 01:58 All right, let's look at some more configuration stuff here.
• 02:01 The next piece that I wanna do,
• 02:02 is I wanna move one of the fields on my PivotTables around.
• 02:05 But unfortunately, you'll notice that even though I'm clicking outside and
• 02:08 back in the PivotTable, my field list didn't come up.
• 02:11 And that's because I'd clicked the little red x to dismiss it earlier.
• 02:13 So what I need to do is I actually need to go, and on the PivotTable Analyze tab,
• 02:18 I'm gonna click field list, and that will bring it back.
• 02:22 So now I have my field list back,
• 02:24 I can start playing around with the different fields in different places.
• 02:27 So what I'm going to do is I'm gonna drag sold by.
• 02:30 I'm gonna put it above inventory item.
• 02:34 Oops, I'm gonna try and put it above inventory item.
• 02:36 There we go, not quite, we'll drag inventory item below then.
• 02:39 There we are. You might have to
• 02:41 fight with it a couple times but eventually you'll get there.
• 02:43 So, a couple observations here.
• 02:47 You'll notice that our numbers subtotal above are individual data.
• 02:52 So here's the total for Fred's sales here, or his commissions.
• 02:57 What if I wanted those down below?
• 03:00 Well, to do that I go to the design tab.
• 03:03 And I'm going to subtotals, and I'll say Show Subtotals at the bottom of the group.
• 03:09 And that will actually flip it down.
• 03:10 So it says Fred Total now, and he's showing underneath.
• 03:13 We've also got the ability to expand and collapse these sections.
• 03:17 You'll notice that we now have the little plus minus icons.
• 03:20 We can click on those to collapse an individual person or
• 03:24 click on the little plus to open it up again.
• 03:26 Or we could right click, we could go to group and outline, and
• 03:30 we could hide details, which will actually hide everybody's details,
• 03:34 allowing us to drill into a specific person.
• 03:37 Or again right click and go to Group and
• 03:41 Outline, Show Detail will expand those rows for the entire PivotTable.
• 03:45 So that 's kind of a nice little trick that you might want as well.
• 03:49 What about the ugly column header here where it says Sum of Commission?
• 03:54 It would be much nicer to have that read Commission, so let's try that.
• 03:57 Let's go with it. We'll see.
• 03:58 C-O-M-M-I-S-S-I-O-N.
• 04:01 And Enter.
• 04:03 And it will tell you that the pivot table field name already exists,
• 04:06 which is a little frustrating, but that's true.
• 04:08 It actually shows up over here in the field well list on the side.
• 04:12 But we can fake Excel out with this, because the name needs to be unique.
• 04:16 So if you put a space in front of it and say okay, it'll actually accept it.
• 04:21 And it even renames it in the Values area over here.
• 04:24 So that's kind of a cool little trick too to make things look a little nicer.
• 04:28 One other thing that you may want to do,
• 04:30 is you may want to change the layout of your report.
• 04:32 Right now, everything's nested in column A.
• 04:35 We see Fred and then we indent a little bit to adorable kitty cat.
• 04:38 But we have the ability to actually go to our report layout and
• 04:42 change our report layouts into some different forms.
• 04:45 So in this case here, if we go to Tabular Form, you'll notice that it actually
• 04:49 puts Fred in one column, Inventory Items in the next column, and our Commissions.
• 04:54 And of course, we could change different fields around,
• 04:56 however we'd like to look at it.
• 04:58 If I put my Sold By over here, it looks pretty much like it did before.
• 05:02 I could put it underneath or above Inventory Item, and
• 05:05 it's gonna show a different view, but
• 05:08 regardless, it's a slightly different format that's not nested anymore.
• 05:13 There's obviously more things that you can do with this as well.
• 05:15 One of the things that you may want to do is hit the repeat all item labels,
• 05:19 which would fill Fred all the way down that particular column.
• 05:22 So, that's a few more formatting options to work with inside a pivot table.
• 05:27 One last one that we need to focus on is when you work with a data set,
• 05:32 and you end up with a huge number that ends up in your data, and
• 05:37 you go over and you refresh your Pivot table.
• 05:40 You'll notice that the column width changes, and it does that so
• 05:43 that you can always make sure that everything fits inside.
• 05:46 The problem is, though, when you get everything set the way you want, and
• 05:51 then you refresh your data, it will also shrink back down, and that's not ideal.
• 05:56 So there's a setting to fix that.
• 05:58 If we go into PivotTable Options, and we go to the Layout tab,
• 06:03 we can choose to not autofit the column widths on update.
• 06:07 And at this point, if we go bank and we say, let's widen that up again,
• 06:12 right click and refresh, the column width won't change.
• 06:16 Even when we go back and set our original data back to what it was supposed to be.
• 06:21 And we go over to our pivot table, right click, and
• 06:25 refresh, the column width will stay exactly as we designed it.

Lesson notes are only available for subscribers.