Purpose
Get the number of rows in an array or reference.
Return value
Number of rows
Syntax
=ROWS(array)- array - A reference to a cell or range of cells.
How to use
The ROWS function returns the count of rows in a given reference as a number. For example, =ROWS(A1:A5) returns 5, since the range A1:A5 contains 5 rows. ROWS takes just one argument, called array, which can be a range or array.
Examples
Use the ROWS function to get the row count for a given reference or range. For example, there are 10 rows in the range A1:F10 so the formula below returns 10:
=ROWS(A1:F10) // returns 10
The range A1:Z100 contains 100 rows, so the formula below returns 100:
=ROWS(A1:Z100) // returns 100
You can also use the ROWS function to get a row count for an array constant:
=ROWS({1;2;3;4;5}) // returns 5
Although there is no built-in function to count the number of cells in a range, you can use the ROWS function together with the COLUMNS function like this:
=COLUMNS(range)*ROWS(range) // total cells
=COLUMNS(A1:Z100)*ROWS(A1:Z100) // returns 2600
This article explains this formula in more detail.
Notes
- Array can be a range or a reference to a single contiguous group of cells.
- Array can be an array constant or an array created by another formula.
- To count columns, see the COLUMNS function.
- To get row numbers, see the ROW function.
- To lookup a row number, see the MATCH function.
ROWS function examples
Combine ranges
In this example, the goal is to combine ranges. With the introduction of the VSTACK function and the HSTACK function, this is quite a simple task. To combine ranges vertically, stacking one range on top of another, you can use the VSTACK function like this: =VSTACK(range1,range2) To combine ranges...
Display sorted values with helper column
This formula relies on a helper column that already contains a sequential list of numbers to represent an established sort order. The numbers in the helper column are independent of the operation of this formula. As long as the sequence is continuous, it can represent an ascending or descending...
First column number in range
When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range. In the example shown the array looks like this...
Randomly assign data to groups
In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY...
Total rows in range
The ROWS function is fully automatic. When you provide a range to ROWS, it will return a count of all rows in the range. In the example, the formula in F5 returns 6, because there are 6 rows in the range B5:C10: =ROWS(B5:C10) // count rows ROWS counts the number of rows in any supplied range and...
Random sort
In this example, the goal is to perform a random sort of the data in B5:B16 with a formula. This can be done with the SORTBY function and the RANDARRAY function . SORTBY function The SORTBY function sorts provided values by one or more "sort by" arrays. The sort by arrays make it possible to sort...
Multiple matches into separate rows
In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong to a...
Dynamic date list
Dates in Excel are just serial numbers , formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past. In the example shown, the date in the named range "start" is provided by the TODAY function: =TODAY() //returns current date The...
Average last N values in a table
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we're doing: =AVERAGE(first:last) where "first" is a reference to the first cell to include in the average and "last" is a reference to the last cell to include...
Random value from list or table
Note: this formula uses the named range "data" (B5:E104) for readability and convenience. If you don't want to use a named range, substitute $B$5:$E$104 instead. To pull a random value out of a list or table, we'll need a random row number. For that, we'll use the RANDBETWEEN function, which...
Get address of named range or table
The goal is to return the full address of a range or named range as text. The purpose of this formula is informational, not functional. It provides a way to inspect the current range for a named range, an Excel Table, or even the result of a formula. The core of the solutions explained below is the...
Last row number in range
When given a single cell reference, the ROW function returns the row number for that reference. However, when given a range with multiple rows, the ROW function will return an array that contains all row numbers for the range: {5;6;7;8;9;10} To get the first row number in a range, we use the MIN...
Address of last cell in range
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14). To get the last row used, we use the ROW function together with the MAX function like this: MAX(ROW(data)) Because...
Get relative row numbers in range
The first ROW function generates an array of 7 numbers like this: {5;6;7;8;9;10;11} The second ROW function generates an array with just one item like this: {5} which is then subtracted from the first array to yield: {0;1;2;3;4;5;6} Finally, 1 is added to get: {1;2;3;4;5;6;7} Generic version with...
Last n rows
The core of this formula is based on another formula that calculates the "current row" in a range: =ROW()-ROW(rng.firstcell)+1 In brief, we get the current row in the workbook, then subtract the first row number of the range plus 1. The result is an adjusted row number that begins at 1. The INDEX...
ROWS function videos
How to create a named range
Named ranges are one of the most useful features in Excel. They make your formulas much easier to read and understand; they automatically give you absolute references , and they reduce errors. Let's take a look at a few ways to create named ranges. The simplest way to create a named range is to use...
How Excel Table ranges work
In this video, we'll take a closer look at how table ranges work. One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest...
How to create a dynamic named range with a Table
In this video we'll look at how to create a dynamic named range with a Table. This is the simplest way to create a dynamic named range in Excel. This table contains data for ten properties. I can easily create a named range for this data. For example, I can create a range called "data." Then, using...
How to perform a random sort
In this video, we’ll look at how to perform a random sort with the SORTBY function , with help from the RANDARRAY function . In this worksheet, we have the first 10 letters in the alphabet in the range B5:B14. How can we sort this data in random order? One way to do this is to add a helper column...
Introduction to structured references
In this video, I'll give a brief introduction to structured references. A structured reference is a term for using a table name in a formula instead of a normal cell reference. Structured references are optional, and can be used with formulas both inside or outside an Excel table. Let's take a look...
How to count items in a filtered list
When you're working with filtered lists, you might want to know how many items are in the list and how many items are currently visible. In this video, we'll show you how to add a message at the top of a filtered list that displays this information. Here we have a list of properties. If we enable "...
Formulas to query a table
In this video, we'll look at some formulas you can use to query a table. Because tables support structured references, you can learn a lot about a table with basic formulas. On this sheet, Table1 contains employee data. Let's run through some examples. To start off, you can use the ROWS function to...
How to remove an Excel Table
In this video, we'll look at how to remove a table from an Excel worksheet. In this workbook, we have a number of Excel Tables . Let's look at some ways you can remove these tables. You won't find a "delete table" command in Excel. To completely remove an Excel table, and all associated data, you'...
What is a dynamic named range
In this video we'll introduce the idea of a dynamic range and show you why you might want to use one. Let's take a look. In this first worksheet, we have a list of ten properties set up in a normal way. If we check the formulas that summarize this data to the right, you can see that each formula...
Related functions
ROW Function
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
COLUMN Function
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
COLUMNS Function
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.
