What is Row Function in Google Sheets?
The ROW function in Google Sheets returns the row number of a specified cell. The ROW formula belongs to the group of Lookup functions in Google Sheets. It gives us the row number where the specified cell is located. It is beneficial to refer to the row number dynamically when performing calculations. If you give a range instead of a single-cell reference, we get the position of the first-row number in the provided range. The ROW function is useful for row-based calculations and conditional formatting.
For example, in cell B3, we enter the formula =ROW(A2). The result is 2, as A2 belongs to the second row. However, when it comes to the formula in cell D3, the formula =ROW() returns the number of the row where it is written, which is 3, as there is no argument. Thus, if no reference is provided as an argument, ROW() will return the cell’s row number where we write the formula.
Key Takeaways
- The ROW function returns the row number of a particular cell where the formula is placed or the row number of the cell reference specified.
- The syntax of the ROW function is as follows:
- =ROW([cell_reference]) where the argument cell_reference is optional.
- The formula =ROW() will return the row number of the cell where the formula is placed, and =ROW(B3) will return 3 since cell B3 is in row 3.
- We can combine ROW() with functions like OFFSET() to create dynamic row references within formulas.
Syntax
ROW is a very useful function for row-level manipulations and has a straightforward syntax.
The general syntax is as follows:
=ROW([cell_reference])
cell_reference[Optional]: You can input a cell reference, and the function returns its row number.
As seen in the example above, if you leave the argument blank, the function returns the row number of the cell in which it is located.
How to Use ROW Function in Google Sheets?
Entering the ROW function in Google Sheets can be done in two ways.
- Entering the ROW formula in Google Sheets manually
- Through the Google menu bar
Entering the ROW formula in Google Sheets manually.
We have some data in a range as shown below. Let us try to find the row number of this range.
Step 1: To find the row number of the given range, type “=ROW(.”
Step 2: Now, we enter the cell references of the range. We enter the range A1B3 and close the braces.
Step 3: Press the “Enter” key. You get the row number of the specified range, A1:B3. Please note that if the cell_reference parameter is a range across rows, then the formula returns the row number of the first row of the range specified.
Using the Google Menu bar
- Go to “Insert” → “Function” → “Lookup” → “ROW”.
- Input a specific reference range or leave the argument blank.
- Press the “Enter” key.
If you don’t fill in the argument, you must enter the “()”, parenthesizes.
Examples
The COLUMN function in Google Sheets is one of the lookup functions that gives us the column number where the specified cell is located. If you are wondering where this function would be helpful, let’s look at some scenarios where we can use it.
Example #1
ROW function in Google Sheets with ADDRESS Function
Let us begin our series of examples on the ROW function with an interesting example. The ROW function in Google Sheets can be combined with other functions like ADDRESS for meaningful data extraction based on our requirements. Let’s combine it with the ADDRESS function in Google Sheets for interesting results.
Displays the Address of a Cell
Step 1: Here, the value in cell A2 tells us which row we should reference in Column A. If the value in A2 is 4, it shows the address of A4.
Enter the following function in cell B1. Press Enter.
=ADDRESS(A2,ROW()).
ROW() returns the row number where the formula is pasted. Here, it is B1, so the result is “1.” This function decides which column to select. Since the result is 1, Column A is selected.
ADDRESS(A2,1) – Here, the value in A2 is 4.
Step 2: Press Enter. Hence, the absolute address, that is, $A$4, is displayed.
You get the absolute address of the required cell. To get the relative address of the same, use the following function.
=ADDRESS(A2, ROW(), 4).
Press Enter. Here, the third argument, 4, specifies that we need the relative address.
Step 3: You can also use ROW and ADDRESS to get the cell address for a particular row and column as follows. To get the address of the cell in the 4th row and the 3rd column (cell C4), you write the formula shown below:
=ADDRESS(ROW(A4), 3). It gives the absolute address.
Example #2 – Using ROW with OFFSET function
The ROW function with the OFFSET function is very powerful for dynamic references. Let’s look at the syntax of the OFFSET function.
OFFSET(reference, rows, cols, [height], [width]): It returns a reference to a range that is a specified number of rows and columns from a starting reference.
Step 1: We have some numbers in the range A1:C3. We want to use ROW() with OFFSET to refer to a particular row in column A dynamically.
Enter the following function in cell C4. Press Enter.
=OFFSET(A1, ROW() – 2, 0)
You get the value 5 which is in cell A3. Let us see how!
ROW() – 2 – Here, we enter the formula in C4, i.e., Row 4. Hence, the output is 2.
A1 – The cell from which we start referencing.
The third argument, 0, means there is no offset in the column, and the search is restricted to Column A.
Hence, the output of this function is two rows below A1 and in Column A, which is cell A3. It contains the value 5. Hence, 5 is the output of this function. Simple, isn’t it?
Example #3 – Conditional Formatting Based on Row Number
Let us look at an interesting example of using the ROW function in conditional formatting. Some values are displayed in columns A to D. Let us use conditional formatting to highlight the even rows.
Step 1: First, use the ROW function to select the range for which you want to apply conditional formatting. Here, we select A1:D6.
Now, go to the following place: Format > Conditional Formatting.
Step 2: Now, in the Conditional format rules pane, under Format cells if, select Custom formula is.
Enter the following custom formula.
=ISEVEN(ROW())
This formula uses the ROW formula to get each cell’s row number, and the ISEVEN function checks if the ROW number is odd. If it is, the condition will apply.
Step 3: Now, check how the even rows are highlighted in the table with the conditions we have specified – yellow shading and bold text.
Important Things to Note
- You can dynamically create cell references based on the current column or row by combining the ROW and ADDRESS functions.
- When used in Conditional Formatting, the ROW function helps create dynamic rules based on the column numbers.
- ROW() returns the row number of the current cell where the formula is placed.
- If the reference argument is a range of cells, the ROW function returns the leftmost column’s top cell number.
- We cannot use the reference argument to refer to multiple areas.
Frequently Asked Questions (FAQs)
We can use ROW() with OFFSET,0 which is very helpful to reference cells dynamically. The OFFSET function allows you to return a value at a reference that is a specified number of rows and columns from a given starting reference. For instance, =OFFSET(A1, ROW() – 2, 0) if typed in B4 will return the value in cell A3 since the OFFSET function moves 2 rows down from A1 and zero columns.
Using ROW in Google Sheets, we can
1. Get the row number of a particular cell or range.
2. Create dynamic formulas based on the row number.
3. Generate a sequence of numbers in a column.
4. Perform conditional formatting or complex formulas based on row numbers.
5. Use the ROW() function with other functions like COUNTA()to find the last filled row in a column.
The ROW function is used to return the row number of a given reference or cell. If you provide a range as an argument to ROW(), it will return the row number of the first row of the range. To get the ROW number of a cell, use ROW() with a specific cell reference.
Download Template
This article must help understand Row Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Row Function in Google Sheets. We learn its syntax & how to use it to find the row number with examples & working template. You can learn more from the following articles. –
Leave a Reply