Excel COLUMN to Number (Table of Content)
- Convert Column Heading to a Number
- How to use COLUMN function in Excel?
- How to Change the Excel Reference Style?
- What is the R1C1 Reference Style?
Excel COLUMN to Number
In Excel, column headings or labels are numbers instead of alphabets. This feature is called Excel reference style.
Convert Column Heading to a Number
Excel provides a built-in COLUMN function which falls under Lookup/Reference function category. This function returns the column number for a given cell reference.
For Example: For finding the column number of Cell A10, we will use the formula like below:
=COLUMN (A10)
Which gives 1 as a result because column A is the first column.
Formula:
Where the passing argument reference is optional.
This function accepts only one argument which is optional. It takes cell reference or a range of cells for which we want to find the column number. It returns the numeric value.
How to Use COLUMN Function in Excel?
Let’s take some examples to understand the usage of this function.
Example #1
We have a list of some cell addresses and range of cells.
Now we will apply the COLUMN function here on the above cell address and the Result is given below:
Explanation:
As we can see above, in cell B7 we have passed blank argument inside the COLUMN function, hence it returns the number 2 as the COLUMN function itself exists in Column B.
Example #2
We have given below list of references.
Now after applying the COLUMN function, the final result is given below:
In the same way, we can find out the list of all column labels in excel. Below is the list from Column A: Column Z for your reference:
In Excel spreadsheet named “Column A to Column ZZ”, we have provided you the Column numbers from Column A to Column ZZ.
How to Change the Excel Reference Style?
Excel displays the column labels alphabetically but we can change it with column numbers. This whole thing is called Excel R1C1 reference style.
For changing this reference style, please follow below steps:
- Open a Microsoft Excel spreadsheet then go to the FILE tab and click on the Options tab in the left pane window as shown in the below screenshot.
- It will open an Excel options dialog box and click on Formulas tab in the left pane window as shown in the below screenshot.
- This will display some option in the right side window as you can see in the above image. Tick on checkbox R1C1 reference style under Working with formulas section. Refer below screenshot.
- By clicking on this option, we enable the style of using numbers for both rows and columns. By default, Excel will display Column headings as Column alphabets. With this option cells are referred to in this format: R1C1, Click on OK.
As a result, Excel will convert the column labels into numbers. Refer below screenshot.
By default, this R1C1 reference style option is disabled, due to this Excel display column headings as alphabets.
What is the R1C1 Reference Style?
Excel by default uses the A1 reference style, which refers to Columns as letters. A is the column and 1 is the row. Excel has a total of 256 columns and 65,536 rows.
i.e. 256 are the column headings and 65,536 are the row numbers. For any cell address, we always start with Column label followed by a Row number.
For Example, E65 refers to the cell where Column E and Row no. 65 intersect to each other.
By enabling the R1C1 reference style option under the FILE tab, we can convert the Column Labels into Column numbers. This style is very useful when we are using rows and columns positions in macros.
In this R1C1 style, Excel refers to a cell position with “R” followed by a row a number and “C” followed by a Column number.
Things to Remember About Excel Column to Number
- Reference argument can be a single cell address or range of cells.
- It is an optional argument, means if we don’t pass any argument, it returns the column number of the cell where the column function exists.
- This R1C1 reference style option is very useful when we are using row and column positions in macros.
Recommended Articles
This has been a guide to Excel Column to Number. Here we discussed How to use COLUMN function in Excel with examples and downloadable excel template. You may also look at these useful functions in excel –
