Excel OFFSET Function - including Common MISTAKES to Avoid!

[@TraceyAOK]

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

[@MyOnlineTrainingHub]

Wow! Thanks for your kind words. Please share my videos with your co-workers.

[@nsanch0181]

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.

[@MyOnlineTrainingHub]

My pleasure! Because typically chart data is summarised, whereas table data is the underlying transactions before being summarised. You can summarise using a PivotTable and then insert a Pivot Chart, but not all chart types are available for Pivot Charts and that's one place where OFFSET can be useful in enabling you to build regular charts from PivotTables and still have them dynamically update.

[@sujirpinna]

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

[@MyOnlineTrainingHub]

Glad it was helpful! 😊

[@realHAUTEcre]

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

[@MyOnlineTrainingHub]

I appreciate that!

[@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.

[@MyOnlineTrainingHub]

Wow, thank you, Rajesh!

[@stevebulls7744]

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

[@MyOnlineTrainingHub]

My pleasure, Steve 🙏

[@chrism9037]

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

[@MyOnlineTrainingHub]

Glad it was helpful, Chris!

[@missamo80]

Ahhhhh 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!

[@MyOnlineTrainingHub]

Glad it was helpful, Neil!

[@Everyonelovesyou]

Got the clear understanding for Offset through this video. Simple

[@MyOnlineTrainingHub]

Great to hear!

[@Everyonelovesyou]

@@MyOnlineTrainingHub You are top excel teacher around. I have learned a lot, especially with shrinked timelines to do tasks

[@joelngige5776]

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.

[@MyOnlineTrainingHub]

That's awesome to hear, Joel!

[@lecrapface5270]

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.

[@MyOnlineTrainingHub]

Glad you found it helpful 😊

[@alexbigonnet5708]

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.

[@MyOnlineTrainingHub]

Great to hear, Alex!

[@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?

[@MyOnlineTrainingHub]

Hi Emre, 1. In Tables? I can't think of a scenario where you'd want to do this. You can't spill arrays in tables (in most cases), so unless OFFSET is returning a single cell, then probably not. 2. Not sure what you mean by a second ranking. 3. You can use COUNTIF(..., &"") 4. A data sequence...why not use SEQUENCE? 5. Yes. If you still have questions, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@alexanderbaranov5418]

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

[@MyOnlineTrainingHub]

My pleasure!

[@mine_paris5627]

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.

[@MyOnlineTrainingHub]

Glad you liked it! Regarding blanks, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@IvanCortinas_ES]

A very complete tutorial! Thank you Mynda!

[@MyOnlineTrainingHub]

Thanks, Ivan!

[@anv.4614]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@darrylmorgan]

Hi Mynda!Really Helpful Explanation Of The OFFSET Function...Thank You :)

[@MyOnlineTrainingHub]

Cheers, Darryl!

[@makubexho]

Thank you for uploading the video the offset function has always been a difficult one for me to overcome in grasping its application

[@MyOnlineTrainingHub]

My pleasure. Hope you found it helpful.

[@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.

[@MyOnlineTrainingHub]

Thank you! In earlier versions of Excel you can select all the cells you want the formula to 'spill' to before entering the formula and then press CTRL+SHIFT+ENTER. Wrap the formula in IFERROR to hide any errors.

[@megenimportexport7796]

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

[@MyOnlineTrainingHub]

Fantastic!

[@sterphyzomo8073]

Hello .I'm enjoying watching your class. Thank you so much. But i have a issue, I'm using excel 2013 and i have followed all the instructions but I'm getting an error. How can i solve it.

[@MyOnlineTrainingHub]

You can use OFFSET in Excel 2013 the same as shown in this video, so there will be something wrong with the formula. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@sajilprkkv]

Wow …superbly simple and easy ❤

[@MyOnlineTrainingHub]

So glad you think so 🙏😊

[@aasavtamboli7919]

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

[@MyOnlineTrainingHub]

The row argument takes a single value. If you want to return multiple values, use the height argument and enter 4.

[@iankr]

Many thanks, Mynda. I've used OFFSET() many times in the past, but only to return a reference to a single cell. Your lesson has expanded my understanding of it, and I can now see other situations where it can be useful.

[@MyOnlineTrainingHub]

That's great to hear, Ian!

[@PaulGodfrey]

Good explanation. Thank you.

[@MyOnlineTrainingHub]

My pleasure 😊

[@jaceklesniewicz8673]

Fabulous, as always. Thank you so much for explaing the details in such an aproachable way. PS. I believe there is a tiny glitch in the workbook attached to the newsletter. The formula in DataValidation for the "Select the program" field value should be =$B$67:$B$73 as it's a bit dead now.

[@MyOnlineTrainingHub]

Cheers, Jacek. I edited the workbook after recording the video 😉

[@megenimportexport7796]

I really appreciate the way you do offset. Can you help me to change this list given by formula =OFFSET(Sheet1!$A$2,1,,COUNTA(Sheet1!$A$2:$A$10002)-1,1) to start at Sheet3 g8 position?

[@MyOnlineTrainingHub]

