Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Column Letter in Excel

Ordinal numbers letters columns in Excel and R1C1 Reference Style

In this tutorial, I want to show that we can display the column letter (column heading) for a particular column. I believe that you are familiar with the option to display in Excel instead of the letter sequence number column, or "R1C1 Reference Style". If you do not then try the following:

File => Options => Formulas => turn the "R1C1 Reference Style". See now the column headers, and instead of letters you note serial numbers. R1C1 style, mainly used in VBA programming in Excel.

So, compared to the above each letter for a certain number. English alphabet consists of 26 characters. Thus we have a number of 1-26 or next letters A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z.

As in Excel 2013 we have 16384 column or of the column, the sequence of letters-character changes after this twenty letters (characters). So follows A ... Z ... AA ... AZ ... BA ... BZ ...... FOR ... ZZ ..... AAA ......... XFD.

If you want the column to display all of the letters of the columns you are using the following formula (see column B in the picture below).


=IF(ROW(A1)>26;CHAR(INT((ROW(A1)-1)/26)+64);"")&CHAR(IF(MOD(ROW(A1);26)=0;26;MOD(ROW(A1);26))+64)

Probably you are wondering what is number 64 in the formula above? Number 64 is the first character before the letter "A" if you type in a cell =CHAR(64), the formula will return a result as the character "@". To understand more, see the link Code Pages.

You order to better understand this formula, see Evaluate Formula calculation steps for each specified formula.

Figure 1.
Ordinal numbers in a series of letters columns in Excel

If you use Excel function CHAR and enter the formula =CHAR(65) then the formula will return the first letter of the alphabet, the character "A". (see column F in the picture above)
Another Excel function is related to the letter codes columns. This Excel function CODE. If the cell enter the formula =CODE("A") as a result of the formula will be the number of the letters (character) which is the number 65 (see column D in the picture above).

How to display a letter in the column cell

If you need to return as a result of a letter column in the cell then see examples below. Perhaps some of you are asking when and where to use the column letter. Letter column can usually be used in combination with the function INDIRECT when we need to modify the header-letter column when copying the formula to the right or down, or when we want to reference a simple formula to a particular column.

In the picture below you see a few headers or letters columns that are returned as a result of the formula that are located below the image. Also, note the letter of the column containing the formula ie. The result of formula. For example, the formula in cell 'D8' column shows the letter 'M' etc.

Figure 2.
How to display the letter columns in an Excel cell

In the picture above we have shown some letters column. Formulas contained in the cells in the above figure are as follows. Here is an important Excel function COLUMN and function ADDRESS.

**** Note column B and all the formulas that return the same result.
Attention, there are formulas in column 'B' and this is important, because if the formula set in the column 'K' then the formula presented a letter of the respective column.

The formula in cell 'B4' is as follows: The result of the letter of the respective columns in which the formula.
=RIGHT(LEFT(ADDRESS(1;COLUMN());LEN(ADDRESS(1;COLUMN()))-2);LEN(ADDRESS(1;COLUMN()))-3)

The formula in cell 'B6' is as follows: The result of the letter of the respective columns in which the formula.
=LEFT(ADDRESS(1;COLUMN();4);LEN(ADDRESS(1;COLUMN();4))-1)

The formula in cell 'B8' is as follows: The result of the letter of the respective columns in which the formula.
=MID(ADDRESS(ROW();COLUMN());2;FIND("$";ADDRESS(ROW();COLUMN());2)-2)

The formula in cell "B10" is as follows: The result of the letter of the respective columns in which the formula.
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW();COLUMN());"$";"");ROW();"")

**** Note column D and all the formulas that return the different result.

The formula in cell "D4" is as follows: The result of the letter of the respective columns in which the formula, because Excel function COLUMN() returns the number of columns in which the formula is located.
=SUBSTITUTE(ADDRESS(1;COLUMN();4);"1";"") or =SUBSTITUTE(ADDRESS(1;broj_stupca_u_kojem_se_nalazi formula;4);"1";"")

The formula in cell 'D6' is as follows: The result of the letter 'C' column. The result is the letter of the respective columns in which the formula, because Excel function COLUMN() returns the number of columns in which the formula is the number of the column 'C' is the number 3. Look at the first image of this tutorial.
=SUBSTITUTE(ADDRESS(1;COLUMN(C2);4);"1";"") or =SUBSTITUTE(ADDRESS(1;3;4);"1";"")

The formula in cell 'D8' is as follows: The result of the letter 'M' column because we are at number 3 column 'C' added up the number 10 which results in the number 13 which is the number of the column 'M'. See the first image of this tutorial.
=SUBSTITUTE(ADDRESS(1;COLUMN(C2)+10;4);"1";"") or
=SUBSTITUTE(ADDRESS(1;13;4);"1";"")

**** Note column F and all the formulas that return different result.

The formula in cell 'F2' is as follows: The result of the letter 'A' column. To better understand the formula look Evaluate Formula.

