Join Dennis Taylor for an in-depth discussion in this video Increasing your productivity with custom lists, part of Excel Tips Weekly.
- If there's a list of items that you need frequently. For example, here's a list of departments within the company. I frequently need this list so what have I been doing? I open this file and I copy, paste the information. Well, let's suppose I wanna have it available at my fingertips at all times. What if I could simply type in any of the entries, say the first one, like ADC here, and then drag from the corner and get the others automatically. That sure would be great. Can't do it yet. We can do this if we convert the list into what's called a custom list.
And we need only do this once. And when we do this it stays in our Excel settings on this particular computer. It's called a custom list and there's another use for a custom list. Totally different context. I've got a list of entries here that pertain to the order that I'd like to see this list sorted by. In other words, I want to quickly be able to sort this list based on all the Full Time people coming first. Now, if I use an alphabetic sort here it's gonna sort alphabetically. I'll get the contract people first.
So, there are two different kinds of custom lists. They're easy to create. You have to keep in mind that custom lists are part of your Excel settings, they don't travel with the workbook or a worksheet in any sense. So, I've decided that I need this list frequently. I'm going to create a custom list based on this. I'll highlight it first. If I don't have it available I'm gonna have to be prepared to type it. So, it is available right here. We go to the File tab in the ribbon and choose Options.
And then down on the left-hand side amongst these various choices, Advanced. Finally, scrolling nearly to the bottom here you see a choice, Edit Custom Lists. The built in custom list consists of abbreviations for days of the week in full spellings. Same thing for the months here. Those are built in, you can't change them. We're going to add a new list. Now, if we didn't have it highlighted we'd be typing in the panel here that says List Entries but since it is highlighted we can simply import and I stress you need only do this once.
If I click OK it's there, click OK a second time. We don't need this list anymore but we can use it in any workbook at anytime on this computer. Now, suppose I'm looking at a different sheet over here or maybe a different workbook. I need my list. So I can type in any entry from the list. Typically it's going to be the first one. So, maybe I've got a heading up here called Department. Type ADC, I'll drag from the corner and start getting the others. It's about 25 so drag down to there.
If I drag too far it simply repeats. So, I drag to too far, get rid of the repeats. Something to keep in mind here too, and sometimes it's a little bit annoying, maybe I've forgotten one of my department names as operations. In a different context completely, if I've typed in Operations and I wanna repeat that word three or four times, right here if I drag downward I get the other entries from that list. So, you just hold down the control key if you truly want it to have these repeated. It's an exception to a rule of wanting to have this list available.
Now, going back to our custom list worksheet right here, let's now set up a custom list that has to do with sorting. I want these lists over here to be sorted in Full Time, Half Time, Hourly, and Contract order. Remember, as a reminder, if I'm about to sort this data and I click in column H and go to the Data tab, click A7, Contract people come first. I want Full Time people first. Highlight the data. File tab, Options, Advanced, scroll to the bottom, Edit Custom Lists, here's the list I created earlier, I'm going to import the new list.
It's highlighted, it's this list right here, click OK. That's now a part of the custom list option that I can choose when I do sorting. But when we do sorting we can't simply click the A7 buttons we have to go to the full sort button here and we do wanna sort by status on values but not A to Z and we use custom list, there's the custom list we want, Full Time Half Time, OK, OK, and now our list is in that order. So, two different uses of custom list.
One for a list that we frequently need. We want to be able to create it simply by typing in an entry and dragging from the corner. Another use for custom list we want to sort in this order. Now, if I were to remove this data, save the workbook and send it to you, and you use this list, if you want to sort, say when you first get this you sort by Employee Name, at a later time if you want to sort in this kind of order you will not be able to do this unless you create a custom list.
So, custom lists do not travel with a workbook or worksheet, they're part of your Excel settings. But I think you can see in the two examples here we've got some really valuable uses, two different kinds of uses for custom lists.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Increasing your productivity with custom lists