## How to use the ROW function

The ROW function calculates the row number of a cell reference.

### Excel Function Syntax

ROW(*reference*)

### Arguments

reference |
Optional. A reference to a cell you want to know the row number of. |

### Comments

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.

### 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.

### 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

### 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.

*cell_range*), ROW(

*cell_range*))

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

#### 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 184 articles have formulas containing 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 a formula that extracts values that exist only in one column out of two columns. There are […]

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

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 […]

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 column D and E. Array […]

This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]

The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]

I this article I will show you how to get numerical values from a cell range manually and using an […]

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

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) […]

Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]

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 is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

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 […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

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 […]

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 […]

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 […]

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]

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 […]

Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]

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 […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

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 […]

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 […]

This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

### Functions in 'Lookup and reference' category

The ROW function function is one of many functions in the 'Lookup and reference' category.

### 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.

**Contact Oscar**

You can contact me through this contact form

[…] ROW(reference) Returns the rownumber of a reference […]