COLUMN Function Examples – Excel & Google Sheets

This Tutorial demonstrates how to use the Excel COLUMN Function in Excel to look up the column number.

COLUMN Main Function

COLUMN Function Overview

The COLUMN Function Returns the column number of a cell reference.

To use the COLUMN Excel Worksheet Function, select a cell and type:

column formula syntax

(Notice how the formula inputs appear)

COLUMN function Syntax and inputs:

reference – Cell reference that you want to determine the column # of.

 

AutoMacro - VBA Code Generator

COLUMN Function – Single Cell

The COLUMN Function returns the column number of the given cell reference. 

COLUMN EX 01

COLUMN Function with no Reference

If no cell reference is provided, the COLUMN Function will return the column number where the formula is entered

COLUMN EX 02

COLUMN Function with a Range

You can also input entire ranges of cells into the COLUMN Function. When doing so, the COLUMN Function behaves differently in Excel 2019 (or earlier) vs. Excel 365 or newer version of Excel.

Excel 2019 or older

In previous versions of Excel, the COLUMN Function returns an array containing the column values of all the cells in the range, but only displays the first result in the cell.

COLUMN EX 03

If you click the cell containing the formula and press F9, all the results are displayed in curly brackets as an array.

COLUMN EX 04

Excel 365

However, Excel 365 (and newer versions of Excel, presumably) comes with a spill range feature. Here, the COLUMN Function will return the columns of all cells in the range, “spilled” into the next cells.

COLUMN EX 05

COLUMN Function in Google Sheets

The COLUMN Function works exactly the same in Google Sheets as in Excel:

COLUMN Google Function

Additional Notes

Use the COLUMN Function to return the column number of a cell reference. What if you want the column letter of a cell reference? Use this complicated formula instead:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
This formula calculates the column number with the COLUMN Function and calculates the address of a cell in row 1 of that column using the ADDRESS Function. Then it uses the SUBSTITUTE Function to remove the row number (1), so all that remains is the column letter.

Return to the List of all Functions in Excel

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!