How to use the ROW function
The ROW function calculates the row number of a cell reference.
What is on this page?
1. ROW Function Syntax
ROW(reference)
2. ROW Function Arguments
reference | Optional. A reference to a cell you want to know the row number of. |
The ROW function is one of my most used Excel functions, that you probably already know if you have been browsing around my web site.
The first example demonstrates basic usage. Example 2 and 3 are more advanced and requires some knowledge about MATCH and INDEX function.
3. Example 1 - Cell references
You can enter a reference to a single cell or a cell range in the ROW function. Note that it will return an array of numbers if you enter a reference to a cell range, as long as you enter it as an array formula.
Formula in cell B2:
B2 is a relative cell reference meaning it changes when you copy the cell and pastes it to another cell.
ROW(B2)
returns 2.
Formula in cell B6:
This example shows that you can omit the argument, the formula will default to the cell where the formula is entered.
The formula is entered in cell B6 in the example above, so it returns 6.
Formula in cell B11:
$D$10 is an absolute cell reference meaning it won't change if you copy the cell and paste to other cells. Absolute and relative references in Excel
returns 10.
The following example is not demonstrated in the image above. Type this formula in any cell:
To enter the formula as an array formula, follow these steps: Press and hold CTRL + SHIFT simultaneously, then press Enter. Release all keys.
The formula is now surrounded by curly brackets, like this: {=ROW(A2:A5)}. Don't enter these characters yourself, they appear automatically.
The cell will now only show the first value in the array which is 2, you must enter the array formula in a cell range equal of size as there are values in the array to be able to see them all.
Update - Dynamic arrays
Office 365 subscribers can now take advantage of dynamic arrays. They behave differently than regular array formulas, simply enter them as a regular formula and Excel will automatically extend the formula to cells below so you can see all values.
This behavior is called spilling, see image above. The cell range containing spilled values have a blue border, it will disappear as soon as you press with left mouse button on outside the cell range.
Microsoft recommends that you use dynamic arrays instead of regular array formulas, however, they are still working and compatible with older Excel versions.
4. Create an array of sequential numbers
The image above demonstrates a formula that creates an array of sequential numbers from 1 to 4. This can be used to number any cell range in order to get the appropriate values/rows.
Here are a few examples: Extract all rows from a range that meet the criteria in one column | 5 easy ways to VLOOKUP and return multiple values | INDEX MATCH - partial match multiple columns
Excel 365 subscribers can use the SEQUENCE function which offers more granular control. Most Excel 365 functions don't need the SEQUENCE function to number values/rows. For example, the FILTER function keeps track of values by itself.
Array formula in cell B2:
4.1 How to enter an array formula
- Select cell range B2:B5 with your mouse.
- Type: =ROW(A1:A4)
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The formula in the formula bar has a leading and ending curly bracket, they appear automatically when you follow all 5 steps above.
Don't enter these characters yourself.
5. Example 2 - Duplicate columns in an array
This example demonstrates that if you provide a cell reference pointing to a cell range containing multiple columns the formula will still only return a single column of row numbers.
returns this array {3; 4; 5}.
The semicolon is a separating character telling you that the values are on a row each. A comma is used to separate values distributed across columns.
Now you might wonder why the array formula doesn´t return {3, 3; 4, 4; 5, 5}. There are six cells in the cell range, how come only three values are returned?
The answer is that there is no need for multiple duplicate columns in the array. Excel simplifies the array down to a single column.
But when used with multiple cell ranges in more complicated array formulas, make sure the number of rows matches.
See this example: Unique distinct values from a cell range
6. Example 3 - Number rows in any cell range
The INDEX function returns a value of the cell at the intersection of a particular row and column, in a given range. To be able to work with multiple values from an arbitrary cell range using the INDEX function we must number each row.
That is exactly what we did in the previous example but the first row in a given range has to be 1 and the second 2 and so on. Why? The INDEX function needs properly numbered cell ranges. This is where the ROW and MATCH function comes in.
The beauty with this formula is that it returns row numbers from any cell range no matter size or location.
Array formula in cell range B3:B5:
becomes =MATCH({3; 4; 5}, {3; 4; 5}) and returns {1; 2; 3}. This array has the same number of values as there are rows in cell range C3:D5.
Remember, enter the formula as an array formula.
So what can you do with row numbered cell ranges?
The MATCH function can also be used to find the relative position of a value in a cell range. See these posts:
- How to extract unique distinct values from a column
- Repeat values
- Fetch data from another table
- Shift Schedule
7. How to number rows?
The image above demonstrates how to number records in a data set, however, this method is not great and I will show you why.
Here are the steps:
- Select cell B3.
- Type: =ROW(A1)
- Press Enter.
- Copy cell B3.
- Paste to cells below as far as needed.
Why not use this formula? The formula will break if you insert more rows above, the cell reference is relative meaning it will change. The same thing will happen with an absolute formula.
I recommend you use the ROWS function with an expanding cell reference.
Formula in cell B3:
Copy cell B3 and paste to cells below, this will adjust the expanding cell reference accordingly. This will not break if you insert more rows.
'ROW' function examples
The following 186 articles contain the ROW function.
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This post explains how to lookup a value and return multiple values. No array formula required.
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Question: List of data and blank cells in a column which will be added from day to day. There are […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they […]
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
Lookup with criteria and return records.
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
I this article I will show you how to get numerical values from a cell range manually and using an […]
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
Functions in this article
Functions in 'Lookup and reference' category
The ROW function function is one of many functions in the 'Lookup and reference' category.
Excel function categories
Excel categories
One Response to “How to use the ROW function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] ROW(reference) Returns the rownumber of a reference […]