The article will show you how to use **OFFSET** Dynamic Range forÂ multiple columnsÂ in Excel in effective ways. Dynamic range expands automatically when you update the raw data to the range. It is so irritating to insert separate formulas for the newly updated data, rather we may utilize dynamic range by 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 for multiple columns. Later, Iâ€™ll show the** INDEX** and **MATCH** functions as effective alternatives.

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

## Create Dynamic Range for Multiple Columns Using Excel OFFSET: 2 Ways

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 and COUNT Functions

If you want to calculate the total sales of all items in January and February, you can utilize the **OFFSET function.** Let’s have a look at the description below.

**Steps:**

- First, select a cell to store the total amount of
**Sales**and insert the following formula cell**D18**.

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

You can see the parameters of this formula with a brief description in the Excel sheet. The values of the **Reference Cell**,Â **Row Number**,Â **Column Number, **etc. are mentioned separately. Here we usedÂ **the COUNT functionÂ **to count the number of cells in column **CÂ **with numbers and hence it provides theÂ **Row HeightÂ **forÂ **the OFFSET function**.

- Also, I have provided the result using
**the SUM function**.

`=SUM(C6:D16)`

- In addition, 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)))`

- Next, 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.

This is possible because we set the values of **HeightÂ **andÂ **WidthÂ **by the entire columnÂ **CÂ **and rowÂ **6**.

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.

**Read More: **Create Dynamic Sum Range Based on Cell Value in Excel

### 2. Dynamic Named Range Using Excel OFFSET and COUNT Functions

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

**Steps:**

- First, open a dialog box i.e.
**New Name**like the following by clicking**Formulas**>>**Named Manager**>>**Define Name**.

- After that, type a name in the
**Name**(also remember the name!) option, and the following formula in the**Refers to**section.

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

- Thereafter. a named range for the range reference of the
**OFFSET function**is created. - 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 being confused about the sheet name!

If you follow the above instructions, 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. Select a cell to store theÂ
**SalesÂ**of the entire month and type the following formula in it. - Later, hitÂ
**ENTER**. This command will show you the total amount ofÂ**SalesÂ**in that range.

`=SUM(Sales)`

Similarly, we can find the average, maximum, and minimum value of the sales for all items using the **AVERAGE**, **MAX**, and **MIN**Â functions respectively as shown in the following illustration.

**Read More:Â **OFFSET Function to Create & Use Dynamic Range in Excel

## An Effective Alternative: Using INDEX and 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.

**Steps:**

- First, calculate the sum of
**sales**in**January**and**February**for all items. For that reason, 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 alternative ways.

Letâ€™s see the application.

- Firstly, insert the formula 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.

**Read More:Â **How to Create Dynamic Range Using Excel INDEX Function

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

**Download Practice Workbook**

## Conclusion

In the end, we can consider that you will learn how to use the Excel **OFFSET **function for **dynamic range **in multiple columns. This is how you can perform the calculation in the case of dynamic named range by using the **OFFSET**,Â **INDEX**, and** 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.

## Further Readings

- Data Validation Drop Down List with Excel Table Dynamic Range
- How to Create a Range of Numbers in Excel
- Dynamic Named Range Based on Cell Value in Excel

**<< Go Back to Dynamic Range | Named Range | Excel FormulasÂ | Learn Excel**