Be The Master Of Your Excel Lists
Now that you know what an Excel list is, let’s take a look at how to create a list that’s all your own—in other words, a custom list.
You have several options for the creation of a list. One has you type the list into the Custom Lists tab in the Options window and the other allows you to highlight the cells that contain a list already in a worksheet.
So… to cover all the bases, let’s go over both.
The first method—typing into the Custom Lists tab—requires you to open the Options window. (Tools menu, Options choice, Custom Lists tab or Alt, T, O then use the arrow keys to get to the correct tab.)
Now that you’re in the right place, you should have a window that looks like this:
On the left you’ll see the Customs lists already in Excel along with an option for a New List.
Highlight New List and then click into the pane on the right—List entries.
In this pane you need to type the items in your list. (Items in lists cannot start with a number.) Between each item be sure to hit the Enter key.
When you’ve completed your list click the Add button.
Presto change-o! Take a look at the list of lists in the left pane, you should now see your entries at the bottom.
At this point you can either start over to make another new list or click the OK button.
Now, if you go into your Excel worksheet and enter into a cell an item from your list and pull down the fill handle, you’ll find that instead of a copy you get the rest of your list filled in.
Don’t worry—I haven’t forgotten about those of you who have a list already entered into a worksheet.
I’m sure you want to hear me say that there’s a way to make a custom list from the data—without retyping everything.
Good news! I’ve got just the thing to take care of your situation.
The fastest way to get your list in is to first highlight the entire list.
Now go to the Options window, Custom Lists tab. (Tools menu, Options choice or Alt, T then O.)
If you take a look at the bottom of the Custom Lists tab you’ll see a field at the bottom labeled as “Import list from cells:“.
This field should now contain the adress of the highlighted cells.
Click the Import button.
Immediately the data should be added in the Custom lists pane (left) and displayed in the List entries pane (right).
Click the Add button.
And—believe it or not—that’s it… you’ve got the custom list created without retyping the data.
Click OK when you’re done.
(Do a little happy dance—you didn’t have to retype the data!)
And what if you forget to highlight the data first?
Do you have to click Cancel and start over?
Well, I should hope not!
We can do much better than that.
Do you see the little button at the end of the “Import list from cells:” field? (The one that looks like a miniature worksheet with a red arrow.)
You will be taken back out into the worksheet where you can highlight the list.
When you’re done you’ll need to click the small button at the far right of the Import lists from cells window that opened when you jumped back out to the worksheet.
Excel will then return you to the Options window where you can continue by clicking Import, then Add and finally click OK.
Still doing the happy dance?
If you have a lot of repetetive data categories then you probably are.