All language subtitles for 7. Grouping PivotTable Data

One of the powerful features of Excel Pivot tables is that you can group data.

So let's take a look at a quick example.

Now I'm going to start out here just by removing a couple of these rows that we've added, so I'm going

to just remove a count of products.

Let's get rid of that and also average gross sales just so we're left with total gross sales.

Now it might be that instead of having the year filter at the top here, maybe I want to display the

year information in the columns.

So I'm going to grab the year and drag it across two columns so I can now see that information.

So I have my gross sales for 2018 and also for 2019.

Now, when you're working with things like years, this is all based on whatever you have in your pivot

table.

So if I take a look at my data, you can see over here, I've got the date in Column K and then I have

month name and year now because I'm using the year field.

Excel is just using the data that it finds in here.

So 2019 or 2018.

But if I use the date field instead, let me show you what happens.

So let's go back to sheet two.

I'm going to remove the year field, and this time we're going to use the date field instead.

So if I drag that down, two columns notice what automatically happens.

It breaks it down into years and quarters.

Because Excel is recognized that the date that I have in this column is made up of a day, a month and

a year.

And because they're all grouped together under the date field, when you drag that date down, it's

going to automatically split them up for you.

Now notice what that's done to my pivot table.

I now have little plus signs next to 2018 and 2019.

If I click the plus is going to show me the quarters.

If I click the Plus again is going to show me the different months.

Now, the way that this is currently organized is not very easy to read.

So let's backtrack a little bit.

Let's collapses back up again and look at this in a different way.

Now, if you're wondering where the grouping is coming from, if I select the 2018 label, go up to

Pivot Table, Analyze Notice that we have a group section just here, and if I go to group field, it's

showing me how the date field is grouped.

So it's going to include months, quarters and the year.

It's also recognizing the start date, so it's found the first date in my data and also the last date

in my data.

So if I decide that I actually don't want to have months, quarters and years, I'm just interested

in, yeah, I could choose to remove both of these.

Click on.

Okay.

And now I don't have those little plus signs, and I can't drill down to see the day in the month information.

So just be aware of that because I know it catches some people off guard.

They think they have one field, they drag it down to the columns and it suddenly splits up into three

separate fields.

That is how Excel handles things like dates.

Now, similarly, we can do the reverse of that as well.

We can create our own groups in our pivot table data.

So for this example, I'm actually going to switch the country and the product around.

So let's drag country above product.

Now, maybe these products are part of different ranges.

So maybe Burlington and Kensington are the premium range luxe and Mandarin are the luxury range, and

Royal Oak and Vermont are the standard range.

Now I can reflect that in my pivot table, even though I don't have that information in my data source

and we can use grouping to help us do that.

So what I'm going to do is select Burlington and Kensington up to Pivot Table Analyze, and then I can

say group selection.

It gives me a name of Group one if you want to edit this group name, so it's a bit more meaningful,

you need to click on the field and press the f two key on your keyboard.

That's then going to allow you to go in and change the group name.

So this is the premium range and enter now I'm going to select the luxury range, and the Mandarin range

lets group selection.

I now have another Group F two to edit and this is the luxury range.

And then my final range is Royal Oak and Vermont.

Let's group this selection.

Click on the cell F2.

This is going to be the standard range.

So very quickly I've managed to divide up and add a bit of structure into my data and also include grouping

that wasn't present in the original source data.

It might also be at this point that you look at your data the way it's being displayed and you might

think to yourself, actually, you know, while I want these subtitles out of the way at the top of

the group and I think I'm actually going to do that, let's go back to subtitles.

Show at top of group.

So that makes it a little bit clearer.

So the takeaways from this lesson are to just be aware of that automatic grouping and modify accordingly.

And also be aware of the fact that you can create your own groups to separate up or add structure to

your data.