Why You Should Use Excel Advanced Filter For This

https://youtu.be/wlsMISaiLHs

Do you have complexed filtering to do on your table data? Is your boss looking over your shoulder while giving your instructions on let’s filter for this and that and include these two, three, or four other things? You’re probably thinking how am I going to do this? Put it in a Pivot Table? Write a VBA script? Hold on...there’s already something in Excel that can do this advanced filtering. It’s even been around since the late 2000s...and it’s appropriately called.... Advanced Filter. You’d be surprised what type of complex filtering can be done with a feature that’s been around since Excel 2007, but it’s there. Once you realize how useful it is, it’ll be your secret tool for complex filters. Even though Microsoft wows us with new filtering features, like the dynamic array function FILTER just know that you can still do some pretty cool stuff with the good old advance filter feature. See FILTER function at https://www.youtube.com/watch?v=pjr5cljnvxU

Separate Text and Numbers in Excel

https://youtu.be/cA9ypXn37jg

When you’re cleaning up some data, maybe you get something where the number and text are combined and need to be separated or split into columns. It could be something as easy as when a fixed set of numbers come after text or a little more complicated where some set of variable numbers come first. Or ...it could be something as hairy as when a bunch of numbers and text are joined together in multiple random lengths. Do you have to write complex formulas or VBA to clean this up? No way...it can be done with a few keystrokes and maybe even just one hand. I’ll show examples of how we could separate the number and text in native Excel and then with Power Query.

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