How To Get A Column Number In Excel With The Function “COLUMN“
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Figuring out a column number is simple—just look at the letter above the column and you’ll have it.
But sometimes you need a function that will tell you the column number.
Maybe you want it displayed for easy reference?
Or to use it in another function?
Whatever the reason, the COLUMN function is what you need!
Here’s exactly how it works 🙂
What does the COLUMN function do?
Before we look at some of the uses of COLUMN, let’s talk about exactly what it does. It’s extremely simple.
Here’s the syntax:
The syntax of the COLUMN function
=COLUMN([reference])
There’s only a single argument, reference. And it’s optional. You can run the function without it.
With no reference, the function returns the column of the cell that contains the formula.
reference is any cell reference. When you include this reference, the function returns the column number of the specified cell.
COLUMN is as simple as it sounds: you tell it which cell you’re interested in, and it gives you the column number.
Try it in any spreadsheet, and you’ll see that it gives you very predictable results.
How to use the COLUMN function
Open up the example spreadsheet, and we’ll take a look at exactly how to use the COLUMN function.
First, click into any cell, and type the following formula:
=COLUMN()
Then hit Enter.
The cell will now display its own column number.
Because I put the function in cell B6, it returned 2—B is the second column.
Kasper Langmann, Co-founder of SpreadsheetoNow let’s try using a reference.
In the same cell, type the following formula:
=COLUMN(GG110)
Hit Enter.
Even though you’re in the same cell, this one gives you a different result: 189.
No matter where you typed the formula, Excel will look at the GG column, because you specified it in the reference cell.
Incrementing calculations with COLUMN
Like the ROW function, COLUMN can be used to quickly increment calculations.
Take a look at the example spreadsheet and you’ll see what I mean. In column A, we have a sales goal.
Now, let’s increment that goal into the month columns so that January’s goal is $10,000, February’s goal is $20,000, March’s is $30,000, and so on.
First, click into cell C2.
Then type this formula:
=$A$2*(COLUMN()-2)
With this formula, we’ll multiply the sales goal by the column number of the specified cell minus two.
So we’ll multiply A2 by one in row C, two in row D, and so on down the line.
Hit Enter.
This first one is simple: you get the same number.
Now use the fill handle to drag the formula to the right a few cells.
As you can see, each month now has the original sales goal multiplied by the month number.
If your numbers look strange, double-check to make sure you added a dollar sign before the row and column number of the original reference ($A$2).
This absolute reference is crucial for making this formula work.
Kasper Langmann, Co-founder of SpreadsheetoYou can use this same method to iterate any calculation across columns.
Keep in mind that we use this with simple multiplication here, but you can combine COLUMN with any other function if you need it.
Kasper Langmann, Co-founder of SpreadsheetoReturning multiple results from search functions
One of the big disadvantages of Excel’s search and find functions, like INDEX and MATCH, is that they only return a single result.
That can make things difficult if you need to find a lot of matching cells.
However, you can use COLUMN to tell Excel to return multiple results. The process is complicated, but this video explains it nicely:
Count column numbers faster than ever
Getting the column number of any cell is easy . . . but using it in a function isn’t always so straightforward.
With the COLUMN function, you can use any column number in your calculations.
The function may not seem all that powerful at first, but keep an eye out for where you could use it in your daily work.
You might be surprised how useful it is!
Kasper Langmann, Co-founder of Spreadsheeto