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