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 also for multiple columns. Later, I’ll show the** INDEX** and **MATCH** functions as effective alternatives.

**Table of Contents**hide

## Download Practice Workbook

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

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

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 (4 Ways)**

**Similar Readings**

### 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: Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)**

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

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

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

**Read More: Excel VBA: Dynamic Range Based on Cell Value (3 Methods)**

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

In the end, we can consider that you will learn how to use 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.