Creating pivot tables with expanding ranges
When you add more rows to the data for a pivot table, it is
frustrating to have to manually adjust the pivot table’s source
range so that you can see the new data in the pivot table. Here are
three tips that let your pivot table expand to fit the available
data.
A dynamic range for a
pivot table (all versions of Excel)
Creating the range
To create a dynamic range for a pivot table, you need to use the
OFFSET function. For example, to define a range 15 columns wide on
Sheet1, starting at cell A1, you would use this formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$A),15)
To get a detailed description of how to create a dynamic range,
see
here. The main differences between a dynamic range used for
pivot tables and one used for other purposes are:
- In a pivot table, you need to include the headers. So, the
range starts at A1 instead of A2, and the height of the range is
the count of all items in column A, not the count – 1 as in the
other description. - For charting or calculations, ranges are generally one
column wide and you will create a different range for each data
series in the chart. For pivot tables, you include the full
table in the range definition and the number of columns is
always greater than 1.
Applying the named range to an existing pivot table
To get the pivot table to use the named range, you need to change
the data source for the pivot table. Here’s how:
Excel 2007
- Select any cell in the pivot table.
- In the Options ribbon, select Change Data Source.
- In the Range Finder in the dialog, type the name of the
dynamic range (eg, =PData) and press Enter.
Earlier versions
- Right-click any cell in the pivot table and select the Pivot
Table Wizard - Click the Back button, which takes you back to the data
source. - In the Range Finder, type the range name (eg, =PData) and
press Finish.
Applying the named range to a new pivot table
When you create a new pivot table you can define a range name as
the source. At the point in the Wizard where the source is
highlighted, type the name of the range (eg, =PData) and continue
with the Wizard.
Using a list (Excel 2003)
Creating the list
To create a list in Excel 2003, do the following:
- Select a single cell in the data range.
- Data > List > Create List. Excel will expand the selection
to cover the whole table, plus one blank cell.
Applying the list to an existing pivot table
You don’t need to do anything. Excel will automatically expand
the pivot table range to match the list. Once you refresh the pivot
table, new data will be incorporated
Using the list with a new pivot table
Again, no need to do anything special. Excel will select the list
range for the pivot table, and it will expand to suit the data.
Using a table (Excel
2007)
Creating the table
In Excel 2007 the List object has been replaced by a Table. You
create a named table by doing the following:
- Select any single cell in the table, or the whole table
- On the Insert tab, select Table. Excel will expand the
selection to cover the whole table. Click OK to confirm, and the
table will be formatted.
Applying the table to a new pivot table
- Select Insert > Pivot Table. Excel will use the data table’s
name as the data source. New data will be shown in the pivot
table once it is refreshed.
Applying the data table to an existing pivot table
Once the data table had been re-created Excel will recognise it
immediately. Just like lists in Excel 2003, you don’t need to do
anything extra for the pivot table to expand to fit the data.
