Dynamic arrays in Excel | Understanding the biggest change to Excel.... ever

Dynamic arrays in Excel | Understanding the biggest change to Excel.... ever | Excel Off The Grid

Рет қаралды 39,246

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Read the full blog post ★
exceloffthegrid.com/dynamic-a...
★ About this video ★
Excel has changed… like seriously, changed. Excel now allows a single formula to fill multiple cells. This is possible because Microsoft has changed Excel’s calculation engine to allow dynamic arrays.
In this video, I should you the basics needed to get started with this new way of thinking.
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #ExcelOffTheGrid

Пікірлер: 50

@fjbekkering Ай бұрын

Really good man. I very much liked the background information about implicit intersection. I now finally understand the control shift enter thing, never really knew what that was!

@roberth.9558 Жыл бұрын

I admire the clear way you break down the functions and appreciate the example files. Thank you. You are good at what you do.

@ExcelOffTheGrid Жыл бұрын

Thanks Robert, that is very kind of you to say 😁

@manoellopes8071 2 жыл бұрын

The best intro of Dynamic Array in excel

@ExcelOffTheGrid 2 жыл бұрын

Thank you Manoel - that's very kind of you to say.

@julienbouillot6960 2 жыл бұрын

I've been looking for this for so long! So many videos about the new array functions but few about using the new dynamic arrays with more classic functions. Thank you!

@ExcelOffTheGrid 2 жыл бұрын

Exactly!!! For me, Dynamic Arrays are not about the new functions, but that almost every other function has now had an upgrade. Even without the new functions, Dynamic Arrays would have been huge 😀

@Sri-Nivas 3 жыл бұрын

The best intro of DA in excel.. Best tutor.. Keep up the good work..

@ExcelOffTheGrid 3 жыл бұрын

Wow, thanks that's very kind of you to say :-)

@esthersuh3388 Жыл бұрын

Excellent clarification for the dynamic array, thanks!!!!!

@ExcelOffTheGrid Жыл бұрын

You’re welcome. Glad I could help.

@cokeheadoffice 3 жыл бұрын

Excellent video to understand the new features of dynamic array functions

@ExcelOffTheGrid 3 жыл бұрын

Thank you - I’m glad you found it useful.

@diegocisneros-sf 3 жыл бұрын

Great videos. I've watched all the videos about the new array formulas and you may be the best Excel instructor around. No frills but outstanding. Very clear.

@ExcelOffTheGrid 3 жыл бұрын

Thanks Diego. I appreciate that. Dynamic arrays are certainly great addition to Excel. 👍

@modernaone 2 жыл бұрын

I agree. There are a few excellent instructors out there but you are very concise and offer ton of valuable information using the current content format to publish your videos. Congratulations for your excellent job! 👏🏻👏🏻👏🏻

@dileepkumaryadav1666 Жыл бұрын

Thanks for this video, help a lot to understand dynamic arrays

@ExcelOffTheGrid Жыл бұрын

Thank you - I’m glad it helped 😀

@ugochukwuonyeka1735 2 жыл бұрын

Mark Proctor!!! Where have you been all my life??? Amazing content you've got!

@ExcelOffTheGrid 2 жыл бұрын

I've been right here waiting for you to find me .... :-)

@controlsgirl 2 жыл бұрын

Great breakdown!

@ExcelOffTheGrid Жыл бұрын

Thanks. 😀

@MohamedAhmed-training Жыл бұрын

Great vedio we use this option now in Excel 2021 v

@stephanweaver1960 2 жыл бұрын

Thanks about the constant array bit

@ExcelOffTheGrid 2 жыл бұрын

Thanks - I think a good understanding of constant arrays is key to understanding this topic.

@brunobaetenscvoscala8818 Жыл бұрын

one word: perfect!

@ExcelOffTheGrid Жыл бұрын

Even if you went with two words ‘almost perfect’, I still would have been happy with that 😀

@ernstgenzs7456 3 жыл бұрын

