Choosing PivotTable Layouts

Quick reference

• 00:04 In this video, we're gonna look at the different layout options we have for
• 00:07 PivotTable cuz there's actually three, and they're all just slightly different.
• 00:11 To start with,
• 00:12 what we're gonna do is we're gonna insert the pivot table off of this dataset.
• 00:15 And we'll take a look at what shows up by default.
• 00:19 So, what I'm gonna do is I'm gonna throw class on rows.
• 00:22 And I'm gonna throw category below that.
• 00:24 And the POS chit hour across the top.
• 00:26 And we'll throw units into value.
• 00:28 So nice quick pivot table here.
• 00:30 This is built in what's called compact view.
• 00:33 And basically what happens is we've got subtotals showing at the top,
• 00:37 we've got all our data in the values area, which is good, and our row labels,
• 00:41 you'll notice that our class shows up in bold on the left to,
• 00:45 based on our style here and these guys show up just indented a little bit.
• 00:49 Now, we do have the option to control the indenting, how far it goes
• 00:53 by going to PivotTable options and actually changing when in compact mode, or
• 00:57 form, indent your row labels by, let's say, three characters.
• 01:01 You'll see that just sort of indents it over a little bit.
• 01:03 Okay, so that is compact view, so I'm just gonna mark that down on this worksheet.
• 01:09 And rather than create the pivot table all over again, we'll move our copy,
• 01:13 we're going to create a copy and move this worksheet to the end.
• 01:17 And this guy is going to be the outline view.
• 01:22 To change the view of the pivot table,
• 01:24 what we do is we go to Pivot Table Tools Design.
• 01:27 We go to report layout, and we change the view.
• 01:31 Show in outline four.
• 01:32 And you'll notice what happens here that's most important is that we actually have
• 01:36 no longer indented in the same column like we had with our compact view.
• 01:41 This was all in the same column here.
• 01:43 We now have separate columns.
• 01:45 One for the first level of row labels and another for
• 01:48 the second level of row labels.
• 01:50 We've also got a little bit more spacing.
• 01:52 Like with compact view, the headers are at the top, or sorry, the subtotals rather,
• 01:56 are at the top.
• 01:57 Okay, so that's how outline view looks.
• 02:00 And then, there's one more, if we move or copy, we'll create a copy,
• 02:04 move this one to the end as well.
• 02:06 I'm going to rename this guy here to tabular.
• 02:10 And as an accountant this is my personal favorite.
• 02:13 I'm going to go to report layout on the pivot table tools design tab and
• 02:17 we'll show in tabular form and watch what happens.
• 02:21 So we have the same kind of spacing, you can very much see that we have
• 02:24 a second set of first columns and our second columns.
• 02:28 And our sub-totals are at the bottom,
• 02:29 which is where an accountant thinks they should be, much better.
• 02:33 So those are the main differences, compact view that's nice and compacted here.
• 02:38 Outline view that's a little bit more spread out, but
• 02:41 we have more space in here, and tabular view, okay?
• 02:44 Now, you'll also notice with outline view, the header has it's own row.
• 02:48 Or sorry, the sub-totals because they're above.
• 02:50 If we were to flip the sub-totals below,
• 02:54 they still have their own header row where tabular does not so it's another feature.
• 02:59 Now we have a couple of other things that we can do with our pivot tables.
• 03:03 One of them is that we can actually go in these areas so
• 03:06 we can fill these values all the way down by going to repeat all item labels and
• 03:11 that will fill them all the way down the column.
• 03:13 Now this only works for tabular view, it does not work for
• 03:17 outline or compact view, if you wanna turn them off afterwards, or
• 03:21 report layout again do not repeat item labels.
• 03:25 One final thing we can look at with our report layouts is that we can actually
• 03:29 insert blank rows after each of our different row label fields.
• 03:34 So if we were to go to blank rows, insert blank line after each item.
• 03:39 You'll notice that we now have a blank row between Alcohol Total and Food, so
• 03:43 it's given a little bit more white space.
• 03:45 Now this will apply a blank row between every one of the label fields.
• 03:51 It's not gonna apply, base on the details of the very last one, though.
• 03:55 So if I were to go and, say,
• 03:56 throw item name down in here, you'll notice that we now have Bottled Beer.
• 04:00 We've got all of our Budweiser, Kokanee, and the rest of it.
• 04:03 And then we've got a blank row before Canned Beer that wasn't there before.
• 04:06 Same with between Canned Beer and Coolers/Cider.
• 04:09 If I go and pull this back off the table, you'll see it collapses back in.
• 04:13 This particular feature works on outline view as well.
• 04:18 So, we go to blank rows, insert blank, there you go.
• 04:22 How about compact view?
• 04:23 Go to blank row, insert blank line, yes, it absolutely does.
• 04:27 We can also remove them just as easily by going back and
• 04:30 saying remove blank line from each item as well.
• 04:32 So that's how you get rid of them if you decide that you don't actually wanna
• 04:35 see them there.
• 04:35 So, those are the different views that we have for pivot table layouts,
• 04:39 from compact outline and tabular, and the different features that work with each.

Lesson notes are only available for subscribers.