Updating Your Pivot Table

Automatically update your Pivot Table 

Whenever I start showing people Pivot Tables, one of the first questions asked is: “What happens when additional data is added to the source data?” 
First, changes to the existing data are automatically updated when you select the REFRESH button  located on the contextual Options tab.

If you add additional rows of data, Excel does not automatically add that information to the Pivot Table.

There are two ways to get Excel to automatically update the Pivot Table for any additional data.

ÜStarting with Excel 2010  you can turn your data into a table. If you do this, then Excel will automatically update the Pivot Table. This is by far the easiest way to do it.

Create a Table

Turn your data list into a table and it displays alternating row shading and AutoFilter drop down boxes.

1.      Click in the data.

2.      Click Insert tab.

3.      Click Table.

4.      Verify the data range and click OK.

That is it! Excel now considers your data to be a table and it will incorporate new rows and columns of information as  they are added.  If you open your Pivot Table and re-select your data source, the Pivot Table will now update as the range changes.  As data is added it will automatically be incorporated into the parameters of the original source data.

If you don’t control the data, then you may not be able to turn it into a table. In that case, the process is a bit more involved as you need to create a dynamic range, but it is definitely worth the effort.

Create a Dynamic Range

1.      Click in the source data.

2.      Select the Formulas tab and click on Define Name.

3.      In the New Name dialog box, name your data. In this example, I named it sales_data.

4.      The scope should stay at the default level of Workbook.

5.      In the Refers to: section type the following:

6.      =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

7.      Click OK.

What we have created  is  a dynamic range called sales_data. The OFFSET formula is telling Excel that on Sheet1, look at cell A1,  count how many cells are in Column A, and then it  counts how many cells are in row 1, so it will count and include new data as it is added.

Warning:  Make sure there is no other data below your dynamic range. If there is, this will not work.