=MID(ADDRESS(1;1;1);2;FIND("$";ADDRESS(1;1;1);2)-2)

The formula in cell F5 "and" F6 "is as follows: The result of the letter 'A' column. Note the number 7 and number 3 within a function Address. This number indicates the number of columns (column_num). To better understand the formula look Evaluate Formula
=MID(ADDRESS(1;7;4);1;1) or =MID(ADDRESS(1;COLUMN(G1);4);1;1)
=MID(ADDRESS(1;3;4);1;1) or =MID(ADDRESS(1;COLUMN(C1);4);1;1)

Display column header or letters of the column in Excel

In the picture below you see multiple labels columns or column header. Each color denotes a formula in multiple forms. This picture below should you visualize how a formula may return a different result if it is in a different place or position in a worksheet (depending on the column in which the formula)

Figure 3.
Column header or column letter in Excel

The formula in cell A14: =MID(ADDRESS(1;COLUMN(G1);4);1;1) => result column letter G
The formula in cell C13: =MID(ADDRESS(1;COLUMN(M1);4);1;1) => result column letter M
The formula in cell F8: =MID(ADDRESS(1;COLUMN(K1);4);1;1) => result column letter K
The formula in cell B2, C5, D3: =SUBSTITUTE(ADDRESS(1;1;4);"1";"") => result column letter A
The formula in cell B4: =SUBSTITUTE(ADDRESS(1;COLUMN(A1);4);"1";"") => result column letter A
The formula in cell D5 =SUBSTITUTE(ADDRESS(1;COLUMN(C1);4);"1";"") => result column letter A
The formula in cell E2: =SUBSTITUTE(ADDRESS(1;COLUMN(D1);4);"1";"") => result column letter A
The formula in cell B6, C8, E5: =SUBSTITUTE(ADDRESS(ROW(A1);COLUMN(A1);4);"1";"") => result column letter A
The formula in cell B9, D11, E8 =CHAR(COLUMN()+64-1) => result column letter A, C, D
The formula in cell B10, D12, E9 =CHAR(COLUMN()+64) => a result of a letter column B, D, E
The formula in cell C11: =MID(ADDRESS(1;1;4);1;1) => result column letter A
The formula in cell C12, F7 =MID(ADDRESS(1;1;1);2;FIND("$";ADDRESS(1;1;1);2)-2) => result column letter A

How copying the formula, return the column header or column letter

In the picture below you see the cells that display the header of certain columns or as a result of returning the letters column. When we need to copy the formula that we need to return a letter that column then we can use multiple excel nested functions

Figure 4.
How copying the formula return the column header or column letter

Dynamic formula in cell B3 and B8 is as follows: =SUBSTITUTE(ADDRESS(1;COLUMN()+64;4);"1";""). By copying the formula to the right changes us returned result display next column in the series. When you copy the formula down, the drive letter of the column remains the same.

Copy the formula to the right and down - the result of a letter or column headers from A to Z

In the picture below you see a formula in multiple forms, which returns an array of letters columns from A to Z. Depending on what function to use the COLUMN or ROW, the result will be different when copying to the right or down.

Figure 5.
How to restore the letter columns from A to Z

The formula in cell E3: =CHAR(64+COLUMN()) => result column letter E, when copying to the right to change the letters column headers but when copying a formula down the letters column headers remain the same.

The formula in cell B8 =CHAR(64+COLUMN(A1)) => result column letter A, when copying to the right to change the letters column headers but when copying a formula down the letters column headers remain the same.

The formula in cell H8 =CHAR(64+COLUMN(F1)) => result column letter F, when copying to the right to change the letters column headers but when copying a formula down the letters column headers remain the same.

The formula in cell E12: =CHAR(64+ROW()) => result column letter L, when copying letters in the right column headers remain the same, when copying a formula down the letters column headers are changing in a series of letters to Z. If the wondering why the result column letter L then look over the first image of this tutorial. Notice that the formula is in the twelfth row. Also note that the ROW(12) =L -> 12. Look at cell B13 on the first image.

The formula in cell B17: =CHAR(64+ROW(B20)) => result column letter T, when copying letters in the right column headers remain the same, when copying a formula down the letters column headers are changing in a series of letters to Z. If are wondering why the result column letter T then look over the first image of this tutorial. Notice that the ROW(20) =T -> 20. Look at cell B21 on the first image.

The formula in cell H17: =CHAR(64+ROW(C3)) => result column letter C, when copying letters in the right column headers remain the same, when copying a formula down the letters column headers are changing in a series of letters to Z. If are wondering why the result column letter T then look over the first image of this tutorial. Note that the ROW(C3) =C -> 3. Look cell B4 on the first image.


And finally, I hope you notice the following:
If you use the ROW() and COLUMN() function then we have a dynamic formula that when copying returns changeable results

Please note, you may need to use a comma (,) instead of a semicolon (;).