Creating a Series of Letters in Excel

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:
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: 
  1. In a blank worksheet, press F5 to display the Go To dialog box.
  2. Enter A1:A255 in the Reference field, and then click OK.
  3. 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.


Read more articles by David Ringstrom.

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 or follow him on Twitter. David also presents monthly Excel webcasts for AccountingWEB partner CPE Link.

You may like these other stories...

Whenever I speak to accountants about creating a cloud practice, the most common question is, “How do I charge my clients?” Ten years ago, maybe even five years ago, if I would’ve posed this question...
While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...

Upcoming CPE Webinars

Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.