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?
- Go to the File menu
- Then, you open the Options menu
- Then you go to the Formulas section.
- And there you check the option Reference Style R1C1
Reference R1C1 means that each cell is identified by numbers. And then, the columns 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.
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 😀👍
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.
The ROW function isn't so helpful because it is enough to read the row number directly in the row headers.