How does COLUMN behave when I reference an entire column like A:A?

When you pass a whole-column reference, COLUMN returns the column number of that column (1 for A:A). In array-compatible contexts it can also return an array of the same number repeated for each column in the range, but most spreadsheets collapse it to a single value.

Can COLUMN be used inside an IF statement to conditionally format rows?

Yes. You can nest COLUMN inside IF to test the column position, for example =IF(COLUMN()=3, "Dept", "Other") will output "Dept" only in column C. This is useful for creating column-specific logic without hard-coding the column letter.

Why does COLUMN sometimes return an array instead of a single number?

If the argument is a multi-column range, COLUMN returns an array containing the numeric index of each column in the range. In Excel the array spills horizontally; in Google Sheets it appears as an array literal. Use functions like SUM or INDEX to collapse the array if you need a single result.

Is there a way to get the column number of the last used column in a sheet?

COLUMN alone cannot locate the last used column. Instead, combine it with functions like LOOKUP or COUNTA, for example =MAX(COLUMN(A1:Z1)*(A1:Z1<>"")) which scans a row and returns the highest column index that contains data.

COLUMN function

COLUMN returns the column number of a given reference, or the column of the cell containing the formula when no reference is supplied.

=COLUMN([reference])

Generate a COLUMN formula

Describe what you need. The generator will reach for COLUMN where COLUMN is the right tool, and tell you when it is not.

Arguments

How COLUMN reads its arguments

Argument	Required	Description
reference	Optional	Optional cell or range reference; if omitted, COLUMN uses the cell where the formula resides; non-reference values trigger a #VALUE! error.

Returns

A numeric column index; returns a single number or an array of numbers matching the width of the reference.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Identify the Salary column number

Employee ID	Name	Department	Hire Date	Salary	Status
101	Alice	Finance	2020-01-15	72000	Active

=COLUMN(F2)

Result: 6

The formula points to cell F2, which lies in column F of the sheet. COLUMN translates column F to its numeric index, which is 6. Because the argument is a single cell, the function returns just that one number.

2. Return column numbers for a block of data

Employee ID	Name	Department	Hire Date	Salary	Status
102	Bob	Marketing	2019-07-30	65000	Active

=COLUMN(C2:E2)

Result: {3,4,5}

C2:E2 spans three adjacent columns: C, D, and E. COLUMN evaluates each column in the range and produces an array of their numeric positions—3 for C, 4 for D, and 5 for E. In Excel the result spills into three horizontal cells; in Google Sheets it appears as an array literal.

3. Get the column of the current cell without an argument

Employee ID	Name	Department	Hire Date	Salary	Status
103	Carol	HR	2021-03-12	58000	On Leave

=COLUMN()

Result: 2

When COLUMN is called with no reference, it automatically uses the address of the cell that contains the formula. If the formula sits in column B (the second column), the function returns 2 regardless of the data in the sheet.

Common errors

Which COLUMN error are you seeing?

Error	Why it happens	How to fix it
#REF!	The reference points to a non-existent column or row, such as =COLUMN(Z1) after column Z has been deleted.	Adjust the reference to a valid cell or range that actually exists on the worksheet.
#VALUE!	A non-reference argument is supplied, for example =COLUMN("Salary") where a text string is used instead of a cell address.	Replace the text with a proper cell reference like =COLUMN(F2) or remove the argument to use the implicit current-cell reference.
#SPILL!	An array result from COLUMN cannot expand because neighboring cells are occupied, e.g., =COLUMN(C2:E2) entered in a cell where the three-column spill area is blocked.	Clear the obstructing cells or move the formula to a location with enough empty space to accommodate the spill.

Tips and when to use something else

Combine COLUMN with ROW to build dynamic cell addresses using the ADDRESS function.
When you need the column index of a header name rather than a hard-coded cell, use MATCH on the header row instead of COLUMN.
COLUMN always returns the same number for every cell in a column when used without an argument, making it handy for conditional formatting rules.
If you require the total number of columns in a range, wrap COLUMN with COLUMNS, e.g., =COLUMNS(A2:F2).

Related functions

ROWROW returns the row number of a given cell or range, or the row of the formula itself when no reference is supplied.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.

Frequently asked questions

How does COLUMN behave when I reference an entire column like A:A?: When you pass a whole-column reference, COLUMN returns the column number of that column (1 for A:A). In array-compatible contexts it can also return an array of the same number repeated for each column in the range, but most spreadsheets collapse it to a single value.
Can COLUMN be used inside an IF statement to conditionally format rows?: Yes. You can nest COLUMN inside IF to test the column position, for example =IF(COLUMN()=3, "Dept", "Other") will output "Dept" only in column C. This is useful for creating column-specific logic without hard-coding the column letter.
Why does COLUMN sometimes return an array instead of a single number?: If the argument is a multi-column range, COLUMN returns an array containing the numeric index of each column in the range. In Excel the array spills horizontally; in Google Sheets it appears as an array literal. Use functions like SUM or INDEX to collapse the array if you need a single result.
Is there a way to get the column number of the last used column in a sheet?: COLUMN alone cannot locate the last used column. Instead, combine it with functions like LOOKUP or COUNTA, for example =MAX(COLUMN(A1:Z1)*(A1:Z1<>"")) which scans a row and returns the highest column index that contains data.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator