Dynamic Ranges in Excel
Posted by Theo Heselmans on September 22nd, 2007
A Pivot Table is usually based on a 'list' of data. This datasource has a header and below them, you got the actual data. It happens often that the data is extended by entering new data lines at the bottom. Unfortunately Excel is not intelligent enough to have the range, the pivot table is based on, be extended automatically.
But there's an easy solution. If you give the range a name, you can use that name as the source range for your pivot table.
Now the only thing we have to do, is make sure the name is dynamically defined.
Let's take a practical example:
Every time I fill the tank of my car, I add a new line to this list.
My pivot table, based on this list, should update whenever I click the red !-mark.
What you do is the following:
- Define a new name (via Insert/Name/Define) called 'Database'
- In the Refers To field, enter:
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
The formula makes 2 assumptions:
- The first column always contains a value (a date is this case) (and there is no data below the list)
- The first row always contains a header (as it should for a pivot table range)
Note that you can use the same principle for ranges you use for Look-ups or for Graphs.
That's right, add a line of data, and your graph will be updated.
Give it a try.
Category: Microsoft Excel | 
Technorati: Microsoft, Excel
Comments (6)
Thanks alot for this post ! Exactly what I was looking for...
Me again ^^.
Ehrm...do you might have a suggestion why Excel might tell me that my "Database" is an invalid reference ?
Problem solved. Maybe it's mac specific or depending on the office version but it works if you use semicolons instead of commata as you do in your article.
@Rian
The fact that you use a comma or a semicolon as a list separator, depends on your system settings.
E.g. in some european countries, the comma is already in use as a decimal seperator, so the semicolon is often used as a list separator instead.
Looking exactly for this kind of help ... but it is NOT working for me. I have tried it on Excel 2003 (SP2 AND SP3) AND 2007, but none worked. I receive always "Reverence not valid". Trying to solve this, I have even tried to use this WITHOUT OFFSET (like ="'Sheet1'!$A$1:$D$7"). The " are always added by Excel AFTER pressing "ADD" in the Name Definition ""Refers to:". Using even simple names in the definition and trying to use them in the second step of the pivot wizard gives above none valid reference feedback! Any idea, what I can do else to get this thing working? Thanks for your feedback!!!
@Ackerfrucht
The only thing that could be causing this, is that you don't use A1 references, but R1C1 references. This is an option you can set in the 'Options' dialogbox.
I've send you an email with some questions/things to try.