Excel Pivot Tables and Dynamic Range

Excel Pivot Table is a great tool for viewing and aggregating data. The data shown in a Pivot Table is taken from some range in one of the sheets of the excel file. The problem with Pivot Tables, is when you add new data. After refreshing the Pivot Table the new data is not shown. To solve this issue we can use a dynamic range. A dynamic range knows to grow automatically according to the rows we add. Suppose we have an excel file that contains 2 sheets: “data” and “pivot table”. We would like to create from the raw data in the sheet: “data” a dynamic range. This will allow us to add more rows to the “data” sheet, and just refresh the pivot table to see our new rows.
This is how our “data” sheet looks like:


And this is how our “pivot_table” sheet looks like:




In order to create a dynamic range from our raw data, we should go to the “Formulas” tab on excel and choose the “Define Name” option. A pop up window will be opened. In the window we should name the range. For example: “pivot_data_source”. In the “Refers to” field we define the dynamic range. The value that should be put there look like:

=OFFSET(data!$A$1,0,0, COUNTA(data!$A:$A), COUNTA(data!$1:$1))

Where the “data” is the name of the sheet in which the dynamic range is located.
The popup window looks like this:



After defining the dynamic data source, we should connect the pivot table with it. We do it by standing on the pivot table and selecting the “Options” tab from the main menu and choosing the “Change Data Source” option. A popup window is opened. In the field: “Table/Range” we should put the name of the dynamic range we defined: “pivot_data_source”.
The popup windows looks like:



Finally we would like the pivot table to be automatically refreshed every time our excel file is being opened. We do it by standing on the pivot table, then selecting “PivotTable Options…”. A popup window will be opened. On the “Data” tab we should check the option: “Refresh data when opening the file”. The popup window looks like:



Note that if you add new rows to the raw data, and you immediately want to see the changes in the pivot table, you should right click on the pivot table and choose the option “Refresh”. Press here to see an example of an excel file with pivot table and dynamic range defined.