Excel OFFSET Dynamic Range Multiple Columns in Effective Way

Dynamic range expands automatically when you update the raw data to the range. It is so irritating to insert separate formula for the newly updated data, rather we may utilize dynamic range through using the OFFSET function efficiently.

In this article, I’ll discuss the ways to apply the OFFSET function in dynamic range and dynamic named range also for multiple columns. Later, I’ll show the INDEX MATCH function as an effective alternative.

Download Practice Workbook

The Basics

How to Enter a Formula in Excel

Do you know how can we insert a formula in Excel?

Entering a formula in the Excel formula bar is quite a simple task.

First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. Finally, press Enter.

Furthermore, you have to use $ (dollar sign) for using any cell or cell range as an absolute cell reference.

What is the OFFSET Function?

The OFFSET function returns a cell or cell range where the starting point, number of rows and columns, height, and width are provided.

The syntax of the function is

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

The arguments are-

reference – The starting point, given as a cell reference or range.

rows – The number of rows to offset below the starting reference.

cols – The number of columns to offset to the right of the starting reference.

height – The height in rows. It is optional.

width – The width in columns. Also, it is optional.

Excel OFFSET Dynamic Range Multiple Columns

Let’s have a glimpse at the following dataset. Here, some electronic Items are given along with sales. Also, sales are provided monthly from January to May.

Dataset for OFFSET Dynamic Range

Now, we’ll see the application of the OFFSET function in the case of dynamic range.

1. Dynamic Range Using OFFSET & COUNT Functions

If you want to calculate the total sales of all items in January and February, you can utilize the OFFSET function.

Just insert the following formula.

=SUM(OFFSET(C5,1,0,COUNT(C:C),2))

The explanation of the formula is given in the screenshot. Please try to understand that.

However, I have provided the result using the SUM function below the result by the OFFSET function.

OFFSET Function in Multiple Columns

Again, if you need to find the sales of all items in all months, just insert the formula in your excel formula bar.

=SUM(OFFSET(C5,1,0,COUNT(C:C),COUNT(6:6)))

OFFSET Function in Dynamic Range

Right now, we’ll add the sales of a month namely June for all items. If the previous formula works on the updated data, then it will be clear to us that the OFFSET function works efficiently.

Let’s check that interesting stuff.

OFFSET Function in Dynamic Range

If you look at the screenshot, the sales data is updated automatically (added the sales of Jun) without updating the OFFSET formula. But in the case of the SUM function, it remains unchanged.

2. Dynamic Named Range Using OFFSET & COUNT Functions

The dynamic named range is created by defining the name of any cell range. Later, it works for all the formulas with updating data automatically.

For creating that, open a dialog box i.e. New Name like the following by clicking

Formulas>Named Manager>Define Name

New Name for OFFSET Dynamic Range

Then type a name in the Name (also remember the name!) option, and the following formula in the Refers to option.

=OFFSET('Dynamic Named Range'!$C$5,1,0,COUNT('Dynamic Named Range'!$C$6:$C$16),COUNT('Dynamic Named Range'!$C$6:$G$6))

After creating the name and entering the formula, you’ll get

OFFSET Dynamic Range for Sales

Alternatively, you can insert the formula by clicking the lower right portion (Up Directional Key) of the above dialog box.

Accordingly, you can specify the formula in the following way instead of confusing about sheet name!

Inserting Dynamic Range Formula for OFFSET

If you follow the above in a detailed way, I can assume that you have successfully created a named range.

Next, we’ll insert the named formula for a specified range to carry out the following types of analysis.

It’s so much easier. Just insert the SUM function, and then type S as if you named the range as Sales.

While typing the S, you’ll see the option of our named formula.

Then click on the Sales.

Inserting Dynamic Named Range Formula

Similarly, we may find the average, maximum, and minimum value of the sales for all items using AVERAGE, MAXIMUM, and MINIMUM functions respectively as shown in the following illustration.

OFFSET Function in Dynamic Named Range

3. An Effective Alternative: Using INDEX MATCH for Dynamic Range

Like the first method of this article where we used the OFFSET function, we may utilize the INDEX MATCH function, a popular combination of two functions for determining the cell value for a specified range.

For example, we can calculate the sum of sales in January and February for all items. Just insert the following formula.

=SUM(INDEX(C6:G16,0,MATCH(C5,C5:G5,0)):INDEX(C6:G16,0,MATCH(D5,C5:D5,0)))

Here, C6:G16 is the cell range for the sales of all items, C5 is for the month i.e. January, C5:G5 is the cell range for five months, D5 is for the month i.e. February.

INDEX MATCH Function in Dynamic Range

More importantly, if you guys need to utilize the INDEX MATCH function for the dynamic named range, it will be more effective than the OFFSET function.

The expert says that in the case of a larger dataset, the OFFSET function performs slowly. So, you have to be aware of the alternative ways.

Am I right?

Let’s see the application.

Firstly, insert the formula in like inputting the OFFSET function as I discussed earlier elaborately.

=INDEX(Sheet7!$C$6:$G$16,0,MATCH(Sheet7!$C$5,Sheet7!$C$5:$G$5,0)):INDEX(Sheet7!$C$6:$G$16,0,MATCH(Sheet7!$D$5,Sheet7!$C$5:$G$5,0))

So, you’ll get the following dialog box.

INDEX Dynamic Named Range for Sales

Then do the analysis based on your requirement e.g. sum, average, or any other thing.

INDEX MATCH Function in Dynamic Named Range

Things to Keep in Mind

  • Be careful while inserting the formula in the Named Manager. If there is any parenthesis or referring problem, you will get a #VALUE error in doing the analysis.
  • Besides, if you don’t fix the cell value or range by using $ (dollar sign) and the formula takes the cell range out of your dataset, you will see a #REF error.

Conclusion

This is how you can perform the calculation in the case of dynamic named range through using the OFFSET and INDEX MATCH functions in excel. If you have any confusion or suggestions, please let me know in the following comments section.

Thanks for being with me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo