#Excel: Autofilling with Alphabets or other Custom lists or series
In Excel, we may had luck with not having to type all the months or days of the week in rows or columns for needs such as pivoting, reporting, etc. Since we can type just one month or the day in a cell(s) (Figure 1) and simply drag autofilling the adjacent cell values (Figure 2).
Figure 1
Figure 2
Sometimes, we need alphabet series for custom personal orbusiness needs. Even if we type A In a single cell or A, B in two adjacent cellsand dragging cell values will result in all A (Figure 3)or repeating A, B values (Figure4)respectively.
Figure 3
Figure 4
To overcome this limitation, type in A,B,C,…Z in adjacent cells just one time prior to adding to the custom list. If this is a daunting task, we can make use of the CHAR() function and a little knowledge on ANSI character set. Uppercase A is represented in ANSI by the value 65 in the Windows environment.
Type in 65, 66 in two adjacent cells in a row wise (vertical) direction or column wise (horizontal) direction. For our purpose, we will go row wise as it is easy to deal with large members and small row height than large column width that is default. In the column adjacent to the cells containing 65 and 66, type “=CHAR()” and inside the parentheses click or point to the cell containing the value 65 and copy this formula to the next row corresponding to the cell containing the value 66. Select the range as shown below (Figure 5) and drag the cell value and formula range to autofill the rows below until we get Z corresponding to the value of 90 (65+25) (Figure 6).
Figure 5
Figure 6
Now we have the necessary alphabet series to be added to the custom list. Before we do that, the formula generated alphabet values need to be converted to just values by copying the range as pasting as values else there will be an error during import (Figure 7).
Figure 7
In Excel 2007, click the office button on the top left corner (Figure 8) and select “Excel Options” (Figure 9).
Figure 8
Figure 9
In the “Excel Options” window (Figure 10), in the “Popular” section select “Edit Custom Lists…” (Figure 11).
Figure 10
Figure 11
Select the “Option” range selector to select the range containing the alphabets (Figure 12) and click “Import” and after verifying the alphabet series is updated click ok (Figure 13).
Figure 12
Figure 13
Now you have at your disposal to create the alphabet series in Uppercase (Figure 14) and also don’t have to create a separate list for Lowercase (Figure 15) as it will be automatically recognized.
Figure 14
Figure 15
The above steps can be used to add other custom series such as Roman Numerals but without the leverage of CHAR() function. Hope this helps in some ways and see you all next time in another tip.