Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.