How to Use the ROW and COLUMN Functions in Google Sheets

The ROW and COLUMN functions in Google Sheets are incredibly simple on the surface: they tell you the numerical coordinate of a cell. If you type =ROW(A5), it outputs 5. If you type =COLUMN(C1), it outputs 3.

Because they seem so basic, many users ignore them. However, when combined with advanced array formulas or used to generate dynamic number sequences, ROW and COLUMN become indispensable tools for spreadsheet automation.

In this guide, you will learn the mechanics of the ROW and COLUMN functions and how to use them to solve complex formatting and numbering challenges.

The Basic Syntax

Both functions follow the exact same, single-argument syntax:

=ROW([cell_reference])
=COLUMN([cell_reference])

• cell_reference (optional): The cell whose coordinate you want to find. If you leave the brackets completely empty (e.g., =ROW()), Google Sheets will output the coordinate of the cell where the formula itself is written.

Use Case 1: Creating an Unbreakable ID List

When you are building a database, you often need a column of sequential ID numbers (1, 2, 3, 4…). If you manually type these numbers, and later decide to delete row 5, your numbering sequence breaks (1, 2, 3, 5). You then have to manually re-drag the numbers to fix them.

You can use the ROW function to create a self-healing sequence.

Assuming your headers are in row 1, and your first data entry is in cell A2, click into A2 and type:

=ROW() - 1

Because the formula is in row 2, ROW() outputs 2. Subtracting 1 gives you your starting ID of 1. If you drag this formula down 100 rows, it will perfectly number them 1 through 100. If you ever delete row 5, the formulas instantly recalculate based on their new physical row positions, perfectly repairing the sequence.

Use Case 2: Generating Arrays with SEQUENCE

Modern Google Sheets often rely on the SEQUENCE function to generate arrays of numbers for complex calculations. You can feed the ROW function into SEQUENCE to dynamically determine how many numbers to generate.

For example, you want a sequence of numbers that is exactly as long as a list of names in Column B.

=SEQUENCE(COUNTA(B:B) - 1)

Wait, that’s COUNTA. How do we use ROW? You can use ROW to find the last row of a specific array. For instance, if you want to generate a list of row numbers for a specific range D5:D20, you can use:

=ARRAYFORMULA(ROW(D5:D20))

This single formula will instantly output a vertical column containing the numbers 5 through 20. This is highly useful when building complex INDEX or VLOOKUP arrays that require a specific matrix of coordinates to function.

Use Case 3: Dynamic VLOOKUP Column Indexes

When writing a VLOOKUP, you have to specify which column to pull data from (the index number). If you are dragging a VLOOKUP horizontally across 10 columns to populate a dashboard, manually changing the index number from 2, to 3, to 4 in every single cell is tedious.

You can replace the hardcoded index number with the COLUMN function.

=VLOOKUP($A2, Data!$A$1:$Z$100, COLUMN(B1), FALSE)

Because COLUMN(B1) evaluates to 2, the VLOOKUP pulls from the 2nd column. When you drag this formula to the right, B1 naturally changes to C1, which evaluates to 3, causing the VLOOKUP to automatically pull from the 3rd column.

By understanding how to dynamically reference grid coordinates, you can build self-adjusting formulas that save hours of manual data entry.