Advanced Pivot Table Tips and Tricks - Part 1

Quick reference

• 00:04 In the previous lesson,
• 00:05 we did a quick recap on how to put together a pivot table.
• 00:08 And we're going to move our skills along a little bit in this lesson by
• 00:12 taking a look at some of the more advanced pivot table tips and tricks.
• 00:16 So let's dive straight into our first tip.
• 00:19 Now we're starting out with the pivot table that we created in the last lesson.
• 00:24 Now one thing you'll notice, particularly because of the way that we have this
• 00:28 pivot table arranged, is that we have some blank cells in here.
• 00:32 Now when it comes to blank cells in your dataset, you can leave them,
• 00:36 it's not going to be too much of a problem.
• 00:38 But it is much better to add something in there so
• 00:40 that people don't think that you've just forgotten to add in some numbers.
• 00:45 Now a quick way that you can do this and ensure that whenever we have a blank cell,
• 00:49 even if we rearrange the fields, we get a zero in there,
• 00:53 is to jump up to the PivotTable Analyze ribbon and open up Options,
• 00:57 which you'll find in that first group.
• 01:00 So once we open up our PivotTable Options,
• 01:03 we need to jump to the Layout and Format tab.
• 01:06 And towards the bottom in the Format group, you can see we have a little item
• 01:10 here that says For empty cells show, and currently we don't have anything in there.
• 01:16 So if we put a 0 in here and click on OK, it's going to fill all of
• 01:21 those empties with a 0, which in general looks a lot better.
• 01:25 Now tip number two is related to grouping data in your pivot tables.
• 01:30 And just to demonstrate this really clearly,
• 01:32 I'm actually going to make a change to the layout of my pivot table.
• 01:36 So we're going to remove the Country field, simply by dragging it out, so
• 01:40 we just have the different item types just here.
• 01:43 Now, maybe I want a more granular breakdown and
• 01:46 I want to group some of these item types together.
• 01:50 So what we can do here is we can select the items that we want to group together.
• 01:54 So Baby Food, I'm going to hold down Ctrl and select Beverages,
• 01:59 Cereal, Fruits, Meat, Snacks, and Vegetables.
• 02:03 And then from the PivotTable Analyze ribbon in the Group group,
• 02:07 we're going to choose Group Selection.
• 02:10 It groups all of these together and at the top it says Group 1.
• 02:14 Now, we can change this to whatever we want it to say.
• 02:17 So we need to press F2 to edit this cell, and
• 02:21 I'm going to change this group to Food and Drink, and hit Enter.
• 02:26 So now we have those grouped together.
• 02:28 And if I want to carry on going, I can.
• 02:30 So maybe this time I want to group together Clothes, Cosmetics, and
• 02:34 Personal Care.
• 02:35 I'm going to choose Group Selection.
• 02:38 Let's click where it says Group 2 this time and press the F2 key to rename.
• 02:43 So we'll just call this Cosmetics and Clothing.
• 02:49 And then the last group, we'll just group these last two together.
• 02:52 Let's go up to PivotTable Analyze, Group the selection,
• 02:56 and then F2 to rename Group 3, and we'll just call this Home.
• 03:01 So very quickly there, we've managed to create our own custom groups.
• 03:05 And if I now add the Country field back in, so let's just drop that back down, you
• 03:11 can see that those groups carry through for all of the different countries.
• 03:16 At the bottom of each we'll get a subtotal as well.
• 03:19 Now if you don't want to show that, if you find it a bit confusing,
• 03:22 jump up to Design and you can say Do Not Show Subtotals.
• 03:25 Or alternatively you can choose to show them at the top of the group,
• 03:29 which makes everything a little bit easier to read.
• 03:31 Now another thing you'll find when you're working with pivot tables is that you
• 03:35 might like to have your pivot table columns at a particular width.
• 03:39 So I've widened mine out, but what you'll find is that whenever you make a change
• 03:44 to this pivot table or maybe you refresh it, so let's quickly give this a little
• 03:48 refresh, it's going to resize those columns back to the default.
• 03:52 Now that can be particularly annoying if you've spent quite a bit of time getting
• 03:57 those columns set to exactly the width that you want them.
• 04:00 So what you can do here is resize the columns so
• 04:03 that they are the width that you like.
• 04:06 And then once again on the PivotTable Analyze ribbon,
• 04:09 we're going to jump into Options.
• 04:11 And on the Layout and Format tab at the bottom,
• 04:14 you can see it says Autofit column widths on update.
• 04:17 So we want to deselect this option, click on OK, and now if we click refresh or
• 04:23 change anything, those column widths do not change.

Lesson notes are only available for subscribers.