Tag: dynamic range

Automatically Include New Data in a Pivot Table

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:

  1. 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.
  2. Choose Insert | Name | Define.
  3. In the Names in workbook box, type a name for the dynamic range, for example, PivotData.
  4. 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))
  5. Click the OK button.

Change the Pivot Table Source

Then, change the pivot table’s source to the dynamic range that you created:

  1. Right-click a cell in the pivot table.
  2. On the shortcut menu, click PivotTable Wizard.
  3. Click the Back button.
  4. 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.

______________