Embed Frequently Used Lists into Excel
As you're most likely aware, it's easy to instantly create a list of days of the week or months of the year. Simply type either the first three letters or the full name of a day or month in a worksheet cell, and then drag the Fill Handle down a column or across a row. What you may not be aware of, though, is that this is part of the Custom Lists feature in Excel, and that you can add your own lists to use in the same fashion, as I recently noted in our AccountingWEB forum.
The initial steps differ by version, but once you get used to the feature, everything else works the same. Before we begin, open a spreadsheet that contains a list you'd like to store, or create a list such as shown in Figure 1, and then select the cells within the list. You might even use the letters in the alphabet since Excel doesn't make it easy to create a series of letters.
The next steps depend upon your version of Excel:
- Excel 2010 and later: Choose File, Options, and then click on Advanced. Scroll all the way down to the bottom and then click Edit Custom Lists.
- Excel 2007: Click the round Office button in the top left-hand corner, choose Excel Options, and then in the Popular section that first appears, click Edit Custom Lists.
- Excel 2003 and earlier: Choose Tools, Options, and then click on the Custom Lists tab.
- Excel 2011 for Mac: Choose Excel, Preferences, and then Custom Lists in the Formulas and Lists section.
In any version of Excel, click the Import button if the cell coordinates for your list already appear in the Import List from Cells field shown in Figure 1, otherwise either type in the coordinates, or click the button in the field and select the cells.
Figure 1: Quickly import a reusable custom list.
Once you've added the list, you can edit it or delete it at any time. To do so, select the list as shown in Figure 2, and then click the Delete button within the Custom Lists dialog box. You can also make manual edits to a list in a similar fashion—once you select the list, simply make any adjustments necessary in the column to the right. You can press Enter to add blank lines for adding new items, or use the Backspace key to remove unwanted items. Be careful to only use the Delete button when you want to remove an entire list. The Add button will save any changes you've made to a list, but you should only have to use it when you need to make changes to more than one custom list. If you're editing a single list, you can save a step and click OK to save your changes and close the Custom Lists dialog box simultaneously.
Figure 2: You can revise or delete your list in the Custom Lists dialog box.
At this point you can now type any item from your list, and then drag the Fill Handle down or across, as shown in Figure 3. You don't have to start with the first item in the list either—Excel will pick up the series at whatever point you choose. Do note that the list does repeat over and over again if you drag the Fill Handle through more cells than items available on the list. In some cases, though, you might create a series of items when you want to simply copy the contents of a cell instead. In such cases, click the AutoFill button shown in Figure 3 and then choose Copy.
Figure 3: Simply type any item from your custom list, and drag the fill handle to populate the list.
If the AutoFill button doesn't appear, you can restore it:
- Excel 2010 and later: Choose File, Options, and then click on Advanced. Scroll to the Cut, Copy, and Paste section and then select Show Paste Options button when Content is Pasted.
- Excel 2007: Click the round Office button in the top left-hand corner, choose Excel Options, and then click on Advanced. Scroll to the Cut, Copy, and Paste section and then select Show Paste Options button when Content is Pasted.
- Excel 2003 and earlier: Choose Tools, Options, and choose Show Paste Buttons on the Edit tab.
- Excel 2011 for Mac: Choose Excel, Preferences, and then Edit in the Authoring section. Click Show Paste Options Smart Buttons.
Note that any custom lists you add become part of the Excel environment on the computer you're presently using. The lists are not tied to any particular workbook, and if you wish to use the lists on, say, your home computer or laptop then you'll need to import the lists on any other computers you use.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.