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 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 supplied argument as well.

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

You can download this Column to Number Excel template here – Column to Number Excel template

Example #1

Get Current Column Number by using COLUMN Function in Excel.

I have opened 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 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 cell write 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.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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 cell open the formula COLUMNS and select the range as C5 toN5.

Hit enter to get the desired result.

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

In this way, by using 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 Column alphabet and then followed by row numbers.

As we learned earlier in the article to get the column number we need to use 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 Row number then followed by column number, not an alphabet.

Follow the below steps to change it to 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, row number will come first and the column number.
COLUMN function can return both current column number as well as supplied column number.
R1C1 cell reference makes it easy to find the column number easily.

Excel Column to Number