Software

Using Excel's fill handle to create an alphabetic list

Microsoft Excel's fill handle tool won't create an alphabetic list by default, but there are a few tricks you can use to make it work.
If you've tried to create an alphabetic list using Excel's fill handle, you already know that the fill handle can't handle the task. For instance, if you enter A, B, and C, in adjacent cells and then use the fill handle to fill in the rest, it just copies A, B, and C, over and over. Fortunately, there are three workarounds for forcing the fill handle to produce the list. Create a manual list

You can enter A through Z yourself. Then, select the cells that contain the letters and use the fill handle to copy the entire alphabet as many times as required. This way isn't so hot, but it'll work.

Use a formula

You can use the CHAR() function to produce the appropriate letters, but this function uses integer as an argument - you can use the fill handle to produce both. First, let's tackle the list of integers that the CHAR() function will use as its arguments.

  1. Enter the value 65 in any cell - CHAR(65) will return A. (Start with 97 if you want lowercase letters.)
  2. Select the cell that contains the value 65 and hold down the [Ctrl] key.
  3. Pull down the fill handle (the little plus sign at the bottom-right corner of the cell). As you move down, Excel will display values for each cell that you add to the selection. Stop when you reach 90.

Now, enter a CHAR() function that refers to the cell storing the integer 65. The function will return A. Next, use the fill handle to create your alpha list.

If you use the handle to copy the complete list, as is, you'll get a bunch of errors. Before you can copy the list, you must convert the list from functions to values as follows:

  1. Select and right-click the list.
  2. Choose Copy from the resulting context menu.
  3. Right-click again and choose Paste Special. Check the Values setting in the Paste section, and click OK. Or, press [Alt]+E+S+V, and press Enter. I find the keyboard shortcut a bit awkward, but you might prefer it. In Excel 2007 and 2010, press [Alt]+H+V+S+V and press Enter.

Once the alphabetic list consists of characters and no functions, you can use the fill handle to copy it.

Create a custom list

Either of the above methods works well for a one-time task. If you think you'll need an alphabetic list again, consider creating a custom list. First, create an actual list from A to Z using either of the above methods. Then, do the following:

  1. Select the list.
  2. Choose Options from the Tools menu and click the Custom Lists tab. In Excel 2007, click the Office button and then click Excel Options. Click Popular in the left pane and then click Edit Custom Lists. In Excel 2010, click the File tab and then click Options under Help. Select Advanced in the left pane,  and then click Edit Custom Lists in the General section.
  3. The Import List From Cells control will display the list range. In this case, it's $B$1:$B$26.
  4. Click Import and Excel will display the custom list.
  5. Click OK.

To use the custom list, simply enter the letter a or A and use the fill handle to complete the alphabetic list. Excel is smart enough to fill in lower or uppercase, accordingly.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
piniyallin
piniyallin

where can i find the matching numbers for Other Languages ?

dhays
dhays

It would be of more use to me to be able to sort alphabetically when using the regular alphabet, then double letters and triple letters (A-Z, AA-AZ, AAA-ZZZ) without the computer sorting as A, AA, AAA, B, BB, BBB ...) I want it to see the single letters, then the double letters then the triple letters. I end up doing it by hand, which is good exercize for the brain, but time consuming.

Ulodesk
Ulodesk

What do the numbers 65 and 97 have to do with upper and lower case letters, respectively? Please explain or offer a reference for those of us who don't use Excel constantly.

ssharkins
ssharkins

Thanks for mentioning this -- I don't use this shortcut, somehow it feels awkward to me, but thanks for mentioning it. I know a lot of users love this one!

jbenton
jbenton

enter the formula "=CHAR(ROW(A65))", then fill that down as far as needed (if you want a row of letters then enter "=CHAR(COLUMN(BM1))" and drag to the right) with that selection still current, right-click and drag away-and-back-again before releasing the right button, select "Copy Here as Values Only" from the shortcut menu

dogknees
dogknees

Assuming you start in cell A1. Add the formula "=len(A1)" to B1 and fill down. Then sort first by the formula column and second by the letters. You should see all the ones of length 1 in alpha order, then all those with 2 letters and so on.

zx2zx
zx2zx

Apparently they are teh ASCII codes for letters. if you type =CHAR(ROW()) in cell A1 and copy down to cell a255, then change teh font to TERMINAL you will see what each ASCII codes represents

awgiedawgie
awgiedawgie

That was going to be my comment, but you beat me to it by an hour. I've been using that method for over ten years. I've even simulated the right-click-drag-and-drop method in several of my macros. I finally made the jump in February away from Office 2000 to 2010 (but only because 2000 refused to install on my Win7 machine). I was amazed to find that MS still hasn't implemented a native series fill for alphabetical lists.

Editor's Picks