EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

ROWS Function in Excel

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Lookup & Reference Functions in Excel » ROWS Function in Excel

ROWS Function in Excel

Excel ROWS Function (Table of Contents)

  • Introduction to ROWS Function in Excel
  • How to Use ROWS Function in Excel?

Introduction to ROWS Function in Excel

Excel is nothing more than a table with rows and columns. Every time we work on Excel, we have to deal with cells. A cell is nothing but intersection rows and columns. While working on cells, we always will come up with a situation where we need to figure out how many ranges of rows we are working on. There comes the excel ROWS function to your help, allowing you to calculate the number of rows present in an array.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Excel ROWS function is categorized under Lookup & Reference functions section under Formulas in Excel. This means this function can be used to either look up the values/numbers or find out the references. And, that is what the function does itself; it lookups what are the rows in an array/reference provided and gives the number of those rows as an output. You can see the definition of the function when you navigate your mouse towards the same. Below is the screenshot that makes things look clearer.

ROWS Function in Excel 1-1

Syntax of ROWS Function

ROWS Function Syntax

The argument of ROWS Function:

array – This is a required argument that specifies the range of cells used as an argument and function that captures the number of rows for the same.

How to Use ROWS Function in Excel?

Let’s understand how to use the ROWS Function in Excel with some examples.

You can download this ROWS Function Excel Template here – ROWS Function Excel Template

Example #1 – Calculate Number of Rows

In order to check how the Excel ROWS function work, follow the below steps:

Step 1: In an active Excel sheet, start typing the syntax as =ROW and select the ROWS function by double-clicking from the list of functions that will appear as soon as you start typing the function syntax.

ROWS Function in Excel 1-2

Step 2: As soon as you double click the ROWS function from the list, it will appear inside the cell you are referring to and will be asking for an argument, i.e. an array for cells reference through which the function can count the number of rows.

Popular Course in this category
Sale
Excel Training (21 Courses, 9+ Projects)21 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (11,462 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (16 Courses, 23+ Projects)

ROWS Function in Excel 1-3

Step 3: Now, we need to provide an array of cells as an argument to this function. Put B1:C10 as an array argument to ROWS Function.

ROWS Function in Excel 1-4

Now, we will figure out what could be the output for this function with B1:C10 as an argument. The array is up to the 10th row of excel irrespective of a column, if you can see. Also, the ROWS function doesn’t figure out column numbers. Therefore, the ideal output should be 10 under cell A1.

Step 4: Complete the formula by adding closing parentheses after an array input argument and press Enter key to see the output.

ROWS Function in Excel 1-5

Example #2 – Calculate Number of Rows for an Array Constant

ROWS function also works on array constants. Array constants are something that are numbers under curly brackets. Ex. {1;2;3}

Step 1: In cell A2 of the active Excel sheet, start typing =ROW and select the ROWS function from the list that appears.

ROWS Function in Excel 2-1

Step 2: Once the function is selected, use {1;3;5;7;9} as an argument for this function. Make sure you separate the array constants with a semicolon instead of a comma. Doing so converts them into individual array constants.

ROWS Function in Excel 2-2

Step 3: Complete the ROWS function with closing parentheses and Press Enter key to see the output.

ROWS Function in Excel 2-3

This code considers each constant as a row number and gives the count of all such present in an array. For example, 1 stand for 1st row, 3 stands for 3rd, and so on. The interesting thing to note down is, this example does not include the rows in-between. Like 2nd, 4th, 6th, and 8th rows are not considered as an argument. This is because we used a semicolon as a separator which defines each element of an array as a sole entity.

Example #3 – Count the Table Rows

Suppose we have a table, and we want to count how many rows the table has acquired. We can do this with the help of the ROWS function as well.

First, we will convert this data into a table. Suppose we have sales data for the past four years as given below.

Count the Table Rows 3-1

Click on the Insert tab in excel and click on the Table button to insert a table on given data.

Count the Table Rows 3-2

Step 1: In cell F1, start typing the formula =ROWS and select the ROWS function by double-clicking on it from the list of suggested functions.

ROWS TABLE 1

Step 2: Now, it will ask for an array argument within which it can count the number of rows. For that, the type table inside the ROWS function will show the active table present in the system. Double click on it to select.

Count the Table Rows 3-4

It will look like the one in the below screenshot. Now we want to check how many rows this table has acquired.

rows function

Step 3: Now, it will ask for an array argument within which it can count the number of rows. For that, the type table inside the ROWS function will show the active table present in the system. Double click on it to select.

Select Table

Step 4: Now close the parentheses so that the formula will be complete and press the Enter key to see the output.

ROWS Table 2

This will give an output of 8 which means there are 8 rows in the table. An interesting thing in this example is that whenever we are trying to capture the rows acquired by a table, the header rows are always neglected from the calculations. See the table; there are 9 rows, including column headers. However, as it is a table, column header rows are neglected.

This is from this article. Let’s wrap things up with some points to be remembered.

Things to Remember 

  • This function can take an array or any single cell as a reference and returns the number of rows present in that array. Obviously, if it is a single cell as an argument, it will always return 1 as a row count.
  • We can also use array constants or array formulas as an argument to this function, and it will return the count of rows under array reference.
  • If a table is used as an argument to this function, it will return the row count for that table, excluding the column header row.

Recommended Articles

This is a guide to ROWS Function in Excel. Here we discuss How to use ROWS Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Add Rows in Excel Shortcut
  2. How to Sum Multiple Rows in Excel
  3. Excel Columns to Rows
  4. Excel Rows to Columns

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download ROWS Function Excel Template

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड ROWS Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Online EXCEL COURSE Learn More