Automatically Include New Data in a Pivot Table

If your pivot table is based on an Excel list, and you probably add records to the existing source data table. You’d like the source data range to automatically expand to include any new rows and columns. You don’t want to manually change the pivot table range every time you add new data.

Create a Dynamic Range

If you frequently add new data to the pivot table source data, you can use a dynamic range, which will automatically expand to include the new rows and columns. Follow these steps to create a dynamic range:

Select the top-left cell in the source table. This step isn’t necessary, but helps you by inserting the cell reference in the name definition.
Choose Insert | Name | Define.
In the Names in workbook box, type a name for the dynamic range, for example, PivotData.
In the Refers to box, type an OFFSET formula that refers to the selected cell. For example, with cell A1 selected on a worksheet named Pivot, you’d type (all on one line)
=OFFSET(Pivot!$A$1,0,0,
COUNTA(Pivot!$A:$A),
COUNTA(Pivot!$1:$1))
Click the OK button.

Change the Pivot Table Source

Then, change the pivot table’s source to the dynamic range that you created:

Right-click a cell in the pivot table.
On the shortcut menu, click PivotTable Wizard.
Click the Back button.
In the Range box, type the name of the dynamic range, and click Finish.

Note

This technique will not work if there are other items in row 1 or column A of the Pivot worksheet. Those items would be included in the count, and would falsely increase the size of the source range.

Technorati Tags: dynamic range

Mike Burton
August 1st, 2009 at 3:10 am

Hi, I have tried
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),144) as a dynamic range for a pivot table but for some reason it does not work.
I define the name but when creating the pivot table I get

‘reference is not valid’

I can not see the name I have created in the Name box list but I can see it from the tool bar option (Insert/Name/Define)?
Is this because I am using excel 2003? Any help would be greatly appreciated.

Debra Dalgleish
August 3rd, 2009 at 9:37 am

Mike, the dynamic named ranges don’t show up in the Name box, in any version, so that’s okay.

You entered 144 as the number of columns. Is there a heading in all 144 of those columns? That might be the problem.

Mike Burton
August 4th, 2009 at 1:10 am

Hi Debra,
thanks for your quick response. All columns have headings but I have figured it out now. I had to enter the formula as=OFFSET(’Sheet1′!…. and now it recognises the function. Very useful piece of code. Thanks for you help.

Mr CheeZe
August 31st, 2009 at 6:00 am

I have the opposite problem I think in Excel2003

I have a pivot table that needs the raw data changing on a regular basis (i’m trying to make it simple for other ‘less able’ users)
What happens is that I have a ‘week number’ column in the pivot and is populated with a ‘minutes’ figure.
However, if there are no minutes for a particular week in the raw data, the pivot auto formats and ‘loses’ this ‘week number’ as shown below

example 1(good);
week number 31 32 33 34 35 36
minutes 280 158 45 178 345 248

example 2 (lost weeks);
week number 31 33 34 36
minutes 280 45 178 248

What i would ideally like is the ‘blank’ weeks to remain in my pivot table, as i need to ‘getpivotdata’ on another worksheet

desired example;
week number 31 32 33 34 35 36
minutes 280 0 45 178 0 248

can anyone please help?

many thanks for your help

Mr CheeZe

Categories

Archives