Microsoft Excel has some nice features that can save repetitive typing. One that people are familiar with is using a fill series for continuous cells. These are often common values like months or days of the week. This feature is called an Excel Custom List and can be used for a creating repetitive data series that you might use in the office or at home.
Probably the first Microsoft Excel feature I valued was allowing the program to prefill cells for me. I could type January, grab the fill handle and extend the range to fill in the rest of the year. I didn’t have to type February, March and so on. I could do the same thing with days of the week and even omit the weekends.
What was equally appealing was I could go horizontal or vertical. This made building standard report so much easier as I didn’t have to type these cell values or get locked in by a template orientation even though I could transpose columns if needed. I also could use these lists on any spreadsheet.
While these lists work well, sometimes you have a series that Excel doesn’t recognize. For example, if you have a series of sales offices in Boston, Dallas, London, Madrid, and New York. Excel would not know they are a series and the next item should be Paris. The solution is to define a custom list in Excel with these values.
How to create an Excel Custom List,
- Type in the values for your list. I chose to use Column A, but you could use a Row.
- Highlight the items in your list.
- From the File tab, select Options. It’s at the bottom.
- Under Excel Options click Advanced.
- Scroll down to the General section.
- Click the Edit Custom Lists… button.
- Click Import. Your initial values will show under List entries.
- Click OK.
- Click OK again on the Excel Options panel.
The Customs Lists dialog will open. You should see your highlighted data range by the Import button.
How to fill cells with your custom list,
- Type your first value such as Boston into a cell
- Click the cell so the border shows.
- Move your mouse over lower right corner till the + appears
- Left click and drag over the cells you want to be filled. You can go down or across.
As you drag the fill handle, you should see labels showing the series values.
Once you start using this feature, you’ll start looking for more examples. The key is finding lists that you use with frequency. Some ideas for using an Excel custom list might include:
- geographic regions
- company divisions
- sports teams
- product categories
Want More Tips & Tools?
Several times a month, we also publish a free newsletter with relevant stories, tips and special offers.