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


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.

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

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

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.

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

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

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.

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

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.

Read More: How to Create a Custom AutoFill List in Excel


Practice Section

You can practice here for further expertise.

How to AutoFill Sequential Letters in Excel


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


<< Go Back to Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo