How to create the alphabet in Excel

A common task is to quickly list all the letters of the alphabet in rows or columns: A, B, C… for headers, labels, test data or seat numbers.

Getting a list of numbers of any length is easy, and everyone knows several ways: fill two cells and drag, use Fill → Series, or a formula referring to the previous cell (=A1+1).

With letters, AutoFill doesn’t work: type “A” and drag, and you get “A” in every cell. But you can create the alphabet with functions, and there are several options. Short answer for Excel 365 users:

=CHAR(SEQUENCE(26,,65))

Read on for older versions and other variations.

Create the alphabet with a regular function

The simplest option is the CHAR function. It returns the character for a code. Uppercase English letters have codes 65 to 90, lowercase ones 97 to 122.

So create a column of these numbers by dragging (65, 66, …, 90), enter =CHAR(A1) once next to the first number with a relative reference and fill it down along the numbers.

Or, without a helper column, use the row number. Put this formula in any cell of the first row and fill it down 26 rows:

=CHAR(ROW()+64)

ROW() returns 1 in the first row, plus 64 gives 65, the code of “A”. In the second row it’s “B”, and so on. If the list doesn’t start in row 1, subtract the starting row: =CHAR(ROW()-ROW($A$5)+65) for a list starting in A5.

How to drag the alphabet with a formula

Can you add a number to a letter and get the next one without any helper column? Yes, that formula exists too. The CODE function does the opposite of CHAR: it returns the code of a character. So the next letter after the one in A1 is:

=CHAR(CODE(A1)+1)

Type “A” in A1, put this formula in A2 and drag it down. Replace +1 with −1 to go backwards.

Create the alphabet with array formulas

As you can see, the first method needed a column of numbers. Can you do without it? Yes, and that’s the beauty of array formulas: they can create intermediate arrays of values for calculations.

Another advantage is that you enter the formula once, after selecting the output area, and it fills all of it at once. In Excel 2019 and earlier, don’t type the curly braces: they appear when you press Ctrl+Shift+Enter.

Method 1: CHAR and ROW

ROW referring to rows 65 to 90 returns an array of numbers, which CHAR turns into letters. Select 26 cells in a column and enter:

{=CHAR(ROW(A65:A90))}

If the sheet will be edited and rows may be inserted or sorted inside A65:A90, this method isn’t reliable. INDIRECT with a text reference is safer:

{=CHAR(ROW(INDIRECT("65:90")))}

For lowercase letters, use “97:122”.

Method 2: column letters from cell addresses

Another way to get the English letters is from the addresses of the first 26 columns. The ADDRESS function returns a cell address as text by its coordinates, and LEFT takes the first letter. For a horizontal list:

{=LEFT(ADDRESS(1,COLUMN(A1:Z1),4),1)}

For a vertical list, wrap it in TRANSPOSE and select a vertical range of 26 cells before entering:

{=TRANSPOSE(LEFT(ADDRESS(1,COLUMN(A1:Z1),4),1))}

A bonus of this method: without LEFT, the same approach continues past Z with AA, AB, AC…, just like Excel column names. =SUBSTITUTE(ADDRESS(1,COLUMN(),4),”1″,””) returns the column letter of the current cell.

Create the alphabet in Excel 365

In Excel 365 and 2021, the SEQUENCE function creates the array of codes, and the result spills by itself, no Ctrl+Shift+Enter needed:

=CHAR(SEQUENCE(26,,65))

Lowercase letters:

=CHAR(SEQUENCE(26,,97))

A horizontal row instead of a column:

=CHAR(SEQUENCE(1,26,65))

Moreover, all the array formulas above no longer need Ctrl+Shift+Enter in these versions.

Other alphabets

CHAR depends on the system code page, so for other alphabets use UNICHAR, which works with Unicode codes. For example, the Greek capital letters Α to Ω start at code 913:

=UNICHAR(SEQUENCE(25,,913))

The Greek range has one gap (code 930 is unassigned), so check the list or remove the empty character. The same way you can generate any Unicode block if you know its starting code.

Make AutoFill work with letters

If you need the alphabet often, teach AutoFill the list once: File → Options → Advanced → General → Edit Custom Lists. Type the letters A to Z in List entries, one per line or separated by commas, and click Add. After that, type “A”, drag the fill handle, and Excel continues with B, C, D, just like with weekdays or months.

Using alphabet formulas in other tasks

These array formulas are useful not only for listing letters. The letters themselves are needed in other tasks, for example to find uppercase letters in a cell with COUNT(FIND(CHAR(ROW(65:90)),A1)) or to find the first letter in a cell and capitalize it.

Summary

I hope you found what you were looking for on this page and were able to create the alphabet in Excel. In Excel 365, it’s one formula: =CHAR(SEQUENCE(26,,65)). In older versions, use CHAR with ROW, and if you need the alphabet often, add it to Custom Lists so that AutoFill does the job.

Learning array formulas, part 2 of 4: count characters in a range of cells.