Excel OFFSET Function - including Common MISTAKES to Avoid!

The OFFSET function is the magic ingredient for writing formulas that automatically adjust as your data grows or summarise data based on criteria you set on the fly.

0:00 How to use the OFFSET function
0:49 OFFSET Function Syntax
1:30 Basic OFFSET Examples
4:46 Dynamic SUM with OFFSET
6:09 Dynamic ranges with OFFSET
7:49 Dynamic named ranges with OFFSET for charts
11:35 Common Mistakes and How to Fix Them

[TraceyAOK]

You are without a doubt the BEST excel YouTuber. So clear and easy to understand.

[teoxengineer]

Thank you for this helpful video
My questions for offset formula:
1- Can we use the offset formula in the tables?
2- Can we use the two nested offset formulas? For example, when using VBA, we can select and select as many lines and columns as we wish in a specific range. In an Offset Range, can a second ranking be selected?
3- In a range, we usually use " " to hide the error values ​​when the Iferror function is used. This is returned by Excel to empty or 0. This takes into account the value formula when determining the number of rows in the offset formula. This gives false results. In a column or line series, how can we explain to Excel whether the relevant count or text is to understand how many values ​​are?
4- Index (): OFFSET() Can we create a data sequence using thise formula?
5- Can we use offset to extract data from different pages or workbooks?

[nsanch]

Thank you Mynda for the great offset video. I had fun following along with a good cup of coffee this morning :) When ever I see the offset function being used with named ranges in a charts, I always wonder why one would use the offset method vs using a table. I always go for the table method.

[stevebulls]

I may be a little late for the party, Mynda, but, as always, the information is superb and the explanation is clear and easily understood. Thanks so much for the video AND the file with which we can practice. Thanks!!!

[rjbanavar]

Hi Mynda: have seen many offset function video by excel experts. But yours is the best of the best video on offset function. After watching I can get what offset functions do. Thanks a lot for the simplicity.

[realHAUTEcre]

Your content is spectacular and very well-produced, thank you for creating this!

[chrism]

Thanks Mynda, I don’t use OFFSET often, so this was a great refresher

[sujirpinna]

Using offsets for chart blew my mind . Great explanation as well as application of a concept/Formula

[missamo]

thank you! I needed to sum the last 7 rows of a table and for some reason was struggling with OFFSET. It only took until 2:32 of this video before I got it sorted. Amazing as always Mynda!

[lecrapface]

This seems like a great channel that I accidentally found. There is something incredibly hot about a woman who knows how to use Excel this well. To me it's the most underrated program from Microsoft Office and glad to see someone is teaching the deeper functions of an infinitely flexible program.

[Everyonelovesyou]

Got the clear understanding for Offset through this video. Simple

[GeertDelmulle]

I used to use OFFSET quite a bit, but now with dynamic arrays I avoid it completely.
And that is a good thing: OFFSET is a volatile function that quickly bogs down massive calculations and dynamic arrays are intrinsically much faster to compute. Great video and explanation, though.

[alexbigonnet]

Good video Mynda, I often use offset, but not necessarily the way you showed...learning evry day so thanks a lot for your excellent tutorials.

[aasavtamboli]

Superb explanation Ma'am
Offset is displaying value error when in the row argument i try to enter array constant for eg {0;1;2;3}
I have ms office 2021 which has dynamic arrays

[buzdesigns]

Wow, nice video. I'm designing a template and I believe people with Excel 2019 and earlier version will use it. I want to spill the values just like the way 365 version does it. How do I do that without an error?
Using CSE (Ctrl + Shift + Enter) gets rid of the error sometimes, but it only display just the first value.

[alexanderbaranov]

Thanks for sharing knowledge, Mynda. Highly informative, as usual 😉

[mine_paris]

That is fabulous informative video thank you so much, kindly I wanna ask is there any function I can use for a dynamic range that include blanks in the cells.

[anv.]

Thank you. Appreciated your lesson with theoretical introduction. best and thanks

[megenimportexport]

Thank you again! I've got it from your training!

[joelngige]

I like the way you break down the OFFSET Function in this video. I had tried to grasp it in the past. I could not follow what was happening under the hood. The initial explanation where you demonstrate the behavior of the function using cell references and ranges makes it easier to understand the applications of it. From today i am the OFFSET MVP just because of your Video. Really appreciate. Keep these awesome videos coming.