Using a Dynamic Range as the Data Source for a Pivot Table

A colleague recently approached me with a spreadsheet problem. He reports on ever-changing data using Microsoft Excel’s pivot tables. As his base data grows in volume day after day, he needed a smart way to ensure that the new data is picked up by his pivot table automatically when he refreshes the table without having to go back to amend his pivot table data source.

This is a common issue among Microsoft Excel users regardless of the version they use. The good news is that there are solutions available. The requirement is to base the pivot table on a so-called dynamic range. This dynamic range can be an Excel table or it could be a qualifying range that has been defined as a dynamic range. A ‘qualifying’ range in a pivot table scenario means a box-shaped range consisting of rows and columns but crucially, all the columns in the range should have headings and are not blank.

To create a dynamic range manually you will need to go through the following procedure:

1. Go to Formulas > Defined Names > Define Name;
2. In the dialogue that follows, type in a Name for your range such as DataForPivotTable. Note that a valid Name cannot have spaces, can begin with a letter or a number, can contain period or underscore characters but cannot exceed 255 characters. Good practice dictates that the name you choose is meaningful for the purpose at hand;
3. If you wish to be able to directly access the Name anywhere i.e., on any worksheet in your workbook, set the scope of your Name to Workbook. That way you’ll simply be able to access your Name by just typing =DataForPivotTable rather than something like =Sheet1!DataForPivotTable;
4. The next step is about setting the reference for the Name. This is the most important step of all and it involves the application of two formulas nested together to return the desired dynamic range.
5. The first formula to use is the OFFSET formula. In Microsoft Excel this formula returns a reference to a range of a certain size (in columns and rows) but is defined with reference to a starting point or base. The OFFSET formula has the following arguments:

• Base reference;

• Number of rows to offset (move away) from the base reference. In other words, the distance between the base range and the resulting range as measured in rows;
• Number of columns to offset (move away) from the base reference. Thus, the distance between the base range and the resulting range as measured in columns;
• Number of rows for the resultant range; and
• Number of columns for the resultant range.

In practice the formula will be laid out as follows:

=OFFSET(BaseReference,RowsToOffset,ColumnsToOffset,SizeInRows,SizeInColumns)

e.g. =OFFSET($A$1,0,0,COUNTA($A:$A),10) as discussed below

There are a few points to note about what is acceptable for each of the arguments in the OFFSET formula. These are summarised below:

• BaseReference argument, must be a reference to a cell, range of adjacent cells. For example A10, B5:D10 or a valid named range.
• The RowsToOffset and ColumnsToOffset arguments can either be positive or negative numbers; but
• The SizeInRows and SizeInColumns arguments can only be positive numbers as a range's dimensions can only be positive. Additionally, these two arguments are optional and if omitted, the resulting range will be of the same size as the Base range. Therefore, as we are going to create an elastic range capable of expanding and contracting, we will specify some values for these arguments.

6. The second formula that can be used in the creation of a dynamic range is the COUNTA formula which counts and returns the number of non-blank cells in a range. It can be laid out simply as follows:

=COUNTA(RangeOfCellsToExamine) e.g. COUNTA(A1:A25) would return a number of non-empty cells in the 25 cells between cell A1 and cell A25.

Now, returning to my colleague’s problem. He has a block of data which feeds his pivot table report but as the number of rows increases, his pivot table should be capable of updating its data source automatically. How does he resolve this?

Let us assume that at the time he decides to create a dynamic range his original data is located in a worksheet in the range A1:J50, that is, 50 rows and 10 columns worth of data. We shall also assume that there is no additional data in this worksheet anywhere below that initial data block. Each passing day, the number of rows or records in his data increases as new records are added on.

To create his dynamic range, having opened the Define Name Box as described above, he would need to enter the following in the Refers to box: =OFFSET($A$1,0,0,COUNTA($A:$A),10)

1. In this case, our BaseReference is set to $A$1, the top left corner of our data block;
2. The RowsToOffset and ColumnsToOffset arguments are each set to 0 (zero) because we want the resultant range as defined in our dynamic range to start from the same point;
3. The SizeInRows is defined by using the COUNTA formula. Because there is no data below our data block, counting the non-blank cells in the entire column A returns the number of rows that our data block occupies.

•  NOTE: I have chosen to use column A for my COUNTA on the assumption that all of the cells within it have some data (not blank) otherwise the COUNTA will give a smaller number than what is required. In using the COUNTA as described in this article therefore, feel free to choose any other column that contains a full set of data.
• The COUNTA holds the key to the dynamic range because it enables the resultant range to self-adjust as the number of rows increases or decreases;

1. The SizeInColumns in this example has been fixed at 10 – the original 10 columns which are not expected to change. In other words, all of the data in these 10 columns will be used by our pivot table. Again please feel free to change this number to the number of columns represented in your data set.
2. IMPORTANT: Do not forget to insert the dollar ($) signs as appropriate to ensure that each time the Name is being referenced, it uses the same (absolute) starting point and the COUNTA also counts items from the same column. The two types of referencing available - absolute and relative - I have also found to be a common area of confusion and could be covered here in future.

After the new dynamic range Name has been created, the final step is to insert it as the data source in the pivot table settings. Whether you are creating the pivot table from scratch (Insert > Tables > Pivot Table) or you already have the pivot table but just need to amend the data source (Pivot Table Tools > Data > Change Data Source), you'd type the Name you created above in the Table/Range field as =DataForPivotTable (including the equal sign).

At the end of the day, my colleague had a handy, smart solution that he promised to explore further and implement more extensively to save him valuable time. I mentioned to him that this was just the beginning of the journey as dynamic or elastic ranges could be applied and constructed in many other ways. A bit of creativity with Microsoft Excel will almost always result in some really fascinating solutions to common issues and problems when working with spreadsheets.

If this article has helped you too, please like it below or if you wish to add something I would also be delighted to hear from you.