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 🙂

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

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.

Get your FREE exercise file

To help you get the hang of the COLUMN function, we’ve created an example spreadsheet with some data.

Download it for free below and follow along!

Download the FREE Exercise File

Download exercise file

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.

column-function

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 Spreadsheeto

Now let’s try using a reference.

In the same cell, type the following formula:

=COLUMN(GG110)

Hit Enter.

column-function-reference

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.

column-increment-absolute

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.

column-increment-result

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 Spreadsheeto

You 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 Spreadsheeto

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