Excel Tips‎ > ‎

Using the alphabet to number a list

How to use letters as an index in Microsoft Excel cells
Rather than use 1 to 10, it is sometimes preferable to use A, B, C, etc.

Microsoft Excel can fill a series with numbers (drag the bottom right corner down the series), but even if you start the pattern with A in one cell, B in the next, etc. it will note extend the series. Instead it merely repeats ABCABC, etc.

There are two ways to get around this and get Excel to do something more intelligent.

You can define a custom list. To do this go into Options -> Advanced -> General -> Edit custom lists.
You need to select a range on a worksheet that already has A to Z, etc. and Import them into the known custom lists. This works well, but only if the copy of Excel has had this done to it.

Another way to do it is to use a simple formula.

Start by putting your first letter, e.g. "A", in the first cell, then in the next cell use the formula:-
=char(code(A1)) where A1 is the cell reference for the first cell.
You can now repeat the formula and you get the full sequence.

As easy as A, B, C!
Comments