Dynamic Range for Multiple Columns with Excel OFFSET

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

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.

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.

• In addition, if you need to find the sales of all items in all months, just insert the formula in your Excel formula bar.

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

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

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.

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.

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