By David Ringstrom, CPA
- Excel 2003: Choose Tools, Options, and then click the Custom Lists tab.
- Excel 2007: Click the Office button, choose Excel Options, and then click the Edit Custom Lists button.
- Excel 2010 and later: Choose File, Options, and then Advanced. The Edit Custom Lists button appears at the bottom of the Advanced options.
- Excel: Mac 2011: Choose Excel, Preferences, and then Custom Lists.
- At the start of this article, I instructed you to hold down the Ctrl key while you dragged to create a series of numbers. Don't hold down the Ctrl key while accessing a custom list, because you'll end up copying the initial letter instead of creating a series.
- If you use the CHAR() function, make sure to convert the formula to values as we did in steps 4 and 5. The Custom Lists feature can only import actual text and not text shown by way of a formula.
- Windows and Macintosh computers have a character set of 255 characters. An uppercase A is character number 65, while lowercase letters start at 97. We accessed the numeric equivalents by using the ROW() function to add the current row number to 64. For lowercase letters, you'd replace 64 with 96. If you wish to see the entire character set, carry out these steps:
- In a blank worksheet, press F5 to display the Go To dialog box.
- Enter A1:A255 in the Reference field, and then click OK.
- Type the following formula in cell A1, and then press Ctrl-Enter: =CHAR(ROW())
- Ctrl-Enter is a keyboard shortcut for filling multiple cells at once. By using the Go To dialog box, you're able to preselect the desired cells, and then type the formula once. If you press Enter, instead of Ctrl-Enter, only the first cell will get filled.
- You can store any type of text-based list you use repeatedly. If you'd like to store a series of numbers as a custom list, you'll need to add an apostrophe before each number to convert the numbers to text. The Custom Lists feature doesn't allow you to embed numeric values.
- In step 5 above, I instructed you to double-click on Values. You can use this trick in many of Excel's dialog boxes to skip the OK button. Double-clicking an option within a dialog box signifies that you've made your final selection and wish to close the dialog box.
A previous version of this article first appeared on www.accountingweb.com .
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@example.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link