Creating a Series of Letters in Excel
by David Ringstrom on
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:
- 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.
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 email@example.com or follow him on Twitter. David also presents monthly Excel webcasts for AccountingWEB partner CPE Link.
You may like these other stories...
In the old days, we used to tape down receipts from our travels and submit them to accounts payable. But that was before remote employees who may live in a different city from the home office. And of course, there's all...
In 2011, electrical services and technology provider Parsons Electric in Minneapolis, Minn., decided to take its accounting to the cloud. Monica Ross, the company's director of strategic projects, talked with AWEB about...
Event Date: July 24, 2014, 2 pm ET In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the...
Upcoming CPE Webinars
Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
FRF for SMEs Series--Statement of Cash Flows, Subsequent Events, Related Party Issues, Accounting for Investments including Consolidations, Part 4A
This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.