All language subtitles for 4. Updating PivotTable Data

In the final lesson of this section, I'm going to show you how quick and easy it is to update your

pivot table data.

Now, before we get onto that, I've just made one minor change to my workbook to keep in line with

those golden rules of good spreadsheet design.

I've gone through and I've color coded all of my worksheets, so now I have a clear indication as to

which worksheet contains my source data, which ones contain pivot tables and pivot charts, and then

which ones contain my report connections and my filters.

Now the source data that I have on the Pivot Tables tab, that's the source data we've been using to

create our pivot tables and pivot charts.

And if you take a look in Column K, you can see that my source data relates to 2018 and 2019.

Now, most times, data doesn't stay static, and it might be that now we have 2020 information to add

into the spreadsheet.

And what I want to happen is that when I add the data for 2020, I can click one button and it's going

to update all of my pivot tables and all of my pivot charts.

And this is why it is so important to put your data into a table.

What you'll see in a moment is that when we add new data because this data is in an Excel table, excel

tables also expand to accommodate that new data.

And then it makes it really simple to update any tables or charts that are using that dataset.

Now, let's not get too far ahead of ourselves.

Let's move across to this last tab court more data now on this worksheet.

I have pretty much the same data.

I have the same column headings, but take a look at the date column and the year column.

This is data for 2020, and I want to add this data into my original data source and then have all of

the pivot tables and pivot charts update.

So this is a really simple process or we need to do is press control a to select all of our data control

C to copy that data.

Let's go back to our original data set.

And what I'm going to do here is just jump down to the very last row control down Arrow.

Let's click in that first blank row and control V to paste that new data in now.

Because we have our data in an Excel table, it's expanded to accommodate that data.

And now the new data is part of the product sales table range.

So now if we jump across to our first pivot table, if I click on it and got to pivot to analyze, all

I need to do is click the Refresh button just here.

Notice there is a keyboard shortcut of Alt Plus F5 five, so I'm going to click the dropdown and choose

to refresh or notice that all of these figures have now updated.

And if I take a look at my slices and move this out a little bit, I should have another slice of there

for 2020, and I can see there it is at the bottom.

I need to put this on the same row.

So let's change the columns here to three to accommodate that.

Yeah, and now I can filter by 2020, 2019 and 2018.

Let's go to our next chart.

I can see that there is all of my 2020 data.

Let's go to the map charts.

Now, we don't actually have a filter on this data.

So let's go up to the pivot table, analyze ribbon and insert a slicer for the year.

And there we go.

I have my three years 2018, 2019 and 2020.

If we go to the report connections page automatically, I have a new 2020 entry, but I do need to modify

the way that I'm viewing this slice that we want to show three columns and not to drag this out a little

bit and now I can filter by 2020 as well.

So the golden rule here really harks back to one of the first things we did in this section.

Always put your Excel data that you're going to analyze in a table because it makes it a lot easier

when you want to update all of your pivot tables and charts when you add new data.