Increasing your productivity with custom lists

- 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.