Creating a custom list is useful if you have a list that you frequently need in your worksheets. For example, you could have a list of all your products stored as a custom list. Every time you need the list in a spreadsheet, you just need to type in one item from the list, usually the first item, and then drag the + sign in the lower right-hand corner of the cell to automatically fill in the rest of the list.
Create a custom list
Select Excel > Preferences.
Select Custom Lists.
Select Import list from cells.
Select and drag through the cells containing the list.
Select Import, and close the dialog box.
Using a custom list
Select a cell
Type the first word of the custom list that you created.
Drag the + sign in the lower right-hand corner of the cell and drag it downward.
Note: Sometimes, you might want to type a word and drag the + sign in the lower right-hand corner of the cell to repeat it a few times. However, if that word is a part of a custom list, you'll get the other entries from the custom list. To avoid that, hold down the Option key, and then drag the + sign in the lower right-hand corner of the cell to repeat it a few times.
Sort data using a custom list
Note: As always, with sorting, make sure your list has no empty rows or columns within it.
In the Sort pop-up box, under Order, select Custom List.
Select the custom list entry of your choice.
Select OK > OK.
We frequently need this list, and we want to be able to at any time, in any worksheet, without this workbook here being open, we want to be able to easily create this list simply by typing in one of the entries.
So this list is highlighted, if it's not, or if you don't see it on your screen, be prepared to type all of this.
Let's hope you don't do it that way. So here's our list.
Then go to Excel, in the top menu, Preferences, then here you will see Custom Lists.
Now the built in Custom lists, the ones that are already there that you cannot delete, are based on abbreviations for days of the week and their full spellings, and abbreviations for months and their full spellings.
Then click on the panel below, Import list from cells, and I'll just click and drag across these cells right here. The dialog box automatically collapses. Click Import.
And now we see that list is in the Custom lists here.
Now once we close this dialog box, that list is going to be there indefinitely.
We don't necessarily need to keep it here.
I'm not going to delete it just yet, but that's available to us at any time.
So testing it first on this worksheet, I'm going to click over in column T, any cell, I'll type ADC, point to the lower right hand corner, start to drag downward, and look at the pop-up tip to the right.
I'm not going to be copying ADC over and over and over again.
I'll be copying the other data.
Now if I'm in kind of a hurry I'll drag it down fast, and maybe I dragged it too far, that's okay.
It will just start repeating at some point.
Starts repeating right there. Don't need that. Get rid of it.
Over time I'll do that probably more efficiently because I know it's got about 25 entries or so.
Nevertheless, it doesn't hurt to drag too far.
Now you might forget at different times about the different department names.
The word Operations, of course, is a word we might use in another context.
So maybe at a different time, different worksheet, here's the Profits worksheet for example, maybe at a different time here you happen to be typing in, and you're not thinking of department names, you happen to type in Operations.
And you want that repeated a few times maybe.
Drag across and what happens?
You start getting the other entries. And you say, "Oh yeah, now I remember, that's part of a custom list."
So what do we do here? Hold down the Option key, if you like to have this repeated, either rightward or downward or in any direction.
This list has been created. It's now a custom list.
That will stay in my Excel settings indefinitely until I possibly get rid of this. Maybe I won't. Maybe I'll keep it forever.
Here's the other list, much shorter.
Maybe over time, we might have a few additional kinds of status.
That's fine, we can adjust that later.
But for now, we've got this list. We want to use this as a basis for sorting.
We've got these cells highlighted, and once again, we'll go to the Excel menu, choose Preferences, Custom Lists.
There's the lists that we saw before, now of course, it's been augmented by the additional list that we just created.
So Import lists from cells, click on the panel at the bottom.
It will highlight those cells right there, and Import. There we are.
We now have those four entries as a custom list as well.
We'll close the dialog box. Now over on HR List, just one worksheet to the right, we want to sort this data by status.
We can't use the individual AZ, ZA buttons, but we can use the larger button AZ Sort.
As always with sorting, make sure your list, and I knew ahead of time, this list has no empty rows or columns within it.
It's a unit. It's an entity. It's intact, so we can sort pretty quickly here just by clicking inside of it, going to the larger AZ button here.
We do want to sort this list by Status.
We don't want it in A to Z order, however.
So we've got a drop arrow here, nor do we want it in Z to A. We want it to refer to a Custom List. And here are all of our Custom Lists entries.
We might have to scroll to find it, but there it is, Full Time, Half-Time, Hourly, Contract. I want to sort my way.
This is not alphabetical, nor is it reverse alphabetical, OK, and then OK, and the list is sorted with Full Time first.
Now if I save this file and send it to you, and maybe you sort this list by employee name, if you later want to sort it in this order, you will need to set up a custom list.
Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.
Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.
Get unlimited access to over 4,000 video courses.
Receive personal recommendations based on your LinkedIn profile.
Stream courses from your computer or mobile device.
Take courses for every level – beginner to advanced.
Practice while you learn with quizzes, exercise files, and coding windows.
Choose a plan for yourself or your entire team.