All language subtitles for 6. Show Values As and Summarize Values By

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

In business, we're going to take a look at the show values as and the summarize values by options.

Now, before we get going, I'm actually going to remove one of these columns from my pivot table.

So I'm going to remove the total profit column simply by dragging it out.

So I just have the total gross sales in that.

Now we've already seen when we were looking at no formatting, if we right click our mouse, we're getting

that contextual menu with options that are relevant to where we currently collect.

And you'll notice that we have a little group here.

Summarize values by and also show values as.

So let's take a look at this one.

First of all, now notice in the menu.

Currently, I have no calculations selected for what I could do is to show the values in this column

in a different way.

So, for example, I could show them as a percentage of the grand total.

So if I click this option, it changes my figures to percentages so I can now see that for Japan, the

Burlington product made up 2.7 one percent of all sales, and I can see this broken down in the subtitles

as well.

So another useful thing I could do would be to use my collapsible and expandable menus just here to

collapse up all of the detail and then just see the percentage of gross sales for each of the products.

So this makes it very easy to tell which product is the most successful I can see here.

Twenty eight point two nine percent of all sales were for the Vermont product.

Now, with these collapsible and expandable items just here, if you don't want to go through and click

the Plus to expand them all again.

Notice that on the pivot table, analyze ribbon in the show group.

At the end, we have a plus and minus button.

So if I click this, it's going to remove them, or I can click again to add back in and I can right

click go down to expand collapse, and I can choose to expand the entire field just to put all those

back again.

Now, if we go back to our right click menu and into show values as we have a lot of different options

in here.

So it really depends how you want to display these figures.

We're currently displaying as a percentage of the grand total, but I could choose to display the percentage

difference.

So if I had, maybe I was comparing years of gross sales, I could see the percentage difference from

the previous year.

I could also display a running total as well.

So let's do running total in.

I'm going to base this on the country field.

So if we click on OK, I can now see what that running total is and a running total is just adding the

previous total to the current total.

So you can see here for United Kingdom, we have 13 million.

The one before was roughly five million.

So the one for Brazil is these two added together.

So we get a nice running total going on there by that country field.

So definitely worth having a little explore and experimenting with some of these.

Now I'm going to put this back to no calculation, so the values are as they were originally.

Now, if we right click once again, we also have a summarize values by option just here.

And this is what I was talking about earlier.

When you drag a field down to that values area.

Excel is going to do its best to work out how you want to summarize your data.

So for the type of values that I'm using in this case, gross sales, it's worked out that I'm probably

going to want to do a sum.

That's how I'm going to want to aggregate this information.

But it might be for some pieces of data you want to do a count or an average or a max.

So for example, if we just remove total gross sales from here and let's replace it with, I'm going

to put product down into values.

Now notice that it's automatically doing a count instead here.

So it's telling me how many have been sold for each country and each product instead of doing a sum

of the gross sales.

So this can be super useful.

Maybe I want to do a count of the product, but I also want to see the gross sales.

So let's grab our gross sales field, drag that back down into values.

Notice here it will put the title back to some of gross sales.

If you remember, we did change that, so let's double click.

Total gross sales.

And once again, notice that the number formatting has also reset.

So you're going to need to go back in and reapply your number formatting.

So let's take those down to zero.

Let's set our negative numbers, and there we go.

So now I have two pieces of information.

I can see a count of the product and I can see the total gross sales.

What about if I wanted to see the average of sales across each of these countries?

Well, what I can do is grab another gross sales field and drag it down to values so you can drag and

use the same.

Fired multiple times in each of these areas.

Now notice once again, it's just given me basically a duplicate of what we've got in the previous column,

but this is where I can right click and go to summarize values by and choose how I want to summarize

that data.

So if I want to see an average as well as the sum, I could change this column to average.

And now I'm seeing my average of gross sales.

Once again, I need to right click and I need to apply my number formatting.

Let's go to currency.

Take that down to zero and set our negative numbers.

And whilst I'm here, I'm actually going to change the custom name and just remove the word of to keep

it consistent and click on.

OK.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.