DataWright Information Services

Consulting and Resources for Excel and Access

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

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:


To get a detailed description of how to create a dynamic range,

. 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
  • 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

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.