Browse All Articles

Or filter by News, Views or Tips.

Excel: Custom Lists

Today we are going to look at creating lists that you can then use with the AutoFill handle to quickly enter into your spreadsheet. This will save you having to type a list of customers, departments, towns that you use on a regular basis.

The AutoFill handle is the little square at the bottom right hand corner of the highlighted cells, which you drag to copy down the column or across the row. If you have tried using this with days of the week or months, you'll know that it AUTOmatically FILLs in the rest of the sequence. This is because Excel has them stored as a list, and you can create your own custom lists.

With your own custom list you'll be able to type in one entry from that list and when you then copy it using the AutoFill handle, the rest of the list will be filled in.

To Create a Custom List

  1. Open Excel.
  2. Type in your list (each entry must be in a separate cell and make sure they are in the correct order).
  3. Highlight the list.
  4. Give the command TOOLS - OPTIONS - CUSTOM LISTS
  5. At the bottom of the dialog box there will be a field that shows the range you have highlighted.
  6. Click the IMPORT button.

Your list is now on your system. Test it by typing any entry of your list and copying it with the AutoFill handle.

If you look back at the CUSTOM LISTS you'll see that there are separate lists for the months in full and the abbreviations, so if you want abbreviations of your list too, you'll have to add a second custom list.

Sorting in Custom List Order

If you have a list of information (database) you can sort it into your Custom List order as follows:

  1. Click anywhere in the list, so that only one cell is highlighted.
  2. Give the command DATA - SORT.
  3. At the bottom of the dialog box, make sure MY LIST HAS HEADER ROW is selected.
  4. In the first Sort by box, select the heading for the column that you want to sort by.
  5. Click the OPTIONS button.
  6. Under FIRST KEY SORT ORDER it should say Normal (ie alphabetical or number order). Click the drop-down arrow and you'll get a list of all your Custom Lists. Select the appropriate one.
  7. Click OK, and then OK again to sort your database into the order of the Custom List.

NB: You can use this technique to sort by Month or Day when you have not used dates.

Custom lists are a very useful feature when you are often using a fixed list to enter information into Excel. It can save a lot of typing.

← Go back to Articles


Post new comment

Your email address will be kept private.

It’s Easy to Follow Us