Skip to content

Autofill and Custom Lists in Excel

In this issue we’ll look at Excel’s ability to automatically fill your cells with automatic and pre-defined lists to save you typing.

AUTOFILL AND CUSTOM LISTS IN EXCEL

In this issue we’ll look at Excel’s ability to automatically fill your cells with automatic and pre-defined lists to save you typing.

Susan G writes from Ohio, “I’ve seen ‘tips’ articles that talk about custom lists in Excel, those articles sound great but are all the same. They use the same example of a list of staff members. But when I try the feature it doesn’t seem so great.

Peter, could you spare a few moments to explain the real story of custom lists? What they are good for and what are better done in other ways. In other words your usual coverage with more practical help for us instead of thinly disguised advertising for Office.”

Faced with that kind request and challenge, how could I refuse a lady’s request?


AUTOFILL

All recent versions of Excel are able to fill in a sequence of cells based on the few you’ve typed.

For example, enter 1 into a cell then 2 into the one next to it. Select both then drag that selection (using the little box in the bottom right corner of the selection) across. Excel will fill the new cells you select with the numbers 3, 4 ,5 and so on.

It gets even cleverer – type in 2 in one cell and 4 in the next, extend the selection as you did before and the numbers are 6, 8, 10 etc. You can enter any two numbers and Excel will try to guess the series:

10, 20 will continue with 30, 40, 50 etc 5, 10 continues 15, 20 etc

It also works in reverse:

100, 95 continues 90, 85 and so on into negative numbers if you wish.


AUTOFILL FOR TEXT

This same idea extends to other non-number lists like Days of the week and months.

Type in Jan in one cell, Feb in the next and extend those cells out to see Mar, Apr, May appear. This also works for the full name of months, starting with January.

CUSTOM AUTOFILL

Going to Tools | Options | Custom Lists lets you see where Excel has the days of the week and months listed in long and short form.

You could alter those lists if you wanted to though I can’t imagine why. Perhaps you have a really autocratic boss who has decreed that Wednesday is to be named after him instead of Woden ?

This is also where you can add new lists to suit your particular needs. Click on New List type in your list entries on the right side, finish with Add to include it in the list of Autofill lists.

To edit an existing list, click on that list summary on the left side then the whole list will appear on the right for editing.

The common example used of custom lists is a list of staff or team members but that’s probably not a good practical example because a list of staff can alter.

Custom lists are probably better for lists that are unlikely to change but you use regularly. Some possibilities are:



  • List of family members – Mum, Dad, Belle, Claudia etc.
  • Floors in a building: Basement, Car Park, Ground, Mezzanine, 1, 2 etc.
  • Decks on a ship (where the decks have names): Mozart, Puccini etc

In the spirit of the season you could have a list of apostles, books in the old or new testament or reindeer in the famous poem.

IMPORTING A CUSTOM LIST

On the custom list dialog there’s an option to import a list from a series of cells. This gives you an easier way to create and manage a list.

Type in your list into a column of cells, then go to Tools | Options | Custom Lists and then the import button. Select the range of cells then Import to bring the list into a new custom list.


REORDERING A LIST

There is no way to directly reorder a list, all you can do is cut and paste the list into the order you want.

Or you can autofill the entire list into a worksheet, edit or reorder the list using normal Excel features then import it into Custom Lists to replace the original list.


MULTI-LINGUAL LISTS

You can also add lists in different languages. You may need to insert days of the week in French, German or some other language. Since Excel inserts only the lists applicable to your default language, you can add other ‘parallel’ lists in other languages.

LIMITATIONS OF AUTOFILL

Autofill is a nifty idea but, like too many other features it really doesn’t go far enough.

Most obviously, the lists are not dynamic. They cannot be linked to a series of cells or other data source. Therefore you have to remember to change the custom list as circumstances change.

In addition once you have autofilled a list into a worksheet, that list doesn’t change if the source custom list changes. The commonly used ‘staff members’ example is a good instance of this limitation. If a staff member is replaced, your existing custom lists in worksheets won’t change automatically (which may, or may not, be what you want).

If you forget to change a custom list, inattentive staff might accidently use an outdated list which could have annoying or more dire consequences.

Tip: If you have a lot of custom lists, create a worksheet that has all the lists in a series of cells. This makes for easier reference, changing and updating the custom lists as necessary.

 

About this author