Microsoft Excel Tutorial - Advanced Formula Tricks in Office 365 That Nobody Knows

Learn Microsoft Excel Office 365 advanced formula tricks that most people don't know.
Sign up to get updates, practice files and code snippets eepurl.com/hwyGg1
00:00 Initial Spreadsheet Data Setup
01:50 UNIQUE, VALUETOTEXT, FILTER functions
03:50 SUMIFS with Dynamic Arrays
07:35 SORT, LET, CHOOSE
11:40 INDEX Dynamic Arrays
13:20 XLOOKUP with Dynamic Arrays
15:20 TRANSFORM
16:20 SUMIFS with 2 Dimensional Dynamic Arrays
#excel #advanced #tutorial

[@agroasesor1]

Thanks for your efforts, time and PATIENCE to make this interesting video!!!! Clear voice.
No o background music.
Concrete.
Not hours.
Gracias mil desde COLOMBIA !!!!

[@elricho72]

What good videos you make, they really are very helpful. thanks for sharing

[@patrickschardt7724]

Great in depth tutorial

[@user-li7mq5hp9g]

One of the best channels in TH-cam thank you!

[@ExcelGoogleSheets]

Thank you!

[@sandip_bettereveryday]

Watching this one video is enough to revisit what's there in Excel Dynamic Array functions, that we must know. Thanks a ton.

[@ExcelGoogleSheets]

What's really annoying is that after adding all these great features they didn't add a way to combine arrays on top of each other or next to each other. That's one things that's still missing to have a killer spreadsheet app.

[@frankcklin]

perfect demonstration

[@s.s.malagi275]

Very good illustration of formula and tricks.

[@ExcelGoogleSheets]

Thanks!

[@masoon3373]

Thanks!

[@khanabdussabur8604]

Awesome! Thanks a lot.

[@joshuamanampiu6489]

Excellent!

[@jamesepace]

Really well done explanation of this!

[@ExcelGoogleSheets]

Thank you!

[@cric8482]

So clever !!!

[@mo-brifs-infos6631]

well done for your useful information, please I want this practice file.

[@ngdzung6284]

Thanks

[@mariecourlier3332]

Thanks !!!! I love the function combining the sort + unique + sumifs. How would you do it with a sumifs based on two columns (in your case Sales Rep plus let’s say region)?

[@ExcelGoogleSheets]

SUMIFS(numbersrange,salesreprange,"Olivia",regionrange,"midwest")

[@mariecourlier3332]

Thanks for your reply. However, that won’t work as I want the region to appear in the array next to the sales people
Column A> sales people
Column B> region of the sales people
Column C > sumifs of the sales people for each region

[@ExcelGoogleSheets]

@@mariecourlier3332 I'm not sure about your layout. Please make an example in Excel online and share with edit permissions here. www.office.com/launch/excel

[@mariecourlier3332]

let me know if you have access. Thanks again for your great video and help

[@ExcelGoogleSheets]

There is no link

[@vbarchild]

Excellent

[@ExcelGoogleSheets]

Thank you! Cheers!

[@relaxationparadise4466]

Brilliant! can you please share the working file sir? Thank you!

[@youngistanrocks2146]

Hello There,
thanks for providing such a wonderful information regarding the dynamic array in excel. I hope this will apply in both online and offline version of excel.
I have one query regarding the online vesio n of excel might be many of us have. I have two different workbook saved on onedrive and i want to apply vlookup between them by using online version of excel. Is it possible?? Because while doing the same i find my self difficult while selecting the array of the other workbook. Would you be able to give me a solution for that?
Thanks..😊

[@ExcelGoogleSheets]

There is new feature recently released that allows this, but it doesn't work well yet. I'll try to do a video on this.

[@youngistanrocks2146]

@@ExcelGoogleSheets Thank you so much..

[@avenewconsulting1118]

why dont you use pivot tables?

[@IanDobson-D360]

One can probably do this a lot easier in Google Sheets using the Query function, could it also be done using Power Query in Excel?

[@ExcelGoogleSheets]

Power Pivot could do it, but that's not really the point of the video.

[@findthetruth3021]

I have an Excel sheet and I have a complicated question in which I couldn't solve by myself for such a long time. so how can I get in touch with you and explain to you so that you can help me on that. Thanks in advance.

[@bogdanescu86]

You can always try r/excel on reddit

[@findthetruth3021]

@@bogdanescu86 could you please share the link of it with me?

[@cric8482]

Often, when the solution is difficult to find the reason is the data are badly formated. (Personnal experience)

[@ronyjoseph529]

How to create the dashboard a table?

[@mohrmm6046]

How can i delete sharing file in excel 365 online web

[@dwarfusinterruptus9314]

I signed up to get practice files, but never received a link or file to download. Are you just collecting email addresses?

[@ExcelGoogleSheets]

Those go out for new videos only. Sorry for the confusion.

[@Mr.KundanThakur]

Sir please speak most popular language "Hindi"

[@Leyla72]

Why is the formula giving an error?
👇
=SUMIFS((B6:B80/3)^2,C6:C80,"Table")

[@ExcelGoogleSheets]

sumrange must be a range, can't be a calculation.

[@Leyla72]

@@ExcelGoogleSheets Thank you

[@ExcelGoogleSheets]

@@Leyla72 Just add this formula in a separate column and then use that range in SUMIFS =(B6:B80/3)^2