Excel Column to Number – Table of Contents
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 which can tell the exact column number you are in right now or you can find the column number of supplied argument as well.
COLUMN function in excel not only tells you the column number you are in but it can be used as a support function for other functions as well. In this article, we will discuss the concept of Column to Numbers in excel.
What is COLUMN Function in Excel?
COLUMN is nothing but your ROW function in excel, it works exactly the same way. ROW will return the current or supplied cell row number. Similarly, the COLUMN function returns the current column or supplied cell column number.
We have only one parameter or argument in COLUMN function and that too an optional argument. You must be thinking how it can be an optional argument when there is only one argument to supply.
Yes if you select any particular COLUMN it will return that supplied column number. Here we need to specify the column alphabet we are looking to find the column number.
Another way is if you do not supply any column alphabet then it will return the active column number as the result.
How to Find Column Number in Excel?
Below are the examples of how to find column number in excel.
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 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 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 references 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.
You can download this Column to Number Excel template here – Column to Number Excel Template
Recommended Articles
This has been a guide to Column Letter to Numbers in Excel. Here we discuss how to find column number in excel using COLUMN and COLUMNS function. You may learn more about excel from the following articles –
- Use Text Function to get the Opposite of Concatenate
- Excel Rows Function
- Excel Rows vs Columns – Compare
- How to Remove Duplicates in VBA?
- Excel Change Case
- Excel Rows and Columns
- How to Concatenate Excel Columns?
- How to Remove Duplicates from Excel Column?
- Lock Column in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
