Excel Column to Number

Column Letter to Number in Excel

Figuring out which row you are in is as easy as you like, but how do you tell which column you are in at the moment. Excel has 16384 columns in total, which are represented by alphabetic characters in excel. Suppose if you want to find the column CP how do you tell?

Yes, it is almost an impossible task to figure out the column number in excel. However, nothing to worry about because, in excel, we have a built-in function called COLUMN in excel, which can tell the exact column number you are in right now, or you can find the column number of the supplied argument as well.

How to Find Column Number in Excel? (with Examples)

Example #1

Get Current Column Number by using the COLUMN Function in Excel.

• I have opened a new workbook and typed some of the values in the worksheet.

• Let’s say you are in the cell D7, and you want to know the column number of this cell.

• In order to find the current column number, write the COLUMN function in excel cell, and do not pass any argument; just close the bracket.

• Press Enter, and we will have a current column number in excel.

Example #2

Get Column Number of Different Cell by using COLUMN Function in Excel

Getting the current column isn’t the toughest task at all. Let’s say if you want to know the column number of the cell CP5, how you get that column number.

• In any of the cells, write the COLUMN function and pass the specified cell value.

• Then press Enter, and it will return the column number of CP5.

I have applied the COLUMN formula in cell D6 and passed the argument as CP5, i.e., cell reference of CP5 cell. Unlike normal cell reference, it will not return the value in the cell CP5; rather, it will return the column number of CP5.

So the column number of the cell CP5 is 94.

Example #3

Get How Many Columns are selected in the Range by Using COLUMNS Function in Excel.

Now we have learned how to get the current cells column number and specified cell column number in excel. How do you tell how many columns are selected in the range?

We have another built-in function called COLUMNS function in excel, which can return the number of columns selected in the formula range.

Assume you want to know how many columns are there from the range C5 toN5.

• In any of the cells, open the formula COLUMNS and select the range as C5 toN5.

• Hit enter to get the desired result.

So totally, we have selected 12 columns in the range C5 toN5.

In this way, by using the COLUMN & COLUMNS function in Excel, we can get the two different kinds of results, which can help us in calculations or identify the exact column when we are dealing with huge datasets.

Example #4

Change Cell Reference Form to R1C1 References

In Excel, by default, we have cell references, i.e., all the rows are represented numerically, and all the columns are represented alphabetically.

This is the usual spreadsheet structure we are familiar with. The cell reference is started by the Column alphabet and then followed by row numbers.

As we learned earlier in the article, to get the column number, we need to use the COLUMN function. How about changing the column headers from the alphabet to numbers just like our row headers, like the below image.

This is called ROW-COLUMN reference in excel. Now take a look at the below image and reference type.

Unlike our regular cell reference, here, reference starts with a Row number then followed by a column number, not an alphabet.

Follow the below steps to change it to the R1C1 Reference Style.

• Go to FILE and OPTIONS.

• Go to Formulas under OPTIONS.

• Working with Formulas, select the checkbox R1C1 Reference Style and click ok.

Once you click ok, then cells will change to R1C1 References, as shown above.

Things to Remember

• The R1C1 cell reference is the rarely followed cell reference in excel. You may get confused easily at the start.
• In normal cell references, we see column alphabet first and row number next. But in R1C1 cell references, the row number will come first and the column number.
• COLUMN function can return both the current column number as well as the supplied column number.
• R1C1 cell reference makes it easy to find the column number easily.

Recommended Articles

This has been a guide to Column Letter to Numbers in Excel. Here we discuss how to find column numbers in excel using the COLUMN and COLUMNS function. You may learn more about excel from the following articles –

• Excel Rows Function
• Excel Rows vs. Columns
• Excel Rows and Columns
• Concatenate Excel Columns