How to use OFFSET function for Dynamic Range in Excel

This video tutorial shows "How to use OFFSET function to get dynamic range in Excel" with the help of examples.

The Microsoft Excel OFFSET function returns a reference or specific range of cells that is offset from a given reference by a specified number of rows and columns supplied in the arguments. It can also be used to create the dynamic range for tables, pivot tables, charts etc. OFFSET function usually is used with other functions as customize functions, expecting a reference argument in excel. It is a “volatile” function, and it gets recalculated whenever there is any change to a worksheet.

We have created this tutorial using following features and :

1. How to move rows (up or down) from starting point / reference?
2. How to move columns (left or right) from starting point / reference?
3. How to use Height / Width to get the dynamic range?
4. SUM with OFFSET
5. Learn more about #REF! error

[gopalakrishnarao]

Thanks for uploading an useful technique.

[ravihoratti]

Hello Sir, The last example where Offset was referring to negative column, how to handle it in the Macro? I'm stuck at this point in one of my Macro. How to get rid of it? Any response is greatly appreciated!

[CarlosMartinDelosSantosNg]

I am using your formula and it worked wonderfully, but I need to use it on vba and I can't get it to work.

w = Range("NamedRange").Max(Offset(9, 3, 12, 1)).Value

Does that even exist for vba?

[rraushan]

Hi sir I hv a query...how can I find the value address if data in more then one columns....help me pls ...note columns criteria is 100 or more than 500 please help me...

[nagasatish]

Sir, How to use OFFSET with CORREL formula

[Corey_Bee]

Don't use the OFFSET function. Volatile functions should never be used. You can do the same thing with INDEX with far fewer performance issues.