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.