Excel OFFSET function basics + Dynamic Ranges | 5 Examples

In this video we show the excel OFFSET function basics and 5 practical examples.

Download Example Workbook here:

How to use INDEX & MATCH:

Creating Dynamic ranges is a skill that can completely transform the way you use Microsoft Excel (It happened to me!)

In this video tutorial, we illustrate the OFFSET function basics and we show how you can create dynamic ranges with 5 examples:

1- First of all, We show the basics of the OFFSET function and how to select a cell's value using the OFFSET function.
 Secondly, we show how you can create dynamically expanding and shrinking range using OFFSET.
In the third example, we show you how you can dynamically select the values of the last 3 months of Sales in a list of months and sales values.
In the fourth example, we show you how you can display the sum of Sales starting from a certain month that you select from a drop-down menu.
Lastly, we show how you can create a dynamically expanding and shrinking drop-down menu with the help of a mini Pivot table!
This tutorial is about 30 mins, and it's packed full of useful information!

DON'T FORGET TO LIKE, SHARE AND SUBSCRIBE TO THE CHANNEL FOR MORE VIDEOS!

Follow us on Social Media:

Subscribe to my Youtube Channel:

#offset #dynamicrange #exceltricks

[ExcelBonanza]

Let me know what you think about the Video.
How are you going to use Dynamic ranges in your Excel workbooks? Let me know what you are going to do with that skill below in the comments!
If there's a certain topic you need me to cover, let me know on the comments below as well!

[David-tgku]

In my opinion the best explanation of the Offset function on YouTube.

[marcw.]

Most dont know how to use this - good for you - good video. I have used this for years in combination with MATCH and can lookup data on a web page or in a document based on a single set of TEXT, which is so much easier than INDEX. We even use SEARCH at times which gets complicated but has many benefits. We pick out any text from any paragraph, or values from any table in an HTML or PDF etc.
A great scraping tool.

[debasish.d]

Very Well explained.
I was struggling with the concept of dynamic named ranges using offset function.
This video from the ground up helped clear my doubts,
Thank You

[mynameisujjal]

Really good explained....would love to look forward for Index match and array functions as well

[marcw.]

In fact we even use INDIRECT to find the best starting point that we know has our data.

[marcinescu]

Hi. I’ve gt a problem with the offset’s value error. I try to use 3 index matches in offset function for: reference, rows, cols. Separately all work correctly just when combined give an error. Also tried used Sum at the beginning of the formula syntax -no joy
Any ideas to fix it or substitute? Many thanks

[davidsookharry]

in this example how would you treat with changing criteria in the rows as you move across columns eg>= 15 but <=19 and the other column >=20 but <=24 and the other column >=25 but <=29 whislt also asking for a brake down on the number of females and males for each catergory with the y axis using dates and the other info going on the x axis?
A G E D I S T R I B U T I O N
>=15 <=19 >=20 <=24 >=25 <=29
M | F M | F M | F
date1
date2

The Source data would be an attendance register showing the students AGE in one col. The Gender in another Col. and the other columns are filled with dates. o would represent present whilst X would be absent. the columns would go down 200 rows