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

Подписаться 28 тыс.

Просмотров 39 тыс.

50% 1

★ 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

Опубликовано:

12 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 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 😀

@cokeheadoffice 3 года назад

Excellent video to understand the new features of dynamic array functions

@ExcelOffTheGrid 3 года назад

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

@esthersuh3388 Год назад

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

@ExcelOffTheGrid Год назад

You’re welcome. Glad I could help.

@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 :-)

@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 😀

@controlsgirl 2 года назад

Great breakdown!

@ExcelOffTheGrid 2 года назад

Thanks. 😀

@ugochukwuonyeka1735 3 года назад

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

@ExcelOffTheGrid 3 года назад

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

@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 😀

@FRANKWHITE1996 Год назад

great explanation

@ExcelOffTheGrid Год назад

Thanks Frank. 😀

@FRANKWHITE1996 Год назад

@@ExcelOffTheGrid likewise 🙏

@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 3 года назад

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

@abdidekmuhumed4510 3 года назад

Thank you too much

@ExcelOffTheGrid 3 года назад

You are most welcome

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

@sriamudhan2062 2 года назад

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 2 года назад

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 17 дней назад

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

@sebfromgermany3819 17 дней назад

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 16 дней назад

@@sebfromgermany3819 thank you