Excel Power Tips: Useful Techniques for Excel Lists, Drop Downs and Data Validation

// Recommended Excel Courses //
1. Launch Excel Macros & VBA School: go.launchexcel.com/macros-vba...
2. Excel Campus Elevate Program: go.launchexcel.com/excel-camp...
3. My Online Training Hub Dashboards: go.launchexcel.com/moth-dashb...
4. Coursera Excel Specialisation: go.launchexcel.com/coursera-e...
5. Coursera Everyday Excel: go.launchexcel.com/coursera-e...
6. Coursera Excel Data Visualisation: go.launchexcel.com/coursera-d...
// Recommended Excel Templates //
If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.
go.launchexcel.com/simple-she...
// Show Notes //
In this 20 minute episode we'll look at lists, drop downs and data validation. These are very important areas of Excel that you should master if you want to take your Excel skills to the next level.
Read the accompanying article and download the sample workbook at www.launchexcel.com:
www.launchexcel.com/excel-drop...
Video Highlights:
-- Excel Built-in Lists --
0:10 Excel's Built-in Lists (Days of week, Months of year)
-- Excel Custom Lists --
1:06 Three use cases for Custom Lists
3:57 How to create a Custom List (instructions for Excel 2010)
-- Dependent Drop-down Lists (Country, City) --
5:08 Drop Downs for Data Entry
6:47 How to create Dependent Drop Downs with Data Validation and Named Ranges
9:38 TIP: How to define multiple Named Ranges
10:02 The "Slow" Way using the Name Manager
11:24 The "Fast" Way using CTRL + SHIFT + F3
14:09 Why do we need "Helper" columns?
-- Data Validation (E-mail, Date of Birth) --
15:40 Data Validation of Email addresses
18:20 Data Validation of Date of Birth (you must be at least 15 years old)
19:48 Warning: Data validation can be easily overwritten by Copy ➜ Paste and what to do about this

[@StuffOffYouStuff]

I loved the way you went to the trouble of explaining WHY you used the helper columns and actually DEMONSTRATED what happens if you don't use them! Very insightful vid, thanks.

[@winnie71]

This tips are exactly what I'm looking for !!
Thanks.

[@sasavienne]

A great tutorial. Thanks a lot. 👍 🌟 🌟 🌟 🌟

[@darshanajayasundara720]

Thank you for the sharing knowledge.
This is helpful.

[@erikagoldston7149]

WOW! That is so cool.

[@eduardonavas6160]

That was some great info, really appreciate your assistance!!! THANKS A LOT!!! I think I got It!! :P

[@Abolwafa27]

Great Vid.

[@bryonlim3183]

Thank very much for sharing.

[@Abolwafa27]

Excellent, enjoyable, and very useful video. Million thanks.

[@karthikaprabu7999]

Really helpful, and Well explained.. Thanks.

[@dugite7902]

The only reason Puteaux, Osnabruck, and Pune were treated as Top Row of separate section when using Goto Special "Constants" is because those cells were formatted differently from the others holding city names. Format those three cells the same as the others and Goto Special "Constants works fine, so there really is no need for the blank helper columns. Besides that, a very useful tip for quickly creating lots of defined names (albeit NOT dynamic names).

[@BasedTeeth]

Good video!

[@dukestt]

i cannot tell you now many conversations i have on a daily basis with staff members telling me that they can't enter the information. it drives me mental. until i put data validation on the system it was a mess. if you wanted to take this one slighty further you could talk about the offset function for the lists. great video!!

[@launchexcel]

Hi emeritus 1esc - thanks for the comment. Yes the formula EDATE(TODAY(), -180) is very nice. Thanks for the tip!

[@magdamagda30]

thanks!!!

[@pradipekashinge4440]

it shows Excellent tips related to Excel.

[@AntonioGRomera]

Excellent video!!! I was looking for the fastway to manage range names. There's only 1 con in the fast way process, and it's that when you fill many cells with ="" the file size increase too much, so you have to get rid of the high ammount of ="" formulas.

[@Anwarzadauet]

Good Job...

[@emeritus1esc]

This is an excellent video. Thank you. My only comment is that the end date formula could be simpler, e.g., =EDATE(TODAY(),-180)

[@kwabenaappiah]

THANKS DEAR I LOVE IT NOW OFFSET

[@syedahmed1152]

loved your video. one of the best i found on you tube. by the way, i am kinda new when it comes to vlookup. how did you connect countries with cities? i am in sales and making excel file to keep countries with weight and charges. apprecaite your advice. thanks

[@giorgitsomaia9832]

thank you very much, it is very useful video, but I want to suggest one way in the naming ranges, have you tried =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1) this kind of formula? it really helps in case of adding cities of deleting them.

[@unitedcomputers]

It seems that the email validation formula only checks for one dot...?

[@victoriarange6162]

thanks for sharing...OMG...who would have thunk it...I think I got it...lol

[@avinashsatnam3707]

In your video you are showing only 2 columns, what if we need to create a more than 5 columns to get data in the last column based on the criteria of first 5 columns??? For example; Continent, Country, State, City, Area, Pin code? Would be great if you can throw some light on this.

[@alraz440]

How do I create 3rd drop down column for the county? country, county and town. Thank you,

[@SBelektronics]

The principle is the same, if you want to create 3rd drop down list. Here you created column with countries names, then you divided countries in separate column and cities were added. In your case you have to create each country, county and town columns in a same way. Define name ranges as in video. Data validation source: for COUNTRY choose "Country column" , for COUNTY =INDIRECT("country cell"), for TOWN =INDIRECT("county cell")....into bracket put cell address of course!

[@HUSTLINISMYGAME]

how do you link data from another worksheet?

[@appliedprodigy6415]

thank you now i ca get a good level in ict in year 10 thanks lol

[@emilynguyen3236]

how can you search for a particular item in a drop box with 1,000 items? any quick way to do it?

[@unitedcomputers]

For example, an email address of [e-mail] would not be recognised as invalid

[@user-bs2xv6tg6f]

I didn't understand how at 9:32 you got the formula written in the source box. Could someone explain? I don't have a lot of background in excel and am learning through this presentation...Thanks

[@launchexcel]

For help on the functions used, check out office.microsoft.com/en-sg/excel-help/indirect-function-HP010062413.aspx and office.microsoft.com/en-sg/excel-help/substitute-function-HP010062578.aspx

[@julesg1331]

The formula for Email and Date of Birth did not work...can you paste here what was the forumula you used?

[@khunjohn3384]

Oh, Sir, sorry I do as you show on the screen but I could not do it " Why! I could not do as you do" let me know your suggestion about it.

[@Ali-lf2hl]

where did you get that are you kidding your order does not work !!!