Dynamic Ranges in Pivot Tables

1st way is to change the reference range the pivot-table is assigned to - use the Wizard - go back and enter the new range of data - press finish - new data inserted

2nd way - more sophisticated, since automatically - use a named range:

Use a Dynamic Data Source You can use a dynamic formula to define the source range for a Pivot Table. As new items are added to the table, the named range will automatically expand. 1. Name the Range Choose Insert>Name>Define Type a name for the range, e.g. Database In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in a column that doesn't contain any blank cells. , e.g.: =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) In this example, the list is on a sheet named 'Data', starting in cell A1. The arguments used in this Offset function are: Reference cell: Data!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Data!$A:$A) Number of Columns: 7 Note: for a dynamic number of columns, replace the 7 with: COUNTA(Data!$1:$1) Click OK

2. Base the Pivot Table on the Named Range Select a cell in the database Choose Data>PivotTable and PivotChart Report Select 'Microsoft Excel List or Database', click Next. For the range, type your range name, e.g. Database Click Next Click the Layout button Drag field buttons to the row, column and data areas Click OK, click Finish