Original subtitles

It's time now for us to do exercise six, where we're going to practice the skills that we've learned

in this section relating to pivot tables.

So currently we have our clean data set in an Excel table and that Excel table is called stool underscore

sales.

So now what I'd like you to do is to create a pivot table using this data.

Once you've created the pivot table, I'd like you to add the category to columns, the town to rows

and the sales to values, and I'd like you to rename the headings on the pivot table to make them meaningful.

I'd like you to turn off grand totals and apply currency number formatting to all cells that contain

values with zero decimal places.

I then like you to apply a top five filter by sum of sales.

And then finally, I'd like you to name the pivot table sales by town cats.

So quite a bit of work to do here now is the time to pause the video and give it a go.

If you get stuck or you won't see my answer, then please keep watching.

So the first thing we're going to do here is we're going to create a pivot table.

So as I'm already on the table design ribbon, I'm going to click on Summarize with Pivot Table.

It's picked up my store sales range, and I want to add the pivot table to a new worksheet.

Let's click on.

OK, so now I get my pivot table fields over on the right hand side, and I asked you to add the category

into the columns, the town into the rows and the sales into the values.

The next thing we need to do here is we need to rename our pivot table labels just to make them a little

bit more meaningful.

So where we have row labels, I'm going to double click and I'm going to change this to towns where

we have column labels.

We're going to change this two categories.

The next thing I asked you to do was to turn off grand totals.

So I have a grand totals column and also if I go all the way down to the bottom are grand totals Ryan.

And I want to turn these off for both of these.

So up to the pivot table design ribbon into the layout group and we want to turn grand totals off four

rows and columns.

I then asked you to apply currency number formatting with zero decimal places to all of the values in

the pivot table.

Now you could do this column by column.

I find it easier to select a cross section across all of the columns, right click and go into value

field settings.

No format.

And we're going to choose currency.

Take this down to zero.

Click on OK and OK again.

And it's going to change them all.

Now, currently with the pivot table in this state, we have a lot of data and we want to create some

pivot charts in the next session, and this is way too much data to put in a chart.

So the next thing I asked you to do was to create a top five filter by sum of sales.

So for this, we can right click and go to filter.

I'm going to use the top 10 filter and then we're going to modify that to show the top five items by

the sum of sales.

Click on OK.

And now I'm just seeing the highest selling categories and towns.

And then the final thing I asked you to do was just to give this pivot table a name.

So let's jump across to pivot table analyze.

And in the pivot table name group, we're going to call this sales by town and cat.

And hit enter.