The COLUMN function in Google Sheets is **useful to get the column number of a specific cell**.

##### Table of Contents

As the name of the function suggests, we can use this functionality to return the column number. We can return it of the same cell where we use this formula. We can even get the column number of any other referred cell in the sheet.

Let’s take an example.

Say we want to create a sheet and you need a numbering in the first row of your sheet. We want to have 1 in column A, 2 in column B and so on.

We don’t want to enter the column numbers one by one, but we want to do it in a more automated way.

So how do we do that?

Simple. The `COLUMN`

function only needs the cell reference to output the column number.

Or even without a reference, we can output the column number of the same cell where we use this formula.

Let’s dive into some examples to show you how we can write our own COLUMN function in Google Sheets to calculate those data.

## The Anatomy of the COLUMN Function

The syntax (the way we write) the `COLUMN`

function is as follows:

=COLUMN([cell_reference])

Let’s break this down and understand what each of these terminology means:

`=`

the equal sign is how we start every function in Google Sheets.`COLUMN`

is our function. We will have to add the values into it for it to work.`cell_reference`

is an optional attribute which we can use in our function. It is the reference to the cell whose column number we require. If you don’t specify this attribute, the`COLUMN`

function returns the column number of the cell where we enter the formula.

### Return the Column Number of a Referred Cell

Have a look at the way the `COLUMN`

function works.

Let’s write the following function in any cell:

=COLUMN(D2)

It’s pretty straightforward. It returns **4** because column **D** is the fourth column.

It doesn’t matter in which cell you use this formula as it still returns the column number of the cell **D2**.

### Return the Column Number without Referring a Cell

As you can see, the single `cell_reference`

attribute of this function is optional. So we can use this function without any attributes.

Let’s try it.

We put the following simple formula into every cell from **A1** to **E5**:

=COLUMN()

The `COLUMN`

function returns the column number of the cell that contains the formula.

As a result, we got the column numbers of these cells, where column **A** means **1**, column** B** means **2** and so on.

### Return the Column Number with a Referred Range of Cell

There is another thing we need to mention when discussing the syntax of the `COLUMN`

function.

It is possible to add a reference to a range of cells instead of a single cell.

The function we write in cell **A1** is as follows:

=COLUMN(C7:E12)

Now you might wonder what happens here.

Simple. The `COLUMN`

function returns only the column number of the first column within the `cell_reference`

and ignores the rest of the range.

Below we will be showing another example when we actually return the column numbers in a whole range of cells. But in that case, we will use an extra function too. Without any further functions, the `COLUMN`

function only returns the column number of the first cell of the range.

## A Real Example of Using COLUMN Function

Have a look at the example below to see how the `COLUMN`

function is used in Google Sheets to output several column numbers.

The example above shows how we used the `COLUMN`

function to automatically fill out the column numbers. We did this without having to enter them manually.

Here we wrapped the `COLUMN`

function into an `ARRAYFORMULA`

function. The ARRAYFORMULA function is useful to apply a formula to an entire row or column in Google Sheets. Read more about its use in our previous article.

The function to fill out the column numbers is:

=ARRAYFORMULA(COLUMN(A1:H1))

Here’s what this example does:

- Firstly, we made a cell active. This is where we will start our
`ARRAYFORMULA`

and`COLUMN`

formula. For this guide, we selected cell A1. - Next, we started off our formula with an equal sign, followed by our first function,
`ARRAYFORMULA`

. - After that, we wrapped the
`COLUMN`

function into it as its attribute. - Then, we added the attributes of the
`COLUMN`

function, which is the range of cells where we wanted to return the column numbers. This is**A1:H1**in this example. - As a result, you can see the right column numbers from 1 to 8 throughout the cells
**A1**to**H1**. - Note that here the
`ARRAYFORMULA`

takes care of applying the`COLUMN`

function to all of the cells one by one that is referenced in the range. Unlike above in the section “Return the Column Number with a Referred Range of Cell”, the ranges are not ignored here. This is because of the way the`ARRAYFORMULA`

function works.

See how simple that is!

Go ahead and give it a shot! 😀 Using the link I have attached below you can make a copy of the spreadsheet:

### Modifying the Sheet After Using the COLUMN Function

Now let’s see how this solution exactly works. What happens to the numbering if we try to modify the columns?

- Firstly, say we want to add a new column before column
**D**.

As you can see, we can add a column anywhere in between. The formula automatically readjusts the numbering, since it always returns the actual column number of the cells. With the additional column, our numbering changes to **1 to 9** now and expands until cell **I1**.

- Now let’s try adding a new column at the beginning, so before the current column
**A**.

In this case, the new column is added, but it doesn’t have a numbering.

There is a simple reason for this.

When we added the new column, our `ARRAYFORMULA`

wrapped `COLUMN`

function didn’t move from its cell. Although it is now not called cell **A1** but **B1**.

The function changed automatically according to the new column positions, so it is now as follows:

=ARRAYFORMULA(COLUMN(B1:I1))

Of course, it doesn’t mean that the function works wrong. It still outputs the column number of each cell as it is expected from cell **B1** to **I1**.

## How to Use COLUMN Function in Google Sheets

Let’s see how to write your own COLUMN function in Google Sheets step-by-step.

- To start off, click on the cell where you want to start showing your results. For the purposes of this guide, I will be choosing
**A1,**where I will write my formula.

- Next, type the equal sign ‘
**=**’ to begin the function. Then followed by the name of the first wrapping function. It is the ‘`arrayformula`

‘ (or`ARRAYFORMULA`

, whichever works).

- You should now see that the auto-suggest box will pop-up with the name of the function. Click on it to start your function.

- After the opening bracket ‘
**(**‘, you have to add the`COLUMN`

function. Start writing its name and again, select it from the pop-up auto-suggest box. Make sure to select the`COLUMN`

function and not`COLUMNS`

, which is a totally different function!

- After that, add the range of cells whose column numbers you want to output. For example, I’m putting the range
**A1:H1**to return the column numbers of these cells. You can type the cell references manually, or you can simply click on them or highlight them to add them to your function.

- Finally, if you added all the cells you wanted to include, hit the
**Enter**key to close the brackets and get the result. Great! If you followed my steps, you would see the column numbers from**A1**to**H1**.

That’s it, good job! You can now use the `COLUMN`

function together with the **other numerous Google Sheets formulas** to create even more powerful formulas that can make your life much easier. 🙂