All language subtitles for 3. Pivoting the PivotTable Fields

So we're now at the stage where we can start building out our pivot table reports and the way that you

build your rapport really depends on the type of analysis that you want to do.

So what information are you interested in?

Is this something that you're going to need to present to your managers or maybe key stakeholders?

What information are they most interested in?

Maybe they want to see a breakdown of the sales by country, or maybe the profit by country and product.

Or maybe they want to see what the costs are broken down by month or by year.

So it's really important to work out what exactly is that you're trying to extract from your data before

putting your pivot table together.

And the thing with pivot tables is that they're very easy to change.

So if you get the analysis a bit wrong, first of all, it's very easy to remove the analysis that you've

done and do a different one.

So let's take a look at pivot table fields and how we can add fields to our pivot table.

So as we mentioned at the end of the previous lesson, on the right hand side, we have a pivot table

feels pain, and in this pain, we have all of the column headings from our source data.

And then underneath we have these four little blank areas filters, columns, rows and values.

And the idea with pivot tables is that you take one or more of these fields drag and drop them down

into these different areas to build your report.

So let's start out with something really simple.

I'm going to grab the country failed and I'm going to drag and drop this down into the rose area.

Now, when I let go, take a look at what happens.

Notice now that I have all of those countries, a unique list of the countries in the rows.

If I was to put this country filled in the columns area instead, well, like that I'm going to get

my countries running across in the columns.

Now I'm going to put this back into rows, so maybe I want to see a breakdown of the gross sales by

country.

Well, all I need to do is grab the gross sales field and drop that into the values area.

And now I can see that breakdown.

So simple, so quick.

What about if I decide?

Actually, you know what?

I don't want to see the gross sales by country.

I want to see the profit by country instead.

Well, we can remove the sum of gross sales by simply clicking on it and dragging it outside of that

pivot table fields area.

I can then grab profit and drop that down into values instead.

Now, notice here that we don't really have any formatting.

Apply to these numbers.

We are going to deal with that a bit later on at the moment.

I just want you to understand the concept of adding fields and pivoting them.

Also notice that when I dragged profit into values, it automatically did a some aggregation.

And again, this is something you can change.

If you wanted to do a count or an average or a men or a max, you could do that instead of some.

And in general, depending on what field you've dragged into, the values area Excel will aggregate

in the way it sees best for the data that you've tracked.

So sometimes that will be account.

Sometimes that will be a sum because we've got currency values here, monetary values, it's doing a

some of the profit.

So now we have the sum of profit by country.

What about if I want to add in a third dimension?

Well, what I could do here is maybe grab the product field and drag that down into columns.

So now I'm seeing the products listed across the top, the countries, and then I've got the grand total

for both the products and the countries.

Now this grand total has been assigned automatically.

Again, this is something you can turn off if you don't need the grand total now, you don't necessarily

just have to have one field per area down here.

You can stack multiple fields in one area to get a completely different layout.

For example, I could grab product from columns and drop it above country in rows and take a look at

how that changes my layout.

I now have the product at the top and then all of the countries with the sum of profit.

If I grab country and move that above product, it switches it around.

So now I'm summarizing by country and then by product, I could see both the sum of profit and the gross

sales if I wanted to, so I could just grab gross sales and drag that into values as well.

And I'm going to get another column.

And the final area that we haven't really looked at here is this filter's area.

So maybe I want to be able to filter all of this information by year.

So I'm going to grab here and drop that into filters, and that means I get this little filter drop

down at the top and I can select which ever year I'm interested in and all of the data will up to.

So that is basically how a pivot table works.

It is very straightforward.

But for some reason, there seems to be this idea out there that pivot tables are quite difficult.

But when you break it down in this way, it does make a lot of sense.

It's really logical.

And as I said, if you make a mistake, it's not a problem.

You can simply drag any of these fields out of the fields area and redo your analysis.