Thanks, Megen. =OFFSET(Sheet3!$G$8,1....

[@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.

[@MyOnlineTrainingHub]

Personally, I've never used OFFSET so much that it has caused performance issues because usually it's for dynamic named ranges, rather than occupying 1000's of cells in a worksheet. However, it is volatile, like you say and with Tables and structured references we hardly need it anymore, except in some exceptions.

[@Luciano_mp]

Thank you Mynda!

[@MyOnlineTrainingHub]

My pleasure, Luciano!

[@fk5774]

Is there a workaround to OFFSET with the [height] referencing a cell with RANDBETWEEN and thus causing a spill?

[@MyOnlineTrainingHub]

Not sure what the scenario would be for this. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@lopher70]

Wow!! Always surprise me, thanks!!

[@MyOnlineTrainingHub]

Glad to hear that, Ricardo!

[@marcinescu091]

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

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@suhashkantamneni673]

Thanks a lot. Very helpful.

[@MyOnlineTrainingHub]

You are welcome!

[@nnamdiofodile2507]

Thank you dear Mynda. This has been most helpful.

[@MyOnlineTrainingHub]

I'm so glad!

[@schoolfriendsteve663]

Quality content, as always, Mynda! Thank you!

[@MyOnlineTrainingHub]

Thanks so much!

[@daniyalbaghi9395]

excellent 👏❤️

[@MyOnlineTrainingHub]

Thank you! 😃

[@OwMyHip]

Hi Mynda, thanks for sharing. Another great lesson as always! I do have a question though: for the last example before the common errors, is there a benefit to using offset to do this over just making the data an Excel table to keep the ranges dynamic? I learned to love using tables from some of your other lessons, so I'm just curious how using offset for that would compare.

[@MyOnlineTrainingHub]

If your chart data is in an Excel Table then you don't need this technique. However, quite often the chart source data is generated by formulas or a PivotTable that summarise data contained in the Excel Table, so the Table itself isn't able to be referenced directly by the chart. e.g.: building regular charts from PivotTables: kzbin.info/www/bejne/a6eyoo14oqeFpdU

[@MohammadAshooryan]

Thank you for sharing.

[@MyOnlineTrainingHub]

My pleasure!

[@kleinboertjie]

Hi. I use the header as reference an go down one row. This prevents the ref error if the first row is deleted

[@MyOnlineTrainingHub]

Good thing to keep in mind if that's likely to happen in your dataset.

[@mon_rich5366]

the best from thailand

[@MyOnlineTrainingHub]

Thank you!

[@VVeiQuek]

-VE value for going LEFT and +VE for going RIGHT is easily understood, but for a long time, I cant brain "-VE value for going UP" and "+VE for going DOWN" (unlike a Cartesian plane). But I guess it has to be so since all sheets start from upper left corner, going right and down is a entropic certainty.

[@MyOnlineTrainingHub]

I've never worked with Cartesian planes, so this has never been a problem for me, but I can see how it would be confusing.

[@afzaalahmed9565]

why it gives error when i put column width in OFFSET

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@tha2irtalib343]

Great .. thanks.

[@MyOnlineTrainingHub]

Glad you liked it!

[@jagdishvishwakarma5273]

I am not getting values in 3 rows and 2 columns just getting value error and in some cases just one single value

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@mr.write1433]

if use offset and save it to the manager !! when i use match the offset wont read the first row for freakin reason its so annoying bug !!

[@MyOnlineTrainingHub]

Not a bug, more likely a formula error. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@furtim1]

Thank you

[@MyOnlineTrainingHub]

You're welcome 😊

[@jstorm858]

what is the difference, supposed you will just reference a cell by typing "=" and "the cell your making refernce".?

[@MyOnlineTrainingHub]

The difference is that reference won't automatically expand or contract as the data changes, which is fine if you don't expect it to.

[@rewatiraman2956]

its nice to understand in very easy manner.

[@MyOnlineTrainingHub]

Great to hear!

[@INCltendor]

I am unable to do the same thing as you have done in excel

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you figure out what's going wrong: www.myonlinetraininghub.com/excel-forum

[@mon_rich5366]

can you one day make a full course on udemy i will purchase it

[@MyOnlineTrainingHub]

I don't have any plans to put more courses on Udemy. You can take my courses from my own site here though: www.myonlinetraininghub.com/

[@mon_rich5366]

@@MyOnlineTrainingHub i mean can you put your existing course into udemy

[@phvrieler]

A common mistake I’ve seen is using Offset with merged cells. Because then your count for rows and/or columns changes.

[@MyOnlineTrainingHub]

Yes, great point. Merged cells are evil 😉

[@ajaysharmaDistrictShamli.]

Nice...

[@MyOnlineTrainingHub]

Cheers, Ajay!

[@pipo441]

Don't understand the purpose of the reference....

[@MyOnlineTrainingHub]

The reference argument is just the starting point. Much like on a treasure map, you have a starting point, and you then move n steps away from that point to reach the treasure location.

[@tahaboxwala4473]

Voice is very slow in all your videos

[@MyOnlineTrainingHub]

Strange. Have you checked the playback speed on the video isn't set to 0.75? If not, try setting it to 1.25.

[@jasonsmith1273]

Excel users trying to pass themselves off as Developers kill me .

[@VVeiQuek]

Let's not look at the means, but the ends/outcomes. If similar output can be achieved without an additional IT headcount and additional cost in licensing of programming, yes that Excel user achieves what a developer tries to achieve, and by deductive reasoning...

[@daniyalbaghi9395]

Hello, do you have Instagram?

[@MyOnlineTrainingHub]

Sure do: instagram.com/mynda.treacy/

[@daniyalbaghi9395]

@@MyOnlineTrainingHub Thank you🤩