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.
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.
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.
