FILTER Function to Create Sub Table from Master Table. Data Validation Too! Excel Magic Trick 1842

Learn about how to use the FILTER function to create sub tables based on a master table. Also learn how to create a unique sorted list of city names for data validation. Learn how to create Conditional Formatting for Dynamic Spilled Arrays.
Topics:
1. (00:00) Introduction
2. (00:05) Filter data set by city name
3. (00:35) UNIQUE and SORT functions to extract a sorted unique list
4. (01:05) Data Validation
5. (01:42) FILTER Function
6. (02:29) Conditional Formatting for Dynamic Spilled Arrays
7. (03:13) Summary
8. (03:23) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #conditionalFormat #uniquefunction #sortfunction #filterfunction

[Excelambda]

Great video!!
These old formulas should have collectable value !! Like an old 930 Porsche 911 Turbo 😊✌

[jameszhou]

thank you Master Mike for another fantastic short trick on filter function. In fact, appreciate your systematic free videos and excel/power bi files for me to start appreciate the technology

[jonk]

You taught me excel in 2014 and I always love how you always emphasize on shortcuts. I am always seen as an expert in the office because of those shortcuts. 😀. Many thanks

[HusseinKorish]

Old school way was a masterpiece at that time ... Thanks Mike

[kyleallen]

what a phenomenal function that is SO EASY.

[ExcelHechoFacil]

Gracias Mike, por esta nueva entrega. Yo espero que todo este muy bien de tu lado. Un abrazo fuerte hasta la distancia. Saludos.

[mattschoular]

Thanks Mike. It's always easy to understand your methodology. Thanks for sharing...

[johnborg]

Thanks Mike. Its amazing how new excel functions have helped to shorten a formula with less complications. Although the old school was fun to build too. :) :)

[TopBam]

Excellent! Thank you for another masterpiece demonstration in teaching!

[spilledgraphics]

Back to basic RAD Mike !! but oh boy minute 2:28.... the good old school ways! Never to be forgotten.

[chrism]

Thanks Mike! Thank God we got the Microsoft 365 functions. That old formula looks painful!

[khanabdussabur]

Northing to say mike, Just amazing of Filter function. Thanks a lot.

[roywilson]

Hi Mike hope you are well and that your mom is on the mend.

Another great video! So much easier than the old school version; it is strange to think there are competent excel users out there that have never and will never know the brain ache caused by such intricate formulae, or the absolute headache of debugging them!

I look forward to the next in the series.

[ebrahimalfardan]

Hey Mike. Welcome back. Hope your mom is doing better now

[ExcelInstructor]

yes! One function to rule them All!
Filter for the win.
Superb Video:) Mike,

How is your mother doing?

[krn]

Des Monis? lol. Glad to see something never change. Thanks for the video Mike. Just need a few more subscribers to make the 1M.

[deepakmirchandani]

very useful video sir, thanks .
hope your mom is fine now.

[brianxyz]

Hopefully one day Microsoft will give us smart spilled formatting.

[adhiyasadarojatun]

Hey mike, do you have any tutorial video about how to make table automatically?

[RobertDevine]

@excelisfun I just reviewed this video "FILTER Function to Create Sub Table from Master Table. Data Validation Too! Excel Magic Trick 1842". Thanks for it! I've been learning Filter function. My question is can you take what you have done with this video and create multiple tables below each other that filters the main table for you which allows to add or delete rows as needed for each new table and not spill into the next table? Been going crazy trying to figure out if this is possible. Thanks!