Excel OFFSET function basics + Dynamic Ranges | 5 Examples
Вставка
- Опубліковано 12 вер 2024
- In this video we show the excel OFFSET function basics and 5 practical examples.
Download Example Workbook here:
bit.ly/3kBlRNv
How to use INDEX & MATCH:
• 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:
Facebook: tinyurl.com/25...
Twitter: tinyurl.com/yc...
LinkedIn: tinyurl.com/yc...
Instagram: tinyurl.com/3m...
TikTok: tinyurl.com/p3...
Telegram: tinyurl.com/es...
Website: tinyurl.com/tw...
Subscribe to my UA-cam Channel:
bit.ly/2NdO6UP
Visit my website at www.ExcelBonanza.com
#offset #dynamicrange #exceltricks
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!
Where is the link to the Excel workbook? The link provided goes to your subscription page, not the workbook download. Do you mention in the video that the Offset is a volatile function and should be avoided with larger data sets because it can slow down calculation time?
Hi Michael,
Thanks for the heads up. I have fixed the link.
I have also mentioned at the end of the video that the OFFSET function is a volatile function.
However, in my practical experience, I haven't encountered any slowdowns when using the OFFSET function. I have created dashboards with 10 and even 20 or more dynamic ranges using OFFSET and haven't experienced any slowdowns.
As an Alternative, you could use INDEX to build dynamic ranges. Let me know if you need me to make a video about that.
I have experienced slow downs with performance but his might be due to a large number of array formulas I was using. Plus, not limiting the used ranges (i.e. referencing all the rows) makes a difference if you have a large file. I think the slowdown might occurr when you start dealing with lots of formulas and thousands of rows of data. I had one job where they had a P&L statement for each division (each sheet contained over 500 rows of data and 30 or more columns of vlookup formulas). It would take 5 minutes to save the file or 2 or 3 minutes to copy and paste data. I should have taken off the automatically calculation and set it to manual. It was a nightmare!
@@michaeldiamond2726 interesting. But it shouldn't necessarily be because of the offset function. Could be because of having lots of rows and array formulas. Anyways, thanks for passing by and let me know your feedback about my videos :)
I clicked on the new link and it sent me to the IndexMatch video, not the Excel workbook. Might want to check on that ...
In my opinion the best explanation of the Offset function on UA-cam.
Thanks, David! I'm glad you liked it!
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.
Thanks, Marc! I'm glad that you found it helpful! It's the first time I hear that it can be used for web scraping as well! That's awesome! Thanks for commenting. Much Appreciated!
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
Thank you! I'm glad that you found the video helpful. Please let me know if there's a certain topic that you would like me to create a video about.
Very nicely explained! Great Job!!
Really good explained....would love to look forward for Index match and array functions as well
I do have an INDEX Match video on the channel. Check it out!
جزاك الله خيرا
Thank you sir
+M. Alomery جزانا وإياكم
My pleasure :)
In fact we even use INDIRECT to find the best starting point that we know has our data.
Thanks, Marc!
tnx
My pleasure!
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
What version of office are you using?
@@ExcelBonanza Hi 365
The inputs for the Offset function should produce integers. Is this the case on your INDEX - Match formulas?
@@ExcelBonanza Please find the syntax =OFFSET(INDEX(50:50,MATCH($H$45,50:50,0)),INDEX(A:A,MATCH($I$45,A:A,0)),INDEX(52:52,MATCH($J$45,52:52,0)),1,4) - in the formula's window all matches give correct numbers of rows and columns but I've ended up with the value error
in this example how would you treat with changing criteria in the rows as you move across columns eg>= 15 but =20 but =25 but =15 =20 =25
Sorry. Don't understand the question