Tag Archives: lists

Excel Hints and Tips

Custom lists in Excel

If you type ‘Monday’ into any cell in Excel and then click and drag the chunky square fill handle on it’s bottom right down, you’ll notice that Excel automatically recognises that you’re using weekdays and, when you let go, et voila, the days of the week are filled into the cells you highlighted:-


Handy stuff!  Excel has some default lists built in for days of the week and months of the year, which in most cases would be all you need, but what if you’ve got lists of your own that you use regularly?  You’re in luck!  Excel allows you to roll your own.

In this example I want a custom list of seasons which I will use in a gardening spreadsheet.  So, when I type in ‘Spring’ and use the fill handle I want ‘Summer’, ‘Autumn’ and ‘Winter’ (if in the UK) to follow.

Go to Tools>Options and select the ‘Custom Lists’ tab from the dialog:-

As you can see, the existing lists appear in the right hand list box.  At the top of the box is an entry called ‘NEW LIST’.  Select this and click in the ‘List entries’ box to its right, and enter your new list, pressing enter in between each entry until you get something looking like this:-

And now, if you click the ‘Add’ button on the right, your new list will be created and will appear in the ‘Custom lists’ box:-

Easy, isn’t it?  Now click OK and you can start using your new custom list as you did with the weekdays earlier:-

And that’s your new custom list created!

This is really useful if there are lists you regularly use in your daily business, and can really save you time.  I’m not sure if you noticed, but at the bottom of the Custom Lists dialog, there is also a control you can use to refer to a range of cells which contain a list, which you can then Import by clicking the button.  Give that a try as well -it makes things a lot quicker, especially if you already have a long list available and don’t want to type it in again.

PS – apologies for the lack of posts recently.  Things have been picking up at work lately and time hasn’t been readily available.  More stuff to come soon.