By David Ringstrom, CPA
By its very nature as a spreadsheet, it's easy to create a series of numbers in Excel. For instance, you can enter the number 1 in cell A1, hold down the Ctrl key, and drag the fill handle in cell A1 down to create an instant series of numbers. For the uninitiated, the Fill Handle is the little black notch in the right-hand corner of the active worksheet cell. Regardless, most users don't realize that you can configure Excel to create a series of letters in a similar fashion.
First we need to create the list itself. If you like, you can type the letters A through Z down a column or across a row. But, there's an easier way:
1. In a blank worksheet, press F5 to display the Go To dialog box.
2. Enter A1:A26 in the Reference field, and then click OK.
3. Type the following formula in cell A1, and then press Ctrl-Enter:
=CHAR(64+ROW())
Important: Make sure that you press Ctrl-Enter; otherwise, you'll have to manually drag or copy and paste the formula in to cells A2 through A26.
4. Press Ctrl-C to copy cells A1 through A26 to the clipboard.
5. Right-click on cell A1, choose Paste Special, and then double-click Values.
6. Access the Custom Lists feature:
- 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.
7. Click the Import button within the Custom Lists window to add your list to Excel.
8. Click OK as needed to close any open dialog boxes.
Now that you've completed this one-time series of steps, you're ready to test your work. Type the letter A in any worksheet cell, and then drag the fill handle down or across to complete the series. You can use this technique with any Excel workbook on your computer. Custom Lists are specific to each computer, so you'll need to repeat the above steps on any other computers that you use.
There are a few other nuances you should be aware of:
- 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())
Some cells will appear blank or show a question mark. These represent non-printable characters, such as tabs and carriage returns.
- 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 .
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 david@acctadv.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
