Dynamic Range for Multiple Columns with Excel OFFSET

Get FREE Advanced Excel Exercises with Solutions!

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.

excel offset dynamic range multiple columns

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

excel offset dynamic range multiple columns method 1

You can see the parameters of this formula with a brief description in the Excel sheet. The values of the Reference CellRow NumberColumn Number, etc. are mentioned separately. Here we used the COUNT function to count the number of cells in column with numbers and hence it provides the Row Height for the OFFSET 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.

excel offset dynamic range multiple columns method 1

This is possible because we set the values of Height and Width by the entire column 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.

excel offset dynamic range multiple columns method 2

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

excel offset dynamic range multiple columns method 2

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.

excel offset dynamic range multiple columns method 3

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

INDEX Dynamic Named Range for Sales

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 OFFSETINDEX, 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo