Hi. In this video, we’ll go over what an autofill list is, how to create a new autofill list in Excel that you can be reused in different worksheets
and we’ll also go over how to edit your custom auto-fill list.
So, first, what is an auto-fill list in Excel? So, sometimes called an autocomplete list, an autofill list is a list that can be automatically
filled in your spreadsheets simply by typing any of the words contained in the list and then dragging the cells over to complete the list.
For example, I can type the word January into this cell and, as I type it, you see that the bottom right corner of the cell has a slightly
different shape than the other corners. Well, if I put my cursor over this, you'll see that it changes into a +
Well, if I drag this over while I'm holding down my mouse button,
you see that there is a preview that shows you the rest of the months of the year. Now, you can
drag is over and stop at any point. I'm going to keep going until we reach December
and then once I let go, the months of the year autofill in my spreadsheet. And this is an
example of a list of items that we can auto fill in our Excel spreadsheet.
There a couple of different ways that you can create an autofill list, but to do either you'll want to go to your File tab and scroll until you
find Options. Click on Options and this brings you to your Excel Options.
Once here, you'll want to click on Advanced and then scroll until you find the section that reads General. And then once you're here
you'll want to choose “Create Lists for Use in Sorts and Fill Sequences: Edit Custom Lists”.
So we click on this. And this brings up all of the Custom Lists stored in our Excel software.
Once here, you can go on to create your new custom list. So, to do that, make sure that New List is highlighted and simply type your
entries in. So, let's say I want to type in a list of my Items Sold. So I would type Shirt,
Pillows, Pants, Bread, and Eggs. Now, one thing to keep in mind is, your autofill list will always
populate in the order that you type your list in. So, once I do this, I can click on Add,
OK and then we'll click OK again in Excel Options and we should have our new list of
Items Sold. So, to test this, let's delete these Items Sold
and we'll type the word Shirt. This should trigger for Excel that this is part of a Custom List and, if
we want to, we can drag down, and you see how this autofills the items that
we included in our list. Now, one thing to notice, if you continue dragging the list will repeat itself. So if I dragged this down, you see
that it starts over again with Shirt, Pillows, Pants, Bread, Eggs, Shirt again and it would go on forever if I continue dragging.
So let's undo these extra list items that we dragged down. So, another way that you can
create a Custom List is by using data that's already in your spreadsheet. So, for example,
let's say we want to create a Custom List for our sales associates. We can come to the File menu again / Options /
Advanced / we go down to General and we'll click on Edit Custom Lists
and this time, where it says Import List From Cells, we'll delete this because we don't want to do that one,
and let's say we want to import our Sales Associates, or Sales Persons.
So we highlight this area
and once we do that, we'll click Import and this automatically adds my entries as a new List. Now, notice one thing that we did differently
here, and this is up to you how you need to do it for your specific project, is I included Sales Person in my list.
And, not just did I include it, but I included it as the very first item. So, once I do this, this will let me start off my list by typing Sales Person
as the trigger. And, this way, I don't have to remember the names of my individual associates.
So, we'll click OK,
OK again. And, to test this, we'll do the same thing -
we will type
Sales Person
and, once we do this, we'll drag down and this automatically enters the name of my associates.
Just have to unbold that.
Now, we can also make edits to our Custom Lists. So, to do that, we'll go back to our Excel Options location / Advanced / and General.
And when we're in the Lists, you'll notice that you can choose any of these lists. Now, some of these are grayed out. These lists were
already included with your Excel program and you can't make any changes to those, but you can change the ones that you've created.
So, let's say we want to change some of our Items Sold from Eggs to Pasta. Well, I will just delete Eggs and type in Pasta. Now, please
note, I'm deleting the word using the Delete button on my keyboard. If you use the Delete button on
this box, it will delete the entire list for you. So, we type Pasta in,
click OK and click OK again.
And then now if I were to drag this down you see that Eggs changes to Pasta.
And, lastly, don't forget that these Lists that we created can be used with any workbook. So, let's say we want to create a new workbook.
And I type in Sales Person,
for example, I can drag down and, even though I'm in a new workbook, my List is still available to me. So, I hope you found this tutorial helpful
to learn how to create a new Autofill list in your Excel and how to modify an existing Autofill list.
If you enjoyed it, please make sure to give it a Like and don't forget to subscribe to this channel. Thanks!