Find data with ROW and COLUMN functions in Excel

The ROW function in Excel returns the row number of a reference. The COLUMN function returns the column number. The examples in this lesson show how to use these ROW and COLUMN functions.

Remark . The instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

Content
  1. Using the ROW and COLUMN function
  2. Syntax and arguments of the ROW and COLUMN functions
  3. Example 1 – omit reference argument with ROW function
  4. Example 2 – Using a reference argument with the COLUMN . function

Using the ROW and COLUMN function

The ROW function is used to:

  • Return the number for the row of the given cell reference.

  • Return the row number for the cell containing the function in the worksheet.

  • Returns a sequence of numbers that identifies the numbers of all the rows the function is in, when used in an array formula.

The COLUMN function is used to:

  • Return the column number for the cell containing the function in the worksheet.

  • Return the number for the column of the given cell reference.

In an Excel sheet, rows are numbered from top to bottom, with row 1 being the first row. The columns are numbered from left to right, with column A being the first column.

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

Syntax and arguments of the ROW and COLUMN functions

Function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.

Syntax for the ROW function:

 = СТРОКА (ссылка) 

Syntax for the COLUMN function:

 = COLUMN (ссылка) 

Clutch (not necessary). 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 following happens:

  • The ROW function returns the row number of the cell reference where the function is located (see row 2 in the examples above).

  • The COLUMN function returns the column number of the reference to the cell where the function is located (see line 3 in the examples 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 specified range (see lines 6 and 7 in the examples above).

Example 1 – omit reference argument with ROW function

The first example (see line 2 in the examples above) skips the Reference argument and returns the line number based on the location of the function on the worksheet.

As with most Excel functions, the function can be entered directly into the active cell or using the function’s dialog box.

To enter a function in the active cell, follow these steps:

  1. Select cell B2 to make it active.

  2. Enter formula =ROW() in a cell.

  3. Press key Enter on the keyboard to complete the function.

The number 2 appears in cell B2 because the function is in the second row of the worksheet.

When you select cell B2, the full function =ROW() appears in the formula bar above the worksheet.

Example 2 – Using a reference argument with the COLUMN . function

The second example (see line 3 in the examples above) returns the column letter of the cell reference (F4) entered as the reference argument for the function.

  1. Select cell B5 to make it active.

  2. Select tab formulas .

  3. Select Search and help to open the drop-down list of functions.

  4. Select COLUMN in the list to open the Function Arguments dialog box.

  5. Place the cursor on the line in the dialog box Clutch .

  6. Select cell F4 on the sheet to enter a cell reference in the dialog box.

  7. Select Okay to end the function and return to the worksheet.

The number 6 appears in cell B5 because cell F4 is in the sixth column (column F) of the worksheet.

When cell B5 is selected, the full function =COLUMN(F4) appears in the formula bar above the worksheet.

Since Excel Online does not have a Formulas tab on the ribbon, you can use the following method, which works in all versions of Excel.

  1. Select cell B5 to make it active.

  2. Press the button Insert function next to the formula bar.

  3. Select Search and help in the category list.

  4. Select Column in the list and select Okay .

  5. Select cell F4 on the sheet to enter a cell reference.

  6. Press key Input .

The number 6 appears in cell B5 because cell F4 is in the sixth column (column F) of the worksheet.

When cell B5 is selected, the full function =COLUMN(F4) appears in the formula bar above the worksheet.

Leave a Reply

Your email address will not be published.