Farihat
Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. There are types of data that require sequential letters by default. As you can autofill sequential numbers or months, you can’t autofill letters. This article will help you with six ways to autofill sequential letters in Excel. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Autofill Sequential Letters in Excel with These 6 Suitable Approaches
The article is going to introduce six approaches in Excel to autofill sequential letters. The methods will help you understand the uses elaborately. The following discussion will cover the uses of the CHAR, CODE, RIGHT, SUBSTITUTE, and LEFT functions. You can also use the Autofill option and the VBA Macro Code feature to autofill sequential letters in Excel. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.
1. Using CHAR and CODE Functions
The CHAR function is a character-calling function. It is widely used in Excel. If you can add the CODE function with it, you can easily autofill sequential letters in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, type “A” in cell C6.
- Second, select cell C7.
- Third, enter the following formula.
- Fourth, hit the Enter key.
- Fifth, using the Fill Handle icon, copy the formula down to cell C9.
🔨 Formula Breakdown
CHAR(CODE(C6)+1)
👉 Here, CODE returns the code number from the ANSII list.
👉 Then, CHAR shows the character value after the addition of 1. It will show the next code eventually.
Finally, we get a “B”.
Read More: 3 Ways to Auto Number or Renumber After Filter in Excel
2. Utilizing RIGHT Function for Multiple Letters
The first method with the CHAR and CODE functions is only applicable to single letters. The RIGHT function can return as many letters as you want if merged with the previous method.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, type “AA” in cell C6.
- Next, select cell C7.
- Then, enter the following formula.
- Now, hit the Enter key.
- Finally, using the Fill Handle icon, copy the formula down to cell C9.
🔨 Formula Breakdown
“A”&CHAR(CODE(RIGHT(A2,1))+1)
👉 Here, RIGHT gets the last 1 character from cell A2.
👉 Also, CODE returns the code number from the ANSII list. It adds 1 to the code received from RIGHT function.
👉 Then, CHAR shows the character value after the addition of 1. It will show the next code eventually.
Finally, we get “AB”.
Read More: 4 Ways to Add Excel Sequence Number by Group
3. Applying SUBSTITUTE Function
The SUBSTITUTE function is one of the functions that have a wide pool of uses in Excel. It can help you to autofill sequential letters with the help of the ADDRESS function and the ROWS function.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell C6.
- Then, enter the following formula.
- Now, hit the Enter key.
- Finally, using the Fill Handle icon, copy the formula down to cell C9.
🔨 Formula Breakdown
SUBSTITUTE(ADDRESS(1,ROWS($A$1:$A1),4),1,””)
👉 Here, ROWS return numbers followed by its range $A$1:$A1.
👉 Also, ADDRESS returns the cell address. The condition is received from the ROWS function.
👉 Again, SUBSTITUTE takes the output from ADDRESS as text. Furthermore, it adds 1 as old text and a blank string as new.
Finally, we get an “A”.
Read More: 6 Ways to Fill Down to Last Row with Data Not Blank in Excel
4. Implementing LEFT Function
The LEFT function is a helping function along with the ADDRESS function and the ROW function to autofill sequential letters directly without using any specific references.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell C6.
- Then, enter the following formula.
- Now, hit the Enter key.
- Finally, using the Fill Handle icon, copy the formula down to cell C9.
🔨 Formula Breakdown
LEFT(ADDRESS(1,ROW(A1),4,1),(ROW(A1>26)+1)
👉 Here, ROW returns the row number of cell A1.
👉 Also, ADDRESS returns the cell address. The condition is received from the ROW function.
👉 Again, LEFT takes the output from ADDRESS as a text string. Furthermore, it shows the number of characters returned by ROW from the left.
Finally, we get an “A”.
Read More: 5 Ways to Fill Down to Last Row with Data in Excel
5. Editing Autofill Sequence Option
This method will help you to automatically enable your autofill options to add sequential letters in Excel like numbers, months, days, etc. It will not be limited to any worksheet or workbook. Rather, it will be applicable to all your Excel workbooks.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, go to the File tab.
- Next, select the Options button as shown in the image below.
- Now you will get an Excel Options dialogue box over your Excel worksheet.
- Then, choose the Advanced option from the menu and scroll down to the last portion where you can see the Edit Custom Lists… box like the following picture.
- Here, select the box.
- Now, you should get a Custom Lists dialogue box.
- Next, click on the “Cell range choosing” icon that is highlighted in the image below.
- Here, select the cell range C6:C9.
- Then, click on the icon at the Custom Lists dialogue box in the following image.
- Then, click on the Import option which will show you the entries.
- After that, click on the Add option.
- Next, hit the OK button.
- Now, on the Excel Options dialogue box, hit the OK button again.
- Here, select cell C6 and type “a” on it.
- Next, using the Fill Handle icon, autofill the cells till cell C9.
- Eventually, you will sequentially get letters even if they are lowercase letters.
6. Employing User-Defined Function
Using VBA (Visual Basic for Applications), you can employ Macro code to apply on your worksheet. Moreover, it is also the only method that will create a new function to autofill sequential letters in Excel automatically. With the proper instruction, you can make your work easier.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, press Alt+F11 to open Visual Basic Application (VBA).
- Next, select the Module option from the Insert tab.
- You can also press Alt+I+L for the action.
- Then, write the following code on the Module box.
Function Auto_subq(b As Long) As String
Auto_subq = Replace(Cells(1, b).Address(False, False), "1", "")
End Function- Here, you should see the window like the image below.
- Now, select cell C6.
- After that, enter the following formula.
- Next, hit the Enter key.
- Finally, copy the formulas down their cells using the Fill Handle icon.
📄 Important Notes
🖊️ You should modify your formulas according to your dataset.
🖊️ Some methods may not work for multiple letters. Make sure you understand your requirement and use the methods accordingly.
🖊️ Finally, you need to enable Macro Workbook for the VBA to run on your workbook.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to convert degrees, minutes, and seconds to decimal degrees in Excel.
📌 Primarily, you can use the CHAR and CODE functions to autofill sequential letters in Excel.
📌 Here, you can combine the RIGHT function with CHAR and CODE functions. It will enable you to autofill sequential letters in multiple-letter cases in Excel..
📌 Again, you can try the SUBSTITUTE function along with the ADDRESS and ROWS functions.
📌 Eventually, if you can use the LEFT function with a justified function, you can use it to autofill sequential letters in Excel. You need to add ADDRESS and ROW functions with it..
📌 One of the most effective, yet complicated methods is editing Custom Lists for Autofill Sequence. It will let you try the Fill Handle to autofill sequential letters in Excel forever.
📌 You can employ VBA Code to autofill sequential letters.
Conclusion
To count duplicates in two columns in Excel, three methods are useful. You can use the CHAR, CODE, RIGHT, SUBSTITUTE, and LEFT functions. The VBA Macro Code feature and the edit Autofill option can help you to autofill sequential letters in Excel as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
Related Articles
- 4 Ways to Repeat Rows a Specified Number of Times in Excel
- 7 Simple Ways to Increment Month by 1 in Excel
