Choosing PivotTable Layouts

Quick reference

• 00:04 In this video, we're going to look at the different layout options we have for pivot
• 00:08 tables, because there's actually three and they're all just slightly different.
• 00:12 So to begin with what we'll do is we'll start by inserting a new pivot table off
• 00:17 of this data set.
• 00:18 So we'll go to insert pivot table, once again, build it off the source data range.
• 00:22 And I'm going to go and configure it using class on rows,
• 00:26 category on rows underneath it, we'll put units on values.
• 00:31 And I'm going to jump up here and say let's put the hour on columns.
• 00:35 So we get a pivot table that looks nicely like this.
• 00:38 Now, just to make this a little easier, I'm going to go and
• 00:40 close the pivot table field list so that we can see a little bit more on screen.
• 00:43 And now we're going to play around with the different options.
• 00:46 You can find all of these under the PivotTable Tools Design tab.
• 00:51 Now, the view that we're looking at right now is what we call compact form.
• 00:56 And I'm going to just go and rename this worksheet down the bottom here into
• 01:00 Compact, because this is what we're actually looking at right now.
• 01:03 And you can see what's happening here is that we have alcohol, and
• 01:06 in the same column, column A, we also have the actual subcategories as well.
• 01:11 This is the default view of a pivot table that comes out.
• 01:15 Now, I want to show you a slightly different view of this one.
• 01:17 I'm going to go right-click, Move or
• 01:19 Copy, because there's no point in creating a whole new pivot table again.
• 01:22 And I'm going to move this one to the end.
• 01:24 And this one, I'm going to go and
• 01:26 rename to Outline because this is going to be the outline form.
• 01:31 Where do we change it?
• 01:32 We go up to Report Layout, and instead of showing a Compact Form,
• 01:36 which is what we have by default, we can go to Outline Form.
• 01:39 And what you'll notice is that immediately column A gets broken into column A and B.
• 01:45 So we can now see our alcohol, or our class, in one column, and
• 01:49 our category in a separate column.
• 01:51 And this makes it a little easier to read some of the stuff that's actually going on
• 01:54 because they're not nested under each other.
• 01:57 Now, as it happens here, this is one of the other views that we have.
• 02:00 We're going to do one more, right-click, Move or Copy > Create a copy,
• 02:05 and I'm going to move this one to the end as well.
• 02:07 And this one is going to be the Tabular view.
• 02:11 It looks very similar to outline form.
• 02:14 But when we go back into Design > Report Layout, and we choose Tabular,
• 02:19 you'll notice that the thing with Outline is that the subtotals are at the top,
• 02:22 with Tabular, they're actually at the bottom.
• 02:25 Now, the view looks a little bit different here as well.
• 02:27 It's put lines in it, it's not quite as spaced out.
• 02:30 But then the question you would have is, okay, well, that's cool and
• 02:33 everything else, but can I get in my Outline form my subtotals at the bottom?
• 02:38 Well, of course you can, we already know that.
• 02:40 We can go and say,
• 02:41 let's go to PivotTable Tools > Design > Subtotals > Show at Bottom of Group.
• 02:46 So what's the difference between outline and tabular in this case?
• 02:49 Outline has a blank row with each of the headers,
• 02:53 tabular is compressed and does not.
• 02:56 Look at some of the other options we can play with.
• 02:59 We can go with Report Layout > Repeat All Item Labels.
• 03:03 What you'll see here is that alcohol will get filled down into all the blank cells,
• 03:07 as will food.
• 03:08 So if I do that, it shows up this way.
• 03:11 This also works on our Outline form, Report Layout > Repeat All Item Labels.
• 03:17 But it does not work on the compact form because there's nothing for
• 03:21 it to actually fill into at this stage,
• 03:23 because underneath our alcohol class, our categories are in the same column.
• 03:27 So that's not going to work for us.
• 03:29 What about another option, Blank Rows?
• 03:32 We can go and say Insert Blank Line after Each Item.
• 03:36 Now, this isn't going to do the individual categories, but it will do
• 03:39 the actual master group here and insert a blank row after each item here.
• 03:44 If I go back to Outline, I can try the same thing, Insert,
• 03:49 it's going to give me a little bit more spacing.
• 03:51 And likewise in Tabular, you can see the same thing happen here as well.
• 03:56 What happens if we go and show our field list and we add even more items into this?
• 04:01 Let's say, for example, we grab item name and slide it in underneath?
• 04:05 Notice that it's now got a blank row after the subtotals for bottled beer,
• 04:08 canned beer, and cooler/cider.
• 04:11 Now, I'm going to get rid of that because I don't actually need it on here,
• 04:13 just wanted to show you exactly how that works.
• 04:16 The final thing I want to show you with the pivot table layouts
• 04:19 is in compact form we can also control the indentation level of what's actually here.
• 04:24 To do that, we can go right-click and say PivotTable Options.
• 04:28 And on the options, you'll notice, under Layout & Format, when in compact form,
• 04:32 we can now indent this by a different number of characters.
• 04:35 And what will happen here when I click OK as you watch,
• 04:37 this will actually indent just a little bit further.
• 04:40 So those are pretty much all the options we have for playing with pivot table
• 04:44 layouts to make them look the way that we want to see them.

Lesson notes are only available for subscribers.