# How to AutoFill Sequential Letters in Excel (5 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 on 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. ## 5 Ways to AutoFill Sequential Letters in Excel

### 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.

Note: If we keep AutoFilling, it will work perfectly till Z. But after that, it will not give sequential letters. It will give the corresponding character against the code. Rather, it will give value according to the ASCII 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
•  Output : “A1”
• 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.

Note: In this process, the cell will be filled AA,AB,AC…………….. in this fashion whenever the values are greater than 26. ### 3. Implementing LEFT Function to AutoFill Sequential Letters in Excel

A very efficient way is the use of the LEFT function including 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
•  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({“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 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. ### 4. Application of Combined Functions to AutoFill Sequential Letters in Excel

Another way for two letters is to apply 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. Related Content: Applications of Excel Fill Series (12 Easy Examples)

### 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, used 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 C5 cell is 5 it is returning the 5th letter which is E.

• Use Fill Handle to AutoFill the rests. Note:

In order to get the value from the first alphabet, we have to start working from the first row. ## Practice Section

You can practice here for further expertise. ## 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 #### Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  