Dependent Drop Down List Dynamic Array in Excel

https://youtu.be/KPQV3ULOwyE

What does a dependent dropdown list do? Basically, it’s a way to select items from a list that depends on the values selected from another list. This example is going to be using the new M365 dynamic array functions like FILTER as well as cover the spill operator. I’ll also cover how you can deal with duplicate or out of order entries using the UNIQUE and SORT dynamic array functions. You’ll also find out why the table feature is a valuable part of this solution. Also, near the end I'll refer you to a previous way of doing this, if you don’t have M365 Excel with the dynamic array functions on your version. No dynamic array function on your version? Try https://www.youtube.com/watch?v=GaPS0EqVNJY

What Excel Experts Aren't Saying about the LAMBDA Function

https://youtu.be/5h3SoDADmcc

The Lambda function is one of those newest functions in the latest Microsoft 365 Excel subscription. What it basically does is let you combine a bunch of functions or steps into one function. For the average Excel user, you are probably not going to use this. In fact it’s said that most people use about only 10% of the features and functions of Excel. So when you hear someone mention this is one of the best new functions in Excel, you might want to think twice about that. Most people will probably not use this function on a daily basis. BUT if you are someone who creates Excel templates for others to use or just love an alternative to VBA, then this other weird sounding function is the perfect addition to your Excel toolbelt. You can see that using or creating functions with the LAMBDA function is easy. The key is that when you want to put together large combinations of functions and make it easier to call the next time, the LAMBDA function makes it much easier to use. Plus, there’s the recursion capability, so you VBA loving people may just eat that up and LAMBDA could become your new best friend.

Create a Calendar Table From Date to Today

https://youtu.be/iOHYFbvx8_0

When you’re analyzing data where you’ve got dates, sometimes it’s a good idea to create a calendar table that not only has a date, but single column fields for the year, month or day of week. It helps then to show more detailed in your data. You can create one directly in the Excel worksheet with functions or you can use Power Query to make it more dynamic like creating a rolling calendar to auto populates with the current date. Creating a calendar table is not that hard and it’s useful information to help you do some additional analysis if you’re comparing dates or want to do some date grouping in a more granular way. It’s takes little time to set up and it’ll be useful in the long run.

Combine and Unpivot Tables Multiple Worksheets Same File

https://youtu.be/PYPe_3rRl8I

You have multiple worksheets or tab to combine?  It's easy if its just 1 or 2.  It's even not that bad if you have to do some kind of transformation like to unpivot the table.  However if you have to do a LOT of tables or do it on a recurring basis, then it's becomes a hassle.  Power Query can rescue you from the mundane and laborious process of manually doing these steps.

Combine & Unpivot Tables Multiple Excel Files in Folder

https://youtu.be/D7IR5GuIEPk

Getting files from your co-worker or from a system and need to combine or join them together? Easy to do if you have a few file; it's a simple copy and paste. Even if you have to transform you data by unpivoting your table it's not a hassle. But if you've got over five files or it's a recurring process, it becomes a burden to do. Power Query can do all this in a semi automated way to solve your problem.

Combine and Unpivot Tables Multiple Worksheets Power Query Function

When you have multiple worksheets or tabs to combine together, it's not a difficult process to copy and paste. However if you need to transform the table in those worksheets, like unpivoting it; still is not that bad albeit it'll take a bit more work to transpose the columns and rows. Now if you had to do this to a LOT of worksheets or if you had to do this on a recurring basis, it becomes a pain to do this. The solution is to have Power Query automate these steps; and it can future proof it by incorporating new data.

Use Power Query Parameter to Quickly Filter

https://youtu.be/gFqlfSckc5s

This video will introduce you to using the parameter feature in Power Query. You can use parameter to pass values to other parts of your query to give interactivity to your query. This example will use a simple procedure of filtering a column to show how it works. Now filtering a column seems like overkill, but if you have a LOT of columns, scrolling across the many columns can be a hassle. Incorporating the parameter feature may make it less of a hassle.