Excel Array Formulas & Functions: 50 Examples of How to Become an Array Expert! - 365 MECS 09

This video is a comprehensive lesson in array formulas functions in the Excel Worksheet.

Topics in video:
1. (00:00) Introduction
2. (00:33) Review Topics
3. (01:06) Review of pdf notes!!!!
4. (02:15) Array Formula Fundamentals: 10 Examples and lots of Terms, Definitions, and Types
5. (23:08) Two Main Reasons to use Array Formulas:
6. (23:08) 1) Array Formulas Make Things Easy (Budget and Investment Examples)
7. (27:55) 2) Array Formulas Help Make Compact Solutions (Finance and Budget Examples)
8. (34:26) SUMPRODUCT function (Total Sales and Grading Examples)
9. (38:21) List of ALL Array Functions
10. (39:35) Array Function Examples:
11. (39:35) Re-Orient Data. Function examples: CHOOSECOLS, CHOOSEROWS, DROP, TAKE, VSTACK, HSTACK, SORT, TOCOL, TOROW, TRANSPOSE, WRAPCOLS, WRAPROWS, SORTBY, UNIQUE, EXPAND, FILTER
12. (51:22) Dynamic Product Sales Report with Total Rows using SORT, UNIQUE, SUMIFS and VSTACK Array Functions
13. (54:34) Create Arrays of Numbers. RANDARRAY Array Function. 6 Examples.
14. (58:59) Create Arrays of Numbers. SEQUENCE Array Function. 6 Examples.
15. (01:03:26) Statistics & Matrix Algebra. Function examples: FREQUENCY, MODE.MULT, TREND, LINEST
16. (01:07:13) Text. Function example: TEXTSPLIT
17. (01:09:09) Summary and Conclusions
18. () Closing and Video Links

#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#MECS09
#Microsoft365ExcelCompleteStory
#ArrayFormulas
#ExcelArrayFormulas
#CtrlShiftEnter
#DynamicSpilledArrayFormulas
#ExcelArrayFormulaLesson
#FreeArrayFormulaClass
#CHOOSECOLS
#CHOOSEROWS
#DROP
#TAKE
#VSTACK
#HSTACK
#SORT
#TOCOL
#TOROW
#TRANSPOSE
#WRAPCOLS
#WRAPROWS
#SORTBY
#UNIQUE
#EXPAND
#RANDARRAY
#SEQUENCE
#TEXTSPLIT
#FreeClass
#FreePowerBIClass
#dynamicarrayformulas
#dynamisspilledarrayformulas
#excelarrayformulas
#exceldynamicarrayformulas
#exceldynamisspilledarrayformulas
#excelformulas
#excelFunctions
#dataanalysis
#dataanalytics

[skimpylemon]

I'm genuinely baffled by why this content is FREE. You blow my mind with every new video! Thank you Sir! :)

[othmanelebbache]

excel is fun, so lets having fun with excel..

indeed. what makes excel fun is the teacher mike ..

[realtoast]

You wouldn't believe how much these lessons comport with my actual sales manager workday! I can't watch for free

[efficiency]

Outstanding. A lifetime of Excel in one hour.

[TheLittleRyeAlchemistintheLigh]

This might sound so cheesy and a bit too much. But dare I say that this world needs more people like you. So generous. So smart. Thank you. You’re a lifesaver.

[thryce]

this channel has helped me out so much at work. I hate excel like hate excel because its so buggy on meaningful amounts of data. But this channel has taught me enough that when excel actually works it can be a joy. thanks for that

[gayaa]

Thanks so much, Mike. I hope you've been well :)

[StopWhining]

Great explanation, especially establishing the boundaries about what an array is not. Too many instructors leave out that part in their videos. Your notice that in an array the formula is only in one cell was key.

[azizurrehman]

Sir i am really your big Fan. From see content rarely like this...in free for the public... ....your every tutorial give me a lot of concept and it give strong base to understand everything from basic to advance i solve many problems in Excel after watching your videos...alot of confident...i search your every video like Excel beginner, then power query completer course and now this brand new course ..it's really a complete story...Excel 365...but you explain very well ..i don't have words for this ..

[visakhsarma]

Found this channel from reddit. You are amazing.

[LostAccount-usfe]

Really really glad I asked chatgpt to recommend best youtube channels for Excel. Thank you for such great quality content. ❤

[excelisfun]

I have added 10 practice problems (homework problems) to the end of the download workbook - so that you can test your skills : )
Also: at 04:55 the label should read "Total Commissions", not "Total Sales" lol

[RajaNatarajan-gt]

Mike sir, you are mother/father of all teachers. Thanks to all your efforts and time for creating such content for free. If i am given a chance for Noble prize in the field of teaching and education, you will be top of my list 😀👍👍👍

[canirmalchoudhary]

Great detailed video on dynamic array and array functions thank you 😊

[vosk]

Mike, you are truly a prophet, an oracle of sorts in our lifetime. It's so true, Excel is grossly underrated and underutilized in the world of education and business workflow. For some reason, PowerPoint and Word usually take precedence. It's difficult to comprehend this conundrum and the fear that many people have of spreadsheets and data analysis. It's significantly overlooked in high schools and universities and many corporate environments. I work in the area of real estate finance and have been helping people with financial (students at all levels as well as companies seeking training) on a part time basis. Since taking a deeper dive into your invaluable materials, especially array operations and sophisticated lookup situations, demand for my services have notably risen. There is high demand out there and the rewards are immeasurable. Thank you so much for your service.

[isaacsewornudzordzorme]

ExcelIsFun Indded. Thank you Mike. Have been following you for at least 7 years

[petardzopalic]

Thank you Mike for your teachings, much have I learned from you since 2013.I am from 3 world countries and thank you because you teach for free.

[richardhay]

Great demonstration of your mastery of array functions!! I saw it posted Saturday night but no time to watch till this (Monday) morning! BTW I recently helped someone with UNIQUE and she eded up with a distinct list of about 1100 items. She needed to move this result down a couple of rows. She started to highlight the entire long list. She was greatly relived that she only had to click and drag the one cell that contained the formula and all the remaining items followed along like obedient puppies!!!

[JoseAntonioMorato]

Dear Mike,
Your «Budget and investment examples», gets more amazing if reduced to a single cell:
=LET(Year, SEQUENCE(C27, , C26, C28),
Rate, SEQUENCE(, C30, C29, C31),
HSTACK(VSTACK("Year/Rate of Return", Year), VSTACK(Rate, FV(Rate, Year, -C32, -C33)))) 🤗

[lucaviglio]

Finally i managed to study this another amazing amazed about how versatile these new formulas are...Thank again for your work Mike