Find Data with Excel's ROW and COLUMN Functions

The ROW function can be used to:

  • return the number for a row of a given cell reference
  • return the number of the row for the cell where the function is located in the worksheet
  • when used in an array formula, the function will return a series of numbers identifying the numbers of all rows where the function is located

The COLUMN function can be used to:

  • return the number of the column for the cell where the function is located in the worksheet
  • return the number for a column of a given cell reference

In an Excel worksheet,

  • rows are numbered top to bottom with row 1 being the first row;
  • columns are numbered left to right with column A being the first column.

Therefore, the ROW function would return the number 1 for the first row  and 1,048,576 for the last row of a worksheet.

01
of 02

The ROW and COLUMN Functions Syntax and Arguments

Find Row and Column Numbers with Excel's ROW and COLUMN Functions
Find Row and Column Numbers with Excel's ROW and COLUMN Functions. © Ted French

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the ROW function is:

= ROW ( Reference )

The syntax for the COLUMN function is:

= COLUMN ( Reference )

Reference - (optional) the cell or range of cells for which you want to return the row number or column letter.

If the reference argument is omitted,

  • the ROW function returns the row number of the cell reference where the function is located - row two above;
  • the COLUMN function returns the column number of the cell reference where the function is located - row three above.

If a range of cell references is entered for the Reference argument, the function returns the row or column number of the first cell in the supplied range - rows six and seven above.

 

02
of 02

Examples Using Excel's ROW and COLUMN Functions

The first example - row two above - omits the Reference argument and returns the row number based on the function's location in the worksheet.

The second example - row three above - returns the column letter of the cell reference (F4) entered as the Reference argument for the function.

As with most Excel functions, the function can be typed directly into the active cell - example one - or entered using the function's dialog box - example two.

Example 1 - Omitting the Reference Argument with the ROW Function

  1. Click on cell B2 to make it the active cell;
  2. Type the formula =ROW() into the cell
  3. Press the Enter key on the keyboard to complete the function;
  4. The number " 2 " should appear in cell B2 since the function is located in second row of the worksheet;
  5. When you click on cell B2 the complete function =ROW ( ) appears in the formula bar above the worksheet.

Example 2 - Using the Reference Argument with the COLUMN Function

  1. Click on cell B5 to make it the active cell;
  2. Click on the Formulas tab of the ribbon menu;
  3. Choose Lookup and Reference from the ribbon to open the function drop down list
  4. Click on COLUMN in the list to bring up  the function's dialog box;
  5. In the dialog box, click on the Reference line;
  6. Click on cell F4 in the worksheet to enter the cell reference into the dialog box;
  7. Click OK to complete the function and return to the worksheet;
  8. The number " 6 " should appear in cell B5 since the cell F4 is located in the sixth column - column F - of the worksheet;
  9. When you click on cell B5 the complete function =COLUMN(F4) appears in the formula bar above the worksheet.