It will be very supportive if we can AutoFill sequential letters in Excel instead of putting them one at a time. In this article, I will try to show some practical ways how to AutoFill sequential letters in Excel.
For clarification, I am gonna use a dataset of a company where the Gate No is mentioned according to the Watchman’s Name.
How to AutoFill Sequential Letters in Excel: 5 Quick Ways
1. Using CHAR & CODE Functions to AutoFill Sequential Letters in Excel
The use of the CHAR function and CODE function is one of the easiest ways to AutoFill sequential letters in Excel.
Steps:
- We need to input the first alphabet which is A.
- I inserted the first Alphabet A.
- Input the following Formula in cell C6.
=CHAR(CODE(C5)+1)
Formula Breakdown
- CODE(C5) —–> It returns the code for the value of the C5 cell.
- Output: 65
- CHAR(CODE(C5)+1)
- CHAR(65+1) —–> The CHAR function will return the character depending on the ASCII table.
- Output: “B”
- Press ENTER.
Then, we will find the sequential value which is B.
- Select the output cell and drag it to the required cell. Here Fill Handle will AutoFill the formula for the rest of the cells.
We will be able to see the values sequentially.
Rather, it will give value according to the ASCII table.
Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table
2. Employing SUBSTITUTE Function to AutoFill Sequential Letters in Excel
Using the SUBSTITUTE function along with the ADDRESS function and the ROW function to AutoFill sequential letters in Excel.
Steps:
- We need to input the following formula in cell C5.
=SUBSTITUTE(ADDRESS(1,ROWS($A$1:$A1),4),1,"")
Formula Breakdown
- ROWS($A$1:$A1) —> The ROWS function returns the value based on row numbers.
- Output: 1
- ADDRESS(1,1,4) —> The ADDRESS function will return the address depending on the row_num and col_num.
- Output: “A1”
- SUBSTITUTE(ADDRESS(1,ROWS($A$1:$A1),4),1,” “)
- SUBSTITUTE(‘A1’,1,””) —–> Here, the SUBSTITUTE function will substitute 1 character from the left side of the text.
- Output: A
- Press ENTER.
Then, we will have the first letter A.
- Next, we will use the Fill Handle to AutoFill the formula for the rest of the cells.
We will see the results on the datasheet.
Read More: How to AutoFill from List in Excel
3. Implementing LEFT Function to AutoFill Sequential Letters in Excel
A very efficient way is the use of the LEFT function including the ADDRESS function and ROW function to AutoFill sequential letters in Excel.
Steps:
- Input the following Formula in cell C5 :
=LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1)>26)+1)
Formula Breakdown
- ROW(A1) —–> The ROW function returns the row number of the referenced cell.
- Output: 1
- ADDRESS(1,1,4,1) —–> The ADDRESS function will return the address depending on the row_num and col_num.
- Output: “A1”
- (ROW(A1)>26) —–> It will check whether the row number is greater than 26 or not.
- Output: FALSE
- FALSE+1 —–> becomes
- Output: 1
- LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1)>26)+1)
- LEFT({“A1”},{1}) —–> Here, the LEFT function will extract 1 character from the left side of the text.
- Output: A
- Explanation: it returns the left 1 character from A1 which is A.
- Press ENTER.
You will have the first alphabet A.
- Then, use the Fill Handle to AutoFill the formula in a column for the rest of the cells.
The rest cells will be sequentially filled.
The increase of letters will be in AB, AC, AD…….. Format whenever the value is greater than 26.
Read More: How to Use Autofill Formula in Excel
4. Application of Combined Functions to AutoFill Sequential Letters in Excel
Another way for two letters is to apply a Combined Function. This combined function includes IF function, RIGHT function, CHAR function, CODE function, LEFT function, and MOD function.
Steps:
- We have given the first input which is AB.
- Write the following Formula in cell C6 :
=IF(RIGHT($C5,1)="Z",CHAR(CODE(LEFT(C5,1))+1),LEFT(C5,1))&CHAR(65+MOD(CODE(RIGHT(C5,1))+1-65,26))
Formula Breakdown
- RIGHT($C5,1) —–> The RIGHT function extracts the characters from the right.
- Output: B
- RIGHT($C5,1)=”Z”—–> The RIGHT function checks if the second letter of the first input is equal to “Z” or not.
- “B”=”Z”
- Output: FALSE
- CHAR(CODE(LEFT(C5,1))) —–> It will return the character based on code value.
- CHAR(CODE(“A”))
- CHAR(65)
- Output: A
Note: If the output of RIGHT($C5,1)=”Z” be TRUE,
Then,
- CHAR(CODE(LEFT(C5,1))+1) —–> It will return the character based on code value.
- CHAR(CODE(“A”)+1)
- CHAR(65+1)
- CHAR(66)
- Output: B
Note: If the output of RIGHT($C5,1)=”Z” be FALSE,
Then,
- LEFT(C5,1) —–> Here, the LEFT function will extract 1 character from the left side of the text.
- Output : A
- CHAR(65+MOD(CODE(RIGHT(C5,1))+1-65,26)) —–> becomes
- CHAR(65+MOD(CODE(RIGHT(“AB”,1))+1-65,26))
- CHAR(65+MOD(CODE(“B”))+1-65,26))
- CHAR(65+MOD(66+1-65,26))
- CHAR(65+MOD(67-65,26))
- CHAR(65+MOD(2,26))
- CHAR(65+2)
- CHAR(67)
- Output: C
- IF(RIGHT($C5,1)=”Z”,CHAR(CODE(LEFT(C5,1))+1),LEFT(C5,1))&CHAR(65+MOD(CODE(RIGHT(C5,1))+1-65,26))
- “A”&”C”
- Output: AC
- Then, press ENTER.
- To AutoFill the formula for the rest of the cells, use the Fill Handle.
Read More: How to Apply AutoFill Shortcut in Excel
5. Employing User Defined Function to AutoFill Sequential Letters in Excel
We can also use VBA to AutoFill sequential letters in Excel.
Steps:
- Go to the Developer tab and select Visual Basic.
We can also press Alt + F11 as an alternative way.
- From the Insert option, select Module.
- Write the following code in the Module.
Function AutoFill(b As Long) As String
AutoFill = Replace(Cells(1, b).Address(False, False), "1", "")
End Function
Here, I’ve created a User Defined Function AutoFill
Then, use the Replace.Address property to determine the cell value.
- Save to code and go back to the worksheet to use the function.
- Select cell C5 and type the following formula.
=AutoFill(ROW())
The result will appear according to the row_num. As the row number of the C5 cell is 5 it is returning the 5th letter which is E.
- Use Fill Handle to AutoFill the rests.
Read More: How to Create a Custom AutoFill List in Excel
Practice Section
You can practice here for further expertise.
Download Practice Workbook
Conclusion
In this article, I explained 4 ways to AutoFill Sequential Letters in Excel. Anyone can choose any process according to their choice. For further queries, leave your thoughts in the comment section.
Related Articles
- AutoFill Cell Based on Another Cell in Excel
- How to Turn Off AutoFill in Excel
- Fix: Excel Autofill Not Working
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
<< Go Back to Excel Autofill | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
