How to auto fill sequential letters in Excel
This week I was asked how to auto-fill sequential letters in Excel. What do I mean by this?
If you type 1 in a cell and drag it down Excel can populate the cells with sequential numbers (2, 3, 4, 5, 6, 7, 8, etc).
A user needed a list of sequential letters (a, b, c, d, etc). Once the sequence reached Z, the next letters would be AA, AB and so on. There were too many rows to be filled in so the user was looking for a quicker, less mundane way to populate the cells other than typing in each letter.
The formula I created for him was:
=LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1)>26)+1)
It was an unusual question and in a split second of inspiration (not a common occurrence) I saw the answer in Excel itself. Excel’s column letters followed the same sequence that the user wanted, so if I could use this, then that would give the user what they wanted.
What follows may seem long winded, but it was easy and quick to piece together using different formulas.
Row
This formula returns the row number of a cell reference. For example =ROW(A1) would give the answer 1, =ROW(G84) would give the answer 84. It is a simple formula, but the key to solving our issue here.
Address
The address formula gives a cell reference in text given row and column numbers. For example, row 1 column 1 is cell A1 – row 255 column 10 is J255.
Using the formula =ADDRESS(1,1,4,TRUE) I get the value A1.
The formula between the brackets is made up of:
- row_num : This is the row number, row 1 is row 1, row 10 is row 10 and so on. I have picked row 1 in my formula for an easy starting point – it will not be important later in the formula
- column_num : This is the column number, 1 is column A, 2 is column B and so on. For our particular problem here this is what I am interested in. In the above Address formula I entered 1 purely for demonstration purposes – this will be changed in the next example of this formula.
- abs_num : This sets the absolute or relative reference of the result. That is, depending on the option you select from 1,2,3 or 4 a $ sign will (or will not) appear before the column letter and row numbers. In our case, I do not want a $ sign in my result so I select 4 to make it a relative reference.
- [a1] : This is the format of the result – you can see the row column numbers by selecting 0 or False or the cell reference by selecting 1 or True. If I selected 0 (or False) in the above formula my result would be R[1]C2. I selected 1 (or True) and the result is A1.
If I entered this formula in cell A1 and dragged it down, I will get the result A1 everytime – this is because I am repeatedly querying the address of row 1, column 1. I need this to change when I drag down the formula.
This is where I use the ROW formula.
Address and Row
If I enter =ROW(A1) in cell A1 and dragged it down the formula will change to =ROW(A2), =ROW(A3) and so on and the result of the formula will be 1, 2, 3, etc.
In my address formula I need the column reference to change so that the result is A1, B1, C1, etc. I will substitute the “1” in the column_num field with the ROW formula.
=ADDRESS(1,ROW(A1),4,1) will give me A1 as a result. This is row 1, column 1 and the column number is the result of the ROW formula.
When I drag this formula down a cell it changes to =ADDRESS(1,ROW(A2),4,1) and the result of this is B1 – row 1 and column 2, again the column number is taken from the ROW formula. If you understand this much, the rest should be relatively simple – it might be confusing to think that I am using a ROW formula to get a column number. I am only using the ROW formula to incrementally increase the column number each time.
Left
The LEFT formula returns the specified number of characters from the start of a string.
The formula is made up of 2 parts
- text : you can enter text here, or you can enter a cell reference if and the formula will query the string in that cell
- num_chars : this is the number of characters from the start of the string that you want returned.
If cell A1 had “THEJAYTRAY” typed in it and I typed the formula =LEFT(A1,5) the result of the formula will be the 5 leftmost characters of my string which is “THEJA”.
If the result of my first ADDRESS/ROW formula from above is in cell A1 and the result itself is A1 then the =LEFT(A1,5) will give me A. The same formula in cell B1 will give the result B.
The ADDRESS/ROW formula above will be the text I am querying and I want the leftmost value (the column letter). My new formula is =LEFT(ADDRESS(1,ROW(A1),4,1),1). This returns A from my first formula, B from the second and so on.
Before we pat ourselves on the back, have we noticed a flaw here?
What happens after we get to Z? The next formula will give us A because it is taking the first character from AA1. We need to get the first 2 characters from the string once we pass the letter Z.
How do we do this?
How to auto-fill sequential letters in Excel
We are going to amend the num_chars part of our LEFT formula to allow for where I want to see the first 2 characters from the string.
We can just copy down our previous formula 26 times (until we reach Z) and then for the 27th formula change the num_chars to 2. That would work, but while we have made it this far, why not go all the way?
If the row number is not greater than 26, then the formula ROW(A1)>26 would be false and ROW(A30)>26 would be true. A false equates to the numeric value 0 and a true equates to 1.
We can use this logic to our advantage. If the ROW number is less than 26 we want the first character of the string only – if the row number is greater than 26 we want the first 2 characters of our string. Replace the num_chars part of the LEFT formula with (ROW(A1)>26)+1. If the row is less than 26 this means 0+1 which is 1 and if the row number is higher than 26 this gives us 1+1 which is 2 and they are the number of characters we need to display.
Note : The ROW number is not returning the value of the row the formula is in – it is returning the value of the row you queried. For example if in cell B50 you entered =ROW(A1) you will be returned the value 1 because you are querying A1. It won’t return 50 which is the row number that the formula is typed in.
The Final Formula
The final formula is =LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1)>26)+1) and it gives us the results we were looking for.
This may seem like a long way to get the result we were looking for and that maybe manually typing the letters in would be quicker – in reality once I knew the approach to take it was very quick to piece together the formula. The key is piecing the formula together. Most of the questions I get asked are passed to me because the user has already tried a formula or can’t think of a formula to use – I piece together several formulas to create one bigger formula.
If you pay attention to the brackets and commas, after that they are not that hard to understand or read.
