If your pivot table is based on Excel data, you probably add new records to that data, on a regular basis. You want the pivot table to automatically include those new records, without you having to manually change the pivot table range every time you add new data.
A range that expands automatically is called a dynamic range, and here are a couple of ways to create one in Excel.
Create a Named Table
In Excel 2010 and 2007, you can format a list as a Named Table, and use that as a dynamic source for your Pivot Table. There are instructions here: Excel Tables — Creating an Excel Table. This is a quick and easy way to create a dynamic range.
Create a Dynamic Range With a Formula
In Excel 2007, and earlier versions, you can use a formula to create a dynamic range, which will automatically expand to include any 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.
______________
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.
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.
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.
Hi
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
I’m using this as my dynamic range definition:
=OFFSET(‘MainTable’!$A$2,0,0,COUNTA(‘MainTable’!$A:$A),COUNTA(‘MainTable’!$2:$2))
The range name I use for this range is Database. When I create a pivot table on the same worksheet using Database as the source, everything works fine. But when I try to create a pivot table on another worksheet, I get the error, “Reference is not valid” in the wizard after I enter Database as the source range. I have followed all of your rules about setting up the table. The only thing unusual is that the table starts in A2 rather than A1, but I don’t have any blanks in the headers or in the first column, and I have nothing in the white space around the table. I have Excel 2002. Any ideas?
Mack, try naming the range something other than Database, such as PivotData. Does that solve the problem?
Thanks for your prompt reply! I decided to put the following code in the Activate macro for the sheet where the pivot table is located:
‘Reset the address of the DatabaseCR range so that it is the same as the ‘address of the Database range (the dynamic range)
Worksheets(“Main Table”).Range(Worksheets(“Main Table”).Range(“Database”).Address).Name _
= “DatabaseCR”
This seems to be working fine. I now use DatabaseCR as the source range of the pivot table.
Hello,
Could anyone help me with this:
I have a data sheet with columm headings on which I base my pivot table.
The formula to define my dynamic range is the following:
= VERSCHUIVING(data!$a$1;0;0;AANTALARG(data!$a:$a); AANTALARG(data!$1:$1)-1)
When I do F5, I do get the data expected (I presume I should have the colum headings included in the range)
My pivot table uses this dynamic range/name as a source “=database”
Then I do get an answer: reference invalid.
Thank you for your help !
Hi Debra, I have tried =OFFSET(Product_Extraction.xls!$A$1,0,0,COUNTA(Product_Extraction.xls!$A:$A),30)
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’. Can you please help me?
thanks,
Sisca
Hi Sisca,
Is the Product_Extraction workbook open? Are there 30 columns in the source data, with no empty cells in the heading row?