All language subtitles for 7. Grouping PivotTable Data

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.