If your pivot table is based on Excel data, you probably add new records to that data, on a regular basis. You want the pivot table to automatically include those new records, without you having to manually change the pivot table range every time you add new data.
A range that expands automatically is called a dynamic range, and here are a couple of ways to create one in Excel.
Create a Named Table
In Excel 2010 and 2007, you can format a list as a Named Table, and use that as a dynamic source for your Pivot Table. There are instructions here: Excel Tables — Creating an Excel Table. This is a quick and easy way to create a dynamic range.
Create a Dynamic Range With a Formula
In Excel 2007, and earlier versions, you can use a formula to create a dynamic range, which will automatically expand to include any new rows and columns. Follow these steps to create a dynamic range:
- Select the top-left cell in the source table. This step isn’t necessary, but helps you by inserting the cell reference in the name definition.
- Choose Insert | Name | Define.
- In the Names in workbook box, type a name for the dynamic range, for example, PivotData.
- In the Refers to box, type an OFFSET formula that refers to the selected cell. For example, with cell A1 selected on a worksheet named Pivot, you’d type (all on one line)
=OFFSET(Pivot!$A$1,0,0,
COUNTA(Pivot!$A:$A),
COUNTA(Pivot!$1:$1)) - Click the OK button.
Change the Pivot Table Source
Then, change the pivot table’s source to the dynamic range that you created:
- Right-click a cell in the pivot table.
- On the shortcut menu, click PivotTable Wizard.
- Click the Back button.
- In the Range box, type the name of the dynamic range, and click Finish.
Note
This technique will not work if there are other items in row 1 or column A of the Pivot worksheet. Those items would be included in the count, and would falsely increase the size of the source range.
______________