Even though the row references in Excel are in numbers, the column references are in English letters such as A, B, C,…
For example, cell C5 would mean the third column, as C is the third letter in the alphabet sequence.
There are times when we need to know the column number rather than the column letter. It is difficult to manually count and find the column number when there are many columns.
In this article, I’ll show you some simple ways to find the column number in Excel.
Method 1 – Using the COLUMN Function
COLUMN function is a quick and easy way to find out the column number of the active cell or any of a specific cell reference.
The syntax of the COLUMN function is COLUMN([reference]). The COLUMN function has only one argument, and it is also an optional argument.
Finding the Column Number of the Active Cell
The fastest way to know the column number of the active cell is by using the COLUMN function.
When you don’t specify the argument in the COLUMN function, and just use =COLUMN() in a cell, it will give you the column number of the active cell (the cell where it’s used).
I have entered the below function in cell C2.
=COLUMN()
In the example above, I entered the formula in column C, and column C’s column number is 3. So, Excel will return 3 in cell C2.
This means that if we enter the COLUMN function without a cell reference, we can get the formula cell’s column number.
Finding the Column Number of a Cell Reference
If we give a cell reference as the COLUMN function’s argument, we can get the column number of that cell reference.
I’ve entered the following formula in cell C2.
=COLUMN(B1)
In this scenario, I’ve chosen cell B1 as the argument for the COLUMN function.
So, the function will return the column number of cell B1. Column B is the second column. So, Excel returns 2 in cell C2.
This could be useful when you need to use the column number in formulas such as VLOOKUP or XLOOKUP and don’t want to hardcode the values.
Finding the Number of Columns Covered in a Range Reference
We can even give a range of cells to the COLUMN function’s argument and get the column numbers as a horizontal array.
In cell C2, I entered the formula below.
=COLUMN(A1:D1)
In this case, I used cell range A1 to D1 as the COLUMN function argument.
Now, the function will return the column numbers of the selected cell range as a horizontal array. Column A is the first column, while Column D is the fourth column. So, we get column numbers 1 to 4 as a horizontal array.
This could be useful in case you want to get sequential numbers in Excel. For example, if you want a horizontal array of numbers from 1 to 10, you can use =COLUMN(A1:J1)
We can use the COLUMN function as an array function only in Microsoft 365.
If we give a range of cells for the COLUMN function in earlier versions of Microsoft 365, we will get only the column number of the first cell reference in the range of reference of the COLUMN function.
If we want, we can just get the column number of the first cell reference in the COLUMN function’s range of references. Then we have to use the implicit intersection operator “@”. When we apply this operator to an array, we can get the array’s top left value.
So, I’m modifying my previous formula as follows.
=@COLUMN(A1:D1)
Now, I only get the column number of the top-left value of the array result of the COLUMN function. It is 1 in this case.
Another function in Excel is called COLUMNS. Only the letter ‘S’ is changing in the names of the two functions, COLUMN and COLUMNS. I believe it is important to know the difference between the COLUMN and COLUMNS functions.
- COLUMN function – Returns the column number
- COLUMNS function – Returns the number of columns of an array
Excel also has similar ROW and ROWS functions that can be used to find out the row numbers
Also read: How to Combine Two Columns in Excel (with Space/Comma)
Method 2 – Using R1C1 Reference Style
If I want to show all column references as column numbers rather than column letters, I can simply change that in my Excel file by using the R1C1 reference style.
Our Excel file column headers look like this.
Let’s imagine I want to show the column numbers instead of the column letters in this Excel file.
Then, following the steps below, I can change the column letters to column numbers.
- Go to the File Menu.
- Select Options. It is at the bottom left corner.
- Go to the “Formulas” in the Excel Options dialog box.
- Go to the “Working with formulas” section and check the R1C1 reference style option.
- Click the “OK” button in the Excel Options dialog box.
My Excel file now shows column numbers instead of column letters.
When we show column numbers in our Excel file, all of the existing formulas in our Excel file will automatically change to the R1C1 reference style.
While this does make it easy to know the column number, I don’t recommend using this method as it will require us to change the cell reference syntax from A1 style to R1C1 style (which most users find more complicated to use)
Also read: How to Swap Columns in Excel?
Method 3 – Selecting Cells from Column A
Another straightforward method to find the column number is to select cells from column A and refer to the name box.
In the below table, I have the semester-wise (S1, S2, S3, and S4) average marks of four students.
Now I want to know the column number of semester 3 (S3) average marks.
I can find the column number of S3 as follows.
- Select the first cell of the same row. In other words, I have to select the cell in column A of the same row. So, in this case, I am selecting cell A1.
When I select cell A1, I can see “A1” in the name box.
- Then, hold down the Shift key and press the Right arrow key until we select the cell that we need the column number. So, I am selecting cells up to E1.
- Now, check the name box. The number before the letter C shows the column number.
In this case, the number before the letter C is 5. It means that the column number of the S3 column is 5.
While this is a straightforward method, it’s not the best, as it can only be used on ranges that you can manually select. If you are far away from the first column and want to know the column number, it’s best to use the COLUMN function
You can quickly find the column number in Excel by using these simple methods. These methods are especially useful when working with tons of data with multiple columns.
Other Excel articles you may also like:
