Dynamic Ranges with OFFSET and COUNTA Functions in Excel

Looking for ways to create dynamic range by applying OFFSET and COUNTA functions in Excel? Don’t worry! You have landed in the right place. In this article, I will show you how to create dynamic ranges OFFSET function and COUNTA function in Excel.


Download Practice Workbook

You can download the practice book from the link below.


Introduction to OFFSET and COUNTA Functions

The OFFSET function returns a reference to a range that is a given number of rows & columns from a given reference.

The syntax of the OFFSET function is given below:

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 given:

COUNTA(value1, [value2],…)

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


2 Ways to Create Dynamic Range by Using OFFSET and COUNTA Functions in Excel

Let’s say, we got a dataset of some information about Car (i.e. Car Type, Model, Year, VIN). The dataset includes information about 13 types of cars and row 5 to 17 describes them.

We want to make the data range dynamic so that any newer input changes the range. For this, we will apply the OFFSET and the COUNTIF functions.

In this section, you will find 2 effective ways to create dynamic ranges with OFFSET and COUNTIF functions in Excel. I will demonstrate them one by one here. Let’s check them now!


1. Creating Dynamic Range with OFFSET and COUNTA Functions

Let’s make our concerning dataset dynamic with the relevant functions. After making it dynamic, we will check whether any newer input in the data range changes the table or not.

Just proceed with the following steps.

Steps:

  • First of all, select a cell where you want to re-input the dynamic data. The static data will be converted to a dynamic state after applying the relevant Excel function in it. Now, type the formula below in the selected cell.

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

Here,

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

Create Dynamic Range with OFFSET and COUNTA Function in Excel

  • Then, press ENTER and a new data range will be shown as per your arguments application. Thes newly shown data range is dynamic in nature.

🔏Formula Unlocking

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

In the COUNTA(B5:B50) section, we have taken the cell range from B5:B50 so that while making it dynamic, it can count up to row 50. So here, the cell range is B5:B50 but here the non-empty cells are only 15. So, this section 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)

Here, the function takes cell B5 as a reference cell 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. So, the function returns the offset data in cell G5.

Create Dynamic Range with OFFSET and COUNTA Function in Excel

Now, let’s check whether it has been converted to the Dynamic range or not. Enter a new value (i.e. Latex) in the Main Data in cell B19. Don’t input anything else. As soon as you entered the new value, the data of the Offset Result will show 0 for the cells (i.e. H19, I19, J19) in which you didn’t enter any value. The auto change in these cells indicates that the range is now converted to a Dynamic state.


2. Using Name Manager with OFFSET and COUNTA Functions

Now, we will make this data dynamic by applying another command: “Name Manager”. The corresponding functions will also also be used along with the Name Manager command. In order to implement this task, proceed in the steps below.

Steps:

  • First, select the first cell of this data range, go to the Formulas tab> click the Name Manager option under the Define Names group.

Name Manager with OFFSET and COUNTA Function in Excel

  • Then, a dialog box with the heading Name Manager will appear in the window. Click the New field in the dialog box.

Name Manager with OFFSET and COUNTA Function in Excel

  • After that, the New Name dialog box will show up. Assign a name in the Name field and enter the following formula in the Refers to field.

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

Here, Sheet5 is the worksheet in which we performed the task.

  • Click OK.

  • Now, Excel will return you back to the Name Manager command box. Close the box and switch to the Excel worksheet window.

  • Here, select a cell where you want to paste the Named Range you have you have created a few times ago. Just start typing the name of the range (i.e. Car_info) and Excel will suggest you the Named Range. Double-click on the range

  • Hence, you get the Named Range in the selected cell and at the same time the range has been made dynamic.

Name Manager with OFFSET and COUNTA Function in Excel

Check this like the previous method whether it is dynamic or not.


Use of Excel Dynamic Ranges for Calculation

We can use the dynamic ranges made with OFFSET and COUNTA functions for calculations. Let’s deal with the dataset below. In column D, the Price of the Car is stated. Make the data range dynamic just like Method 1.

We will calculate the Total Price, Average Price, Maximum Price, and  Minimum Price from this dynamic range. So, let’s start the process like the one below.

Steps:

  • First of all, let’s calculate the Total Price of the dynamic range. Apply the SUM function for this.

=SUM(J5:J50)

Here, the range of summing is considered as J5:J50 so that it can calculate the sum up to cell J50 from the dynamic range after entering values in it. You will get the total value of the dynamic range J5:J50.

Use of OFFSET and COUNTA Function in Excel

  • Similarly, apply AVERAGE, MAX, and MIN functions to calculate relevant values.

Now, let’s check whether the calculated values change with newer inputs. Enter a new Car information whose price is $376700. It is the highest price in the range.  Hence, you will notice that the Maximum Price as well as all other prices has been changed now.


Practice Section

Here, I am giving you a practice section so that you can practice yourself.


Conclusion

In this article, I have tried to show you some methods to create a dynamic range with OFFSET and COUNTA function in Excel and some uses of it. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo