Article 46 – Creating Pivot Table with Dynamic Range

The file related to this article can be downloaded from Dynamic Pivot Tables

We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.

Generally in pivots, number of rows may change and columns are mostly static. In this article, we will handle both scenarios i.e. where number of rows may increase and number of columns may also increase.

Approach 1 – Full Columns Reference

Case 1 – No Increase in Columns, only in Rows – In this approach, you can select the full columns i.e. rather than making your pivot on A1:I40, make it on A:I. You will get a blank entry in the pivot table which will not look professional. The blank entry will be for the rows and may be for columns also if you have used COLUMNS area in pivot table to create the pivot table.

To remove this blank entry, apply the Label Filter in Row Labels > Does Not Equal > OK (No need to put any value as you are removing blanks)

Caution – To not uncheck (blank) in the filter box above. If you remove blanks through this method, any new category if comes as a new entry, it will not be considered. Say, North is a new Region in row 41, if you refresh, North will come as unchecked, hence will not be appearing in the pivot table.

Hence, use only Label Filter not Value Filter.

Case 2 – Even columns can also Increase – Handling this is simpler. As a best practice, always include additional columns in your table to take care of future requirements. I have created two columns Future1 and Future 2 in my Excel sheet and I have created pivot table including these 2 columns also. You can create as many columns as you want. As a thumb rule, I recommend at least 10 future columns should be created.

In future when new columns are used, they are already sitting inside the pivot table and they can be used any moment. You just need to refresh.

Approach 2 – Use Table Structured Reference

This approach takes care of increasing rows and increasing columns in one approach itself. You just need to convert your range into table.

To convert range into table – Select the range > CTRL+T

Select any cell in this table and make your pivot.

Approach 3 – Use Formula to take care of expanding range

Case 1 – No Increase in Columns, only in Rows – Formulas tab > Name Manager > Create a range with following formula

In my example, I have 9 columns, so I created the name range with following formula

=OFFSET($A$1,,,COUNTA($A:$A),9)

Create your pivot table on existing range > Right click on pivot table > Analyze tab > Change Data Source and put the Range Name in this.

You have dynamic pivot table ready.

Case 2 – Even columns can also Increase – Steps are same as in Case 1 above, the only change will be in formula

=OFFSET($A$1,,,COUNTA($A:$A),COUNTA($1:$1))