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
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.