Locked lesson.
About this lesson
Explore advanced Pivot Table Tips and Tricks to supercharge your analysis skills.
Exercise files
Download this lesson’s related exercise files.
06-02-Advanced Pivot Table Tips and Tricks Part 1-Start.xlsx759 KB 06-02-Advanced Pivot Table Tips and Tricks Part1-Complete.xlsx
690.8 KB 6.02 advanced-pivot-table-tips-and-tricks-part1 - Exercise.docx
53.3 KB 6.02 advanced-pivot-table-tips-and-tricks-part1 - Exercise solution.docx
229.1 KB Exercise - Advanced PivotTable Tips and Tricks - Part1.xlsx
110 KB Exercise Solution - Advanced PivotTable Tips and Tricks -Part1.xlsx
110.4 KB
Quick reference
Advanced Pivot Table Tips and Tricks
Learn some advanced PivotTable Tips and Tricks.
When to use
We use the advanced features of PivotTables whenever we want to supercharge our analysis.
Instructions
Let's take a look at some common advanced formatting techniques.
Fill Blank Cells
Depending on our analysis, some cells might show nothing in them. This is usually because there is no date for this particular layout. Blank cells aren't too much of an issue but could cause confusion when using Pivot Charts.
We recommend that blank cells are filled with a zero.
- From the PivotTable Analyze tab, in the PivotTable group, click Options.
- Go to the Layout & Format tab.
- Check the box next to For empty cells show.
- Enter a 0.
Group Data
We can create our own custom PivotTable groups to group data.
- Remove the Country field from the Rows area.
We now have a list of the total costs by Item Type and Sales Channel.
Let's add some groups for our item types.
- Select 'Baby Food'.
- Hold down CTRL and select 'Beverages', 'Cereal', 'Fruits', 'Meat', 'Snacks', and 'Vegetables'.
- From the PivotTable Analyze tab, in the Group group, click Group Selection.
- Click in the cell where it says 'Group 1'.
- Press the F2 key to edit.
- Type the name 'Food and Drink'.
- Repeat this process and groups the items according to the screenshot below.
Preserve Column Widths
When we are working with PivotTables we might set our column widths to a very specific size. However, when we refresh the pivot table or move fields around, Excel will change the column widths back to the default width.
This can be annoying if you don't want them to revert to the default.
- From the PivotTable Analyze tab, in the PivotTable group, click Options.
- Go to the Layout & Format tab, in the Format group, de-select Autofit column widths on update.
- 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.