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:
Image:Dynamic Ranges in Excel
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))
Now use 'Database' as the source for your new (or existing) pivot table.
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)
That's it. Let your data grow, and your range-definition will grow with it.
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  | TechnoratiTechnorati: ,