How To Autofill Letters From A-Z In Excel
Microsoft Excel has a neat autofill feature that can complete a series like days of the week, months of the year, or numbers in a series e.g., odd or even numbers, etc. This feature excels with other functions, and while it doesn’t input letters automatically, we’ve discovered a solution to make it work for you. With the help of a simple formula, you can get excel to autofill letters from A-Z.
Autofill In Excel
If you’ve never heard of this feature, it works like this. If you’re trying to autofill the months in a year, you will type the first month i.e., January in one cell, then type February in the next cell. Select both and move the cursor to the bottom right corner of the second cell (the one with February in it). It should change to a plus sign. Click and drag it all the way down and it will fill in the rest of the months for you. This is also useful for filling formulas.
When you use this with letters of the alphabet though, it doesn’t work. To autofill letters from A-Z, you need to use a formula.
Autofill Letters From A-Z
In Excel, type the letter A in a cell. In the next cell, enter the following formula and replace the cell address with the cell address for the cell you’ve typed A in. The cell you enter the formula in will show you the letter B.
=CHAR(CODE(A1) + 1)
Next, click the bottom right of the cell you’ve pasted the formula in and the cursor will turn into a plus sign. Drag it down and the letters will autofill.
You can start at any letter. You don’t necessarily have to start with the letter A. Once you reach the end of the alphabet though, this formula will start filling it in with other characters like square brackets, underscore, backtick, and then the letters will start again in small case. It will not start filling in the letters like Excel does for the columns when it runs out of letters.
The formula makes use of the Char function which can fill in ASCII values. These values exist in a series which is what you can use to your advantage but it has its limitations. You can restart the series by typing A in the next cell once you reach Z. Unfortunately, if you try and use AA, AB, and AC, and try and create a series out of it like you can with odd or even numbers, Excel will not follow it.
Why can’t Excel just do alphabetical fill itself, complete with AA, AB etc when required, instead of having to use hacks like this.
Put some data in the first few cells, then fill down, and Excel figure out the pattern.
It’s a pity AI is used to harvest our data instead of useful functions like this!
If you want to use two letter, you can expand on the one letter handle with the following that copies the first letter than calculate the next one.
=CHAR(CODE(LEFT(A1, 1)) & CHAR(CODE(RIGHT(A1, 1) + 1)
I think your formula might need a relative not an absolute reference — your suggestion got me started but the formula below worked better — I am a novice so I may be wrong — thank you
=char(code(R[-1]C)+1)
I do not know how to write this in A1 format
Brilliant ! Keep up the good work
To make the next consecutive alphabetical list of AA, AB, AC, etc., that imitates Excel columns, assume”A” is visible in cell A2. Restart by typing “AA” in the cell after “Z”. Then in the next cell insert the formula =CONCATENATE(($A$2),CHAR(CODE($A2)+1)). Draw the formula down to make a column or row of labels that mimic the Excel double-letter pattern through AZ. Restart again by typing BA, then write another similar formula that concatenates a static “B” cell ($), with the added A-Z characters. Do it again & again until ZZ if you wish. It isn’t wholly auto-fill, but it gets there.