Thank you for sharing this valuable information. I didn't know how many new tricks Excel brought along. Question, how come the sumifs formula doesn't give you the pound sign? I am mirroring your formulas in 365 and it was all similar except the pound sign.

@ExcelOffTheGrid 2 жыл бұрын

Without seeing your screen, I'm not sure. Does it work correctly when you add/remove the # sign? That's the most important question.

@FRANKWHITE1996 Жыл бұрын

great explanation

@ExcelOffTheGrid Жыл бұрын

Thanks Frank. 😀

@FRANKWHITE1996 Жыл бұрын

@@ExcelOffTheGrid likewise 🙏

@jeny1279 3 жыл бұрын

I knew about the new XLOOKUP function, but didn't fully understand the impacts of the new dynamic arrays - the implications of this change are pretty cool. Out of curiosity, is there any way that you've found to base the range of row numbers within the brackets in your VLOOKUP to be based off an alternate lookup? I've tried a few different options... even went with an Indirect of some sort, but it doesn't want to recognized the list. I can think of quite a few uses for that.

@ExcelOffTheGrid 3 жыл бұрын

I'm glad it give you a good introduction into the changes to Excel. In terms of your question, I think you want to look at a VLOOKUP/MATCH or INDEX/MATCH/MATCH formula combination. I have a post about INDEX/MATCH/MATCH here: exceloffthegrid.com/index-match-match-in-excel-2-dimension-lookup/ Trump Excel has a good post about VLOOKUP MATCH here: excelchamps.com/blog/vlookup-match/

@jeny1279 3 жыл бұрын

@@ExcelOffTheGrid Thanks. I'm familiar with the formulas, I was just excited at being able to pull multiple data elements for multiple lookups with a single formula and was looking for a way to designate the column references all in one shot. Great video, though - I'll be checking out the rest.

@ExcelOffTheGrid 3 жыл бұрын

A part from using a constant array, like {1,2,3}, I think another function is the only option.

@sandip_bettereveryday 3 жыл бұрын

In older Excel suppose in any single cell we write =B2:B$6 and then press Ctrl+Shift+Enter and then we drag that cell below. What happens in the sixth cell down, is {=B$6:B7} Surprising, na? Why this happens? Is there any explanation?

@abdidekmuhumed4510 2 жыл бұрын

Thank you too much

@ExcelOffTheGrid 2 жыл бұрын

You are most welcome

@sriamudhan2062 Жыл бұрын

Hello, How can I use # operator with Xlookup function, where the no. of rows in the data source can fluctuate from one instance to another? Can an example be provided regarding syntax?

@ExcelOffTheGrid Жыл бұрын

It depends on circumstance. Are the lookup_array and return_array arguments based on individual spill ranges, or one large single spill ranges?

@Sourenics 2 жыл бұрын

This does not work en 2021 version, right? I'm trying to do the F2# and the VLOOKUP {X,Y} examples and does not work.

@ExcelOffTheGrid 2 жыл бұрын

You can do F2# or {X,Y} in a VLOOKUP, but not both at the same time.

@Sourenics 2 жыл бұрын

@@ExcelOffTheGrid I figured it out. The F2# thing does not work unless the data you want to "select" is an array. For the VLOOKUP thing going to try when I can get to my PC

@Sourenics 2 жыл бұрын

Yep, it's the same, you need an array to use the function. So useful! Thanks for the vid!!!

@ExcelOffTheGrid 2 жыл бұрын

@@Sourenics Yes, the # refers to the spill range.

@Sourenics 2 жыл бұрын

@@ExcelOffTheGrid The vlookup shows the data in vertical, can't make it to show the values in horizontal (spanish excel).

@NhiNgo-up8js 5 күн бұрын

when I put # behind a cell, It not work and return #REF error. Is there any solution. I use 365

@sebfromgermany3819 5 күн бұрын

Go back to the video 14:10 where Mark talks about referencing methodology, depending on your country you may have to use “pound” not “hash”; good luck

@NhiNgo-up8js 4 күн бұрын

@@sebfromgermany3819 thank you