"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Custom Auto Fill - Cool!

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

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

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.

  1. 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.
  2. Now go to Tools, Options, and select the 'Custom Lists' tab.
  3. At the 'Import list from cells' field, click on the small button with the red arrow to 'collapse' dialog box.
  4. Select the range of cells containing your list and then click that small button again to restore the dialog box.
  5. Click the Import button and then OK
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.

Alternatively, instead of importing your list from the cells, you can type your list of values directly into the Custom Lists dialog box.

  1. In the 'List entries' field, enter each value of your list on a separate line (i.e. press Enter after each entry).
  2. Then click Add and OK.
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.

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.