What afe dynamic ranges? These are cell ranges in Excel that are automatically adjusted when data is added (or removed). This is very useful because then the ranges in formulas no longer have to be adjusted when data is added or removed. This article will explain how to create these dynamic ranges. Two techniques are discussed. The first technique with the OFFSET function can be applied in all Excel versions and the second technique with tables, which is much simpler, from Excel version 2007.
Dynamic range with the OFFSET function
A dynamic range can be created with the help of the OFFSET function. As of Excel version 2007 there is another easier way. This will be discussed in the next section. The explanation is based on the example below:
The total turnover can of course easily be calculated with = SUM (B2: B9). But every time a value is added, this formula has to be adjusted. With the formula below, the range of this formula can be made dynamic:
=SUM(OFFSET(B2,0,0,AANTAL(B:B),1))The OFFSET function can be used to move a cell or range of cells and or to set the size of a range of cells. The result of this function is a reference. Arguments 2 and 3 are used for moving (number of rows and number of columns). That is not the case here, which is why a zero has been entered twice. The last 2 arguments of this function are used to set the height and width of the range. The height is set here with the COUNT(B:B) function, counting the number of numbers in column B and the width set to 1. In this way, the range always exactly matches the number of numbers in column B and is thus made dynamic.
Even nicer and more convenient is to give a name to the dynamic range. This makes the formulas in the worksheet shorter and much more legible. This name can be set in the formulas tab → manage names (for Excel 2003 choose Insert → Name). Then choose new and another form will appear:
Name the range sales and fill in the formula displayed at "Refers to". Please note that all references must be made absolute and therefore must be provided with dollar signs. Afterwards, this range can be used in the formulas in the worksheet. For example: =SUM(turnover)
Dynamic range with tables
Starting with Excel version 2007, it has become much easier to set up dynamic ranges. The method from the previous section also works, but it is easier to work with tables. Therefore, the entered data must first be converted into a table. Select all data including the column headers and choose Table at the Insert tab:
Press OK and the chosen range is automatically changed to a table. You can see that the lines are colored alternately and that the column headings have filter buttons:
A big advantage of tables is that they are automatically expanded when data is added to them. For example, type a new date in A11 and you will see that this line automatically becomes part of the table. When inserting the table, it is automatically given a name. This can easily be changed by changing the name of the table name (outlined in red). Change this name to a relevant name, for example to sales.
Finally, based on this table, a formula can be made with the sum of the turnover. That can be done in different ways. An easy way is to first enter = SUM( and then select the relevant numbers from the table (so from B2 to the end of the table). Then close with a ) and the formula looks like this: = SUM(sales [turnover]). The reference to the table is therefore table name followed by the column name in square brackets.
This is a dynamic range, because by adding a new line to the table, the table will be automatically expanded and the sum will be automatically adjusted accordingly.
Questions / suggestions
Hopefully this article helped you to create dynamic ranges in Excel. If you have any questions about this topic or suggestions for improvement, please post a comment below.
