How to find the column number in Excel?

How to find the column number in Excel?
Last Updated on 06/06/2023
Reading time: 2 minutes

There are 2 ways to know the column number in Excel. One is an Excel option, and the other one is a function.

Method 1: Change column headers from general options

In Excel, column headers are represented by the letter A, B, C, ... And the final column is XFD. This means column number ... 16384 😲😲😲 But how to easily convert the column letter to a number?

  1. Go to the File menu
  2. Then, you open the Options menu
  3. Then you go to the Formulas section.
  4. And there you check the option Reference Style R1C1
Option to change the cell reference is R1C1

Reference R1C1 means that each cell is identified by numbers. And then, the columns headers are numbers

Column headers are numbers

FYI, you should know that it was the only way to identify the cell references in the first Excel version. Fortunately, the letters for identifying columns were added later to make cell references easier to read 😉

This method isn't the one I will recommend because not only the column headers have changed, but also the reference in your formulae.

Formula with Reference R1C1

As you can see, it's not easy to understand such a formula and nearly impossible to visualize the absolute or relative reference

Method 2: Use a formula to display the column number

But the simplest technique is to use the COLUMN function 😀👍

=COLUMN()

And that's it! This function returns the column number of the cell which is the formula. This function doesn't need an argument.

In this example, we know that our table contains 44 columns (46 - 2). Minus 2 because we have written or formula 2 cells after the last column of the table.

Function COLUMN to return the column number

The ROW function isn't so helpful because it is enough to read the row number directly in the row headers.

Leave a Reply

Your email address will not be published. Required fields are marked *

How to find the column number in Excel?

Reading time: 2 minutes
Last Updated on 06/06/2023

There are 2 ways to know the column number in Excel. One is an Excel option, and the other one is a function.

Method 1: Change column headers from general options

In Excel, column headers are represented by the letter A, B, C, ... And the final column is XFD. This means column number ... 16384 😲😲😲 But how to easily convert the column letter to a number?

  1. Go to the File menu
  2. Then, you open the Options menu
  3. Then you go to the Formulas section.
  4. And there you check the option Reference Style R1C1
Option to change the cell reference is R1C1

Reference R1C1 means that each cell is identified by numbers. And then, the columns headers are numbers

Column headers are numbers

FYI, you should know that it was the only way to identify the cell references in the first Excel version. Fortunately, the letters for identifying columns were added later to make cell references easier to read 😉

This method isn't the one I will recommend because not only the column headers have changed, but also the reference in your formulae.

Formula with Reference R1C1

As you can see, it's not easy to understand such a formula and nearly impossible to visualize the absolute or relative reference

Method 2: Use a formula to display the column number

But the simplest technique is to use the COLUMN function 😀👍

=COLUMN()

And that's it! This function returns the column number of the cell which is the formula. This function doesn't need an argument.

In this example, we know that our table contains 44 columns (46 - 2). Minus 2 because we have written or formula 2 cells after the last column of the table.

Function COLUMN to return the column number

The ROW function isn't so helpful because it is enough to read the row number directly in the row headers.

Leave a Reply

Your email address will not be published. Required fields are marked *