Creating Dynamic Ranges with the OFFSET and the COUNTA Functions in Excel

Introduction to OFFSET and COUNTA Functions

The OFFSET function returns a reference to a range.

The syntax of the OFFSET function is:

OFFSET(reference, rows, cols, [height], [width])

The COUNTA function counts the number of empty cells in a given range, The syntax of the COUNTA function is:

COUNTA(value1, [value2],…)

We will implement these two functions to create dynamic range in Excel.

The dataset showcases. Car Type, Model, Year, VIN.

Method 1- Creating a Dynamic Range with the OFFSET and the COUNTA Functions

Steps:

Select a cell to re-input the dynamic data. (static data will be converted into a dynamic state after applying the Excel function)
Enter the formula below in the selected cell.

=OFFSET(B5,0,0,COUNTA(B5:B50),COUNTA(B5:E5))

B5 = Reference (based on which the offset parameters are applied)
0 = Row number counted from the reference cell
0 = Column number counted from the reference cell
COUNTA(B5:B50) = Height (Number of rows that will be returned as resultant values)
COUNTA(B5:E5) = Width (Number of columns that will be returned as resultant values)

Press ENTER and a new data range will be shown.

Formula Breakdown

The COUNTA function returns the number of non-empty cells in a selected data range.

COUNTA(B5:B50) takes B5:B50 and makes it dynamic: it can count up to row 50.The non-empty cells are only 15. It returns 15.

Similarly, COUNTA(B5:E5) returns 4.

So, OFFSET(B5,0,0,COUNTA(B5:B50),COUNTA(B5:E5)) = OFFSET(B5,0,0,15,4)

takes B5 as a reference and offset O rows and O columns from it. The height (Row) of the offset data will be 15 and the width (Column) is 4. The function returns the offset data in G5.

Enter a new value (Latex) in the Main Data in B19.

The Offset Result will show 0 for the cells (H19, I19, J19) in which you didn’t enter any value.

Method 2 – Using a Name Manager with the OFFSET and the COUNTA Functions

Steps:

Select the first cell of this data range, go to the Formulas tab> click Name Manager in Define Names.

In Name Manager, click New.

In New Name, enter a name in Name.
Enter the following formula in Refers to.

=OFFSET(Sheet5!$B$5,0,0,COUNTA(Sheet5!$B$5:$B$50),COUNTA(Sheet5!$B$5:$E$5))

Sheet5 is the worksheet in which the task is performed.

Click OK.

In Name Manager, close the box and go to the Excel worksheet.

Select a cell to paste the Named Range.
Start typing the name of the range (Car_info) and Excel will suggest you the Named Range.
Double-click the range

You get the Named Range in the selected cell.

Check whether the dataset is dynamic by entering new data.

Using Excel Dynamic Ranges to perform Calculations

In column D, the Price of the Car is stated. Make the data range dynamic, like in Method 1.

Calculate the Total Price, Average Price, Maximum Price, and Minimum Price in this dynamic range.

Steps:

Calculate the Total Price of the dynamic range. Apply the SUM function:

=SUM(J5:J50)

J5:J50 is the summing range

Apply the AVERAGE, MAX, and MIN functions.

Enter new Car information whose price is $376700. It is the highest price in the range: The Maximum Price as well as all other prices changed.