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.

**Table of Contents**hide

## 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.

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.

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)))`

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.

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**

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

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!

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**.

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.

### 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.

**Read more: **Excel Dynamic Range Based on Cell Value

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.

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.

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

## 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.