You can populate numbers, names of months or weekdays easily by dragging your mouse. However, you can’t populate letters in the same fashion. In this guide, we're going to show you how to populate Alphabet letters in Excel using three different methods.
Introduction
Let us first take a look at how character sets work in Excel. There are two built-in functions which are related to converting numbers into characters:
We can use these functions to convert numbers and letters from one another to populate letters in Excel.
Simpler Method
Uppercase and lowercase letters in ASCII character set reside between the numbers 65-90 and 97-122, respectively. 65 = A, 66 = B, 90 = Z, 106 = j, etc. Using character conversion formulas like CHAR, we can convert this numbers to corresponding letters.
Syntax
Example
The challenge for this approach is generating numbers for the CHAR function. As explained in the previous section, you need numbers between 65 and 90 or 97 and 122 for letters. For this, you can use the SEQUENCE function at this step. The SEQUENCE function can generate an array of sequential numbers automatically.
All you need to do is to supply how many numbers you need, and which number to start with.
Syntax
Example
The function above returns numbers into 26 rows and single column. If you want to generate numbers or letters like in our example, you can use a formula like =SEQUENCE(1,26,65).
Let’s combine both functions to create formula for populating letters of the alphabet.
Syntax
Example
Using the CODE function for populating letters of the alphabet
Excel's has the CODE function works in opposite way of the CHAR. The CODE function simply returns the corresponding number of the given character in a character set.
Syntax
Example
As a result, the CODE function with the character can be replaced with the number itself. The following is an example for using this formula.
Syntax
Example
Dynamic character interval
Using the CODE function, we can improve the model by following a more dynamic approach. We can replace the hard coded letters like “A” or “Z” with cell references containing these characters. This approach allows us to change the start and end characters of alphabet letters without updating the formula.
Syntax
Example
Using LET function to populate Alphabet letters
This is an optional step for populating alphabet letters in Excel. The relatively new LET function allows you to create named ranges inside the context of its own formula.
The syntax of the LET function contains the actual formula which using determined name-value pairs.
Syntax of the LET function
Syntax of our model with the LET function
Example
We defined named ranges for Start and Finish as CODE(M7) and CODE(M6) respectively. Each name is replaced with the corresponding CODE function block.
