How to Find the Column Number in Excel

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()
Column function to get the column number

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)
Column function to get the column number of cell reference

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)
finding column number of range

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)
finding column number of first cell in range

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.

column references are shown as column letters

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.

  1. Go to the File Menu.
Click the file tab
  1. Select Options. It is at the bottom left corner.
Click on Options
  1. Go to the “Formulas” in the Excel Options dialog box.
Click on the Formulas option
  1. Go to the “Working with formulas” section and check the R1C1 reference style option.
Check the R1C1 reference style option
  1. Click the “OK” button in the Excel Options dialog box.
Click the ok button

My Excel file now shows column numbers instead of column letters.

Column labels are now numbers

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.

Dataset with student scores

Now I want to know the column number of semester 3 (S3) average marks.

I can find the column number of S3 as follows.

  1. 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.
Select the first cell in the row

When I select cell A1, I can see “A1” in the name box.

  1. 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.
Select till the cell where you want to know the column number
  1. Now, check the name box. The number before the letter C shows the column number.
Check the namebox

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment