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

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.

 How to AutoFill Sequential Letters in Excel


Download Practice Workbook


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.

How to AutoFill Sequential Letters in Excel

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

How to AutoFill Sequential Letters in Excel

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.

How to AutoFill Sequential Letters in Excel

Rather, it will give value according to the ASCII table.

Read More: How to Use Autofill Formula in Excel (6 Ways)


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,"")

How to AutoFill Sequential Letters in Excel

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.

How to AutoFill Sequential Letters in Excel

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.

Read More: How to Enter Sequential Dates Across Multiple Sheets in Excel


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)

How to AutoFill Sequential Letters in Excel

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 for the rest of the cells.

The rest cells will be sequentially filled.

How to AutoFill Sequential Letters in Excel

The increase of letters will be in AB, AC, AD…….. Format whenever the value is greater than 26.

Read More: How to AutoFill from List in Excel (8 Quick Ways)


Similar Readings:


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))

How to AutoFill Sequential Letters in Excel

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.

How to AutoFill Sequential Letters in Excel

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.

How to AutoFill Sequential Letters in Excel

  • 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

How to AutoFill Sequential Letters in Excel

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.

How to AutoFill Sequential Letters in Excel

Note:

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

Related Content: [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)


Practice Section

You can practice here for further expertise.

How to AutoFill Sequential Letters in Excel


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

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

Leave a reply

ExcelDemy
Logo