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

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.