Do you repeatedly have to enter the same list of values (i.e.
city names, product names, etc...) in various workbooks?
I know what you're thinking, 'I can just copy the list from an
existing workbook.' Sure, you can do that but then you have to
find and open the workbook every time you want to create your
Wouldn't it be cool if you could instantly create your list 'out
of thin air'?
Excel's Custom List option will let you do that. You probably
already use Custom Lists in Excel. For example when you want to
enter the twelve months of the year, you enter January in the
first cell and then drag the fill handle to complete the
You did know about that, didn't you? I thought you did.
Well it's really easy to create any list of values you want to
work the same way.
Now, whenever you want to add this list of values to a worksheet,
simply enter the first value from your list into a cell and drag the fill handle
to complete your list.
- First you need to tell Excel what you want in your list. So,
enter the values of the list on your worksheet, in the order
that you want them.
- Now go to Tools, Options, and select the 'Custom Lists' tab.
- At the 'Import list from cells' field, click on the small
button with the red arrow to 'collapse' dialog box.
- Select the range of cells containing your list and then click
that small button again to restore the dialog box.
- Click the Import button and then OK
Alternatively, instead of importing your list from the cells, you
can type your list of values directly into the Custom Lists
For those of you who don't know what the Fill Handle is, it is
that small black square on the bottom right-hand corner of a
selected range of cells. When you position the cursor over the
Fill Handle, the cursor changes to a 'plus sign'. Simply click
and drag to fill a range of cells.
- In the 'List entries' field, enter each value of your list on
a separate line (i.e. press Enter after each entry).
- Then click Add and OK.