Excel - AutoComplete for Drop-down List - New Feature Coming to M365

Intro Hello, I'm Chris Menard. Excel keeps rolling  out the great features for Microsoft 365 users.   Regardless of the Excel version you're on now,  you can do an auto complete and you can do   a dropdown list. If you don't know how  to do those, I'm going to cover them   in just a second. But Microsoft 365 users  will soon be able to use auto complete with   the dropdown list. And I'm also going to  show you a great, great keyboard shortcut. This feature, I'll be covering this when I go  to Administrative Professionals Conference to   speak in Orlando, Florida in September  at Disney's Coronado Springs resorts.   If you're an admin professional and heading  the conference, feel free to drop by and   say hello if you don't make my session.  Let's dive into this video right now. AutoComplete in Excel So the first thing I want to cover is auto  complete, which everyone has in Excel.   I have a list of customers listed here. Notice the  city in column D. I'll zoom in just a little bit.   When I go to add a new customer and I come in here  and hit the letter N, it knows that I've already   typed in New York once, that's why it says New  York. That is auto complete. If I hit the letter   C, it wants to do Chicago. Now, when I hit the  letter A, I have Atlanta, but I also have Athens,   so it's not sure what to do. So if you just type  a couple more characters, it will figure it out. Great Keyboard Shortcut I promised a really cool keyboard shortcut. This  is probably in my top 10 keyboard shortcuts, Alt   down arrow. There are all the cities  I have, but it internally sorted   all the cities and I can see them here and  I can just go pick one. Alt down arrow. Now the drop down list is  also called Data Validation.   Dropdown list Everyone has that in their Excel version too,  so that's not new. It's going to be the auto   complete with the data validation. But let  me show you how to create a dropdown list. I like to keep my dropdown  list on a separate worksheet.   So I'm going to end up putting departments here  on this worksheet, but on a separate worksheet,   I went ahead and typed in the departments  just to keep this video short or shorter.   Departments, that is my header  row, I did make it bold.   Make it a table, Control T. My table has headers,  hit OK, so now that it's a table, that's perfect. Come over to sheet number one. Here's  how you create the drop down list.   Highlight your range, where you're going  to type, go to the Data tab at the top,   you've got something called Data  tools, group, Data validation.   When you open up Data validation it's  telling me you can type in any value.   What's so cool about a dropdown list is, if  someone types an Accounting and spells it out   great. But then the next person does Acct period  to abbreviate it. And now I've got Accounting   technically in there twice, and I got to go  clean up the data. I don't want to do that. So instead of any value, I'm going to make  this a list. Where's your data validation   list at? I've got it on this worksheet called,  Departments. And there they are. A2 through A10,   click on OK. Perfect. Again, you can do this  in any version of Excel. When I click here,   there's Accounting, Marketing, I'm  going to do one more, Purchasing. Great. Here is the new feature for Microsoft  365 users that you'll have soon.   I press the letter A and it looks inside my  data validation list and finds Accounting.   AutoComplete w/ Dropdown list How about that? Cool. If I hit the letter  I, it wants to put in IT, nice. I like that.   If I hit P, it pulls up every department  that starts with the letter P. This is a huge time saver, because I can either  arrow down and click on one or just keep on   working. Before when you typed in something, it  didn't show you just that specific subset of data. Really cool feature right there. Also, I encourage  you to make your data validation or drop down list   Sort Data Validation List a table, because if you add stuff on here, it'll  pick it up on the other sheet, if it's a table. Also, I may want to come in here and sort  this because if you notice, they're not   alphabetical, I want to do a to Z. Now  when I come over here, they're also A to Z. I hope you enjoyed this video. Feel free to  join me at one of my public speaking events.   Have a wonderful day, subscribe, ring the bell. Thank you.