Most
of
us work with lists of things in Excel that need
to be entered in a specific order such as region
names, department names, job titles, etc...Each time you need to use one of these lists, manually typing them isn't very efficient. You may think, "Simply copying them from an existing workbook is pretty easy". Well, it is...compared to retyping them each time.
But what if I showed you a way that you could instantly create your lists 'out of thin air'?
Excel's Custom List feature makes creating your own custom lists simple.
You've probably already used custom cists in Excel without even realizing it. When you need to enter the twelve months of the year and you type January in the first cell, then drag the Fill Handle to AutoFill the remaining months, that's a 'built-in' custom list. Or if you type Sunday in once cell and drag the Fill Handle to fill in the days of the week, that's also a 'built-in' custom list.
I'll bet when you first discovered those tricks you were amazed, right?
Well it can be just as easy to autofill your own custom lists whenever and wherever you need them. Just one time you'll need to create your list. After that, you can autofill it at any time and in any workbook. There's no need for copying or sorting.
Creating your Custom List
1) First you need to tell Excel what you want in your custom list. So, just enter the values for your list in a worksheet in the order that you always want them to appear. If you already have this list in a worksheet, you can even skip this step;
2) Next select the cells containing your list (only one column wide);
3) From the File tab choose Options, Advanced tab, scroll down to the General section, then click the Edit Custom Lists button;
4) The cells you selected for your list will be shown in the 'Import list from cells' field. Click the Import button and your list will be added to the Custom Lists area. (Alternatively, instead of importing your list from your worksheet, you could click NEW LIST andtype your list of values directly into the 'List entries' field then click the Add button to create the list.)
| Notice the 4 built-in custom lists: (Sun..., Sunday..., Jan..., January...). The A, B, C list is a custom list I created for myself for quickly autofilling the alphabet either across columns or down rows. |
5) Click OK and your Custom List will be saved in Excel, not just the current workbook, and will be available for use in any workbook.
AutoFilling your Custom List
Whenever you want to add your custom list to any worksheet, simply enter the first value from your list into a cell and drag the fill handle. The list will be autofilled into those cells. If you drag over more cells than there are items in the list, the list will repeat over. Actually, you can start by typing any value in your custom list and when you AutoFill, the list will continue from that entry.
Sorting Based on a Custom List
Now that you have a custom list, you can sort existing data into the order of your custom list. From the Sort dialog (Data, Sort), choose Custom List from the Order dropdown in the Sort dialog, click OK, select your custom list and click OK.
In this example, my list happens to be in alphabetical order, however, sorting using a custom list will sort data in whatever order your list is in.
If you found this tip helpful, please share it with your friends and colleagues.
To get more tips every week like this one...
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'
Plus you also get my 'Excel in Seconds' E-book as a BONUS!
(Download it immediately after you sign up)
Copyright Francis Hayes © All Rights Reserved
8 Lexington Place, Conception Bay South, NL Canada A1X 6A2
Phone 709-834-4630
This site is not affiliated with Microsoft Corporation.