Count Rows in Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

How to Count the Number of Rows in Excel?

Here are the different ways of counting rows in Excel using the formula, rows with data, empty rows, rows with numerical values, rows with text values, and many other things related to counting the number of rows in Excel.

Table of contents

#1 - Excel Count Rows which has only the Data

Firstly, we will see how to count the number of rows in Excel with the data. There could be empty rows between the data, but we often need to ignore them and find exactly how many rows contain the data in it.

  1. We can count the number of rows with data by selecting the range of cells in Excel. For example, take a look at the below data.


    Row Count Example 1

    We have a total of 10 rows (border inserted area). In this 10 row, we want to count exactly how many cells have data. Since this is a small list of rows, we can easily calculate the number of rows. But when it comes to the huge database, it is impossible to count manually. So, this article will help you with this.

  2. Firstly, we must select all the rows in Excel.


    Row Count Example 1-1

  3. It is not telling us how many rows contain the data here. Instead, look at the Excel screen's right-hand side bottom, i.e., a status bar.


    Row Count Example 1-2

    Take a look at the red circled area. It says COUNT as 8, which means that out of 10 selected rows, 8 have data.

  4. Now, we will select one more row in the range and see what the count will be.


    Row Count Example 1-3

  5. We have selected 11 rows, but the count says 9, whereas we have data only in 8 rows. When we closely examine the cells, the 11th row contains a space.


    Row Count Example 1-4
    Even though there is no value in the cell and it has only space Excel will be treated as the cell which contains the data.

#2 - Count all the rows that have the data

We know how to check how many rows contain the data quickly. But that is not the dynamic way of counting rows that have data. Instead, we need to apply the COUNTA function to count how many rows contain the data.

We will apply the COUNTA function in the D3 cell.

Row Count Example 2

So, the total number of rows containing the data is 8 rows. Even this formula treats space as data.

Row Count Example 2-1

#3 - Count the rows that only have the numbers

Here we want to count how many rows contain only numerical values.

Row Count Example 3

We can easily say 2 rows contain numerical values. Let us examine this by using a formula. We have a built-in formula called COUNT which counts only numerical values in the supplied range.

Row Count Example 3-1

We will apply the COUNT function in cell B1 and select the range as A1 to A10.

Row Count Example 3-2

The COUNT function also says 2 as a result. So, out of 10 rows, only rows contain numerical values.

Example 3-3

#4 - Count Rows, which only has the Blanks

We can find only blank rows by using the COUNTBLANK function in Excel.

Example 4

We have 2 blank rows in the selected range, which are revealed by the count blank function.

Row Count Example 4-1

#5 - Count rows that only have text values

Remember, we do not have any straight in the COUNTTEXT function. Unlike in previous cases, we need to think differently here. We can use the COUNTIF function with a wildcard character asterisk (*).

Example 5

Here all the magic is done by the wildcard character asterisk (*). It matches any of the alphabets in the row and returns the result as a text value row. Even if the row contains numerical and text values, it will be treated as text value only.

#6 - Count all of the rows in the range

Now comes the important part. How do we count how many rows we have selected? One uses the Name Box in excel, which is limited while still choosing the rows. But how do we measure then?

Example 6

We have a built-in ROWS formula, which returns how many rows are selected.

Example 6-1

Things to Remember

  • Even space is treated as a value in the cell.
  • If the cell contains numerical and text values, it will be treated as a text value.
  • ROW may return the current row we are in, but ROWS will return how many are in the supplied range even though there is no data in the rows.