Dynamic Named Range – OFFSET and COUNTA

Today, we are going to learn one of the advanced and important topics of excel which is to create dynamic named ranges. Let us see what is a dynamic named range and why we need them. We would use OFFSET Function to make the Dynamic Named Range.

What are Dynamic Named Ranges?

Named Ranges are the named references to the cells or range of cells. Named ranges are useful when the range to which it references does not change. We cannot use static named ranges when we know that the range would shrink or expand with time.

Dynamic Named Range is a special kind of named range that automatically reflects the insertion or deletion of new cells into the named range whenever the user makes changes to the data. The named range automatically expands upon the insertion or deletion of values as it is dynamic.

Syntax to Make Dynamic Named Ranges

We can create Dynamic Named ranges with the help of OFFSET and the COUNTA functions of excel. The general syntax of the formula used to create a dynamic named range using these two functions is as follows.

=OFFSET(upper_left_cell,0,0,COUNTA(vertical_range),COUNTA(horizontal_range))

The OFFSET Function returns the dynamic named range. Here, the required inputs are as follows.

• upper_left_cell – This is the upper left cell of the named range referencing.
• vertical_range – This is the vertical range of cells to which the data might expand vertically (row-wise) in the future. The COUNTA Function would count the number of non-empty vertical rows from this argument that would define the dynamic height of the named range.
• horizontal_range – This is the horizontal range of cells to which the data might expand horizontally (column-wise) in the future. The COUNTA function is going to count the non-empty vertical columns and that would define the dynamic width of the named range.

We would use absolute referencing in referencing the upper_left_cell, the vertical_range, and the horizontal_range.

You can have the practice workbook below.

Making Dynamic Named Range – OFFSET Function

Let us suppose we have the following range of marks of students in a class test as follows.

We want to create a dynamic named range so that the range marks would update itself if we add or remove any new student record. The range marks would also reflect the column-wise insertion or removal of any subject’s column.

To create a dynamic named range:-

• Go to the Formulas tab on the ribbon. Click on the Name Manager button in the Defined Names group.

• Consequently, this opens the Name Manager dialog box. Click on New Button.

• Enter the Name as “Marks” and use the following OFFSET formula in the refers to the range.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$21),COUNTA(Sheet1!$A$2:$F$2))

Here Sheet1! refers to the worksheet name. Click Ok.

Explanation – In the OFFSET formula, we have specified the upper_left_cell as A2 containing the first marks entry. Thereafter, the height of the named range is also dynamic because of the COUNTA Formula. COUNTA(Sheet!$A$2:$A$21) would count the total entries in the range A2:A21. This would be the height of range marks. A2:A21 specifies the maximum limit of records that could be entered in the future. We can take a larger range for greater data set possibility.

Similarly, the COUNTA function calculates the total number of non empty columns in the dynamic named range marks. We used COUNTA(Sheet!$A$2:$F$2), which counts the columns that are filled with data and would become the width of the dynamic range.

Using Dynamic Named Range in Formulas

We have created the named range marks. Let us suppose we want to know the maximum score in the class. We would use this formula:-

=MAX(marks)

The advantage of this formula is that, when we add a column for marks of the 4th subject or we add marks of a new student, the marks range would expand itself and the MAX formula result would update if maximum marks are in the newly inserted record or column.