To count the rows in an Excel range, use =ROWS(A2:A100); it returns 99 because that range contains 99 row positions, whether or not they contain data. To count populated records instead, count a column that should be filled in every record, such as with =COUNTA(A2:A100). The right formula depends on whether you mean every row, nonblank entries, numeric values, or only rows showing after a filter.
What “count rows” means in Excel
People use “row count” to describe several different things. A worksheet row has a numbered position, while a data row is usually one record in a list or table. A row may exist in a selected range but have no content; it may contain text rather than numbers; or it may be hidden by a filter. Those distinctions matter because Excel has different tools for counting each situation.
For example, a sales list might have a header in row 1 and records in rows 2 through 100. There are 99 possible record positions in that range. If only 85 orders have been entered, the range still has 99 rows, but the list has 85 populated records if every order has an order ID. If a filter shows 12 orders, there are only 12 visible records at that moment.
Excel worksheets have 1,048,576 rows, but that maximum is rarely the useful answer. In everyday work, the question is usually how many rows a selected range contains or how many records have been entered. Decide which one you need before choosing a formula.
Count every row in a range with ROWS
ROWS counts the number of row positions in a reference. It does not inspect whether cells are empty, and it does not count individual cells. With a range such as A2:A100, it returns the number of rows from the first endpoint through the last, inclusive.
With the range A2:A100 as the layout, enter =ROWS(A2:A100). The result is 99. This is useful for checking the size of a fixed block, calculating how many slots a template provides, or confirming the dimensions of a range. The same result applies if every cell in the range is blank.
The formula can also count rows in a multi-column rectangle: the number of columns does not change the result. With a range from A2 through D100 as the layout, =ROWS(A2:D100) returns 99, not 396. Use COLUMNS if you want the number of columns instead. For instance, A2:D100 has 99 rows and four columns.
To count a range you have selected, click the Name Box to the left of the formula bar, type the reference, and press Enter; you can then use that reference in the formula. For a one-time check, selecting cells may show a Count value on Excel’s status bar, but that depends on the selected content and status-bar settings. A formula is more dependable when the result must update or be used elsewhere.
Count nonblank rows or records with COUNTA
When each record has a field that must be filled in, count that field rather than counting the range’s row positions. Common choices are an ID, date, or name column. COUNTA counts cells that are not empty, including cells containing text, numbers, dates, or formulas. It is not a count of rows that contain numeric data only.
Assume each order has an ID in A2:A100, with the header outside the range. Enter =COUNTA(A2:A100) to count the entered IDs. If 85 cells contain IDs and the rest are empty, the result is 85. This is a practical record count as long as the chosen column is reliably filled for every real record and left empty for unused rows.
Choosing the counting column is important. If you count a notes column that is optional, the result will omit valid records without notes. If the selected range includes a header, the header itself is counted as a nonblank cell, so start below it or subtract one deliberately. Also be aware that a formula in a cell counts as nonblank even if its displayed result looks empty; this can make COUNTA larger than the number of visibly filled records.
For a formatted Excel table, use a column with a value in every record. If the table is named Table1 and every order has an Order ID, enter =COUNTA(Table1[Order ID]) to count nonblank IDs in that table column. Structured references expand as table records are added, so you do not have to edit a fixed endpoint such as A100. If some IDs may be missing, this formula counts filled IDs, not every table row.
Count numeric cells with COUNT
Use COUNT when the question is specifically how many cells contain numbers. Dates and times are stored as numbers in Excel, so they are included. Text, blanks, and numbers stored as text are not counted. This makes COUNT useful for numeric measurements or scores, but it may not equal the number of records in a mixed data list.
With numeric test scores in B2:B100, enter =COUNT(B2:B100). If 83 cells contain actual numeric values, the result is 83, even if the other rows contain text, blanks, or numbers stored as text. If every record has a student name but some scores are missing, COUNT answers how many scores have been entered, while COUNTA on the name column answers how many students are listed.
A common source of surprise is imported data. A cell that looks like 42 may be stored as text, perhaps because of the way a file was imported or because it has a leading apostrophe. COUNT ignores that entry. If the expected count is too low, check the cell’s value and data type rather than assuming the formula is wrong.
Count visible rows after filtering
Ordinary formulas such as COUNTA include filtered-out records. To count nonblank cells that remain visible after filtering, use SUBTOTAL with function number 103, which performs a COUNTA-style count while excluding rows hidden by a filter and rows hidden manually. The count changes as the filter changes.
Assume every visible order has an ID in A2:A100 and an AutoFilter is applied to the list. Enter =SUBTOTAL(103,A2:A100). If the filter leaves 12 orders visible and each visible row has an ID, the result is 12. If some visible records have a blank ID, those rows are not counted, so the answer is a count of visible nonblank IDs rather than a guaranteed count of visible row positions.
There is a related distinction between SUBTOTAL function numbers 3 and 103. Both perform a COUNTA-style count and ignore rows removed by a filter. The 3 version includes rows hidden manually; the 103 version excludes them as well. If manual hiding should not affect the answer, use 3. If the desired count should reflect only what is currently visible, including manually hidden rows being omitted, use 103.
These formulas count nonblank cells in the chosen column, not every visible row regardless of content. For a reliable visible-record count, choose a key field that is always filled. If a list has no such field, add a helper column with a value for each actual record before relying on a visible-cell count.
Count rows in an Excel table
Tables are often easier to maintain than fixed ranges because they grow when data is added. To count table records, count a required field in the table, or count the table’s data rows with ROWS. A structured reference to a table column excludes the header and any total row, making it convenient for ordinary record lists.
For a table named Table1 with data rows in its Order ID column, enter =ROWS(Table1[Order ID]) to count the number of data-row positions in that column. If the table contains 85 order rows, the result is 85 even if one Order ID cell is blank. By contrast, =COUNTA(Table1[Order ID]) counts only the nonblank IDs. The first formula answers how many table rows there are; the second answers how many IDs are filled in.
To check or change a table’s name, click a cell in the table and use the Table Design tab on the ribbon; the Table Name box is at the left. Table names and column names in formulas must match the workbook. If the formula reports a name error, check the table and column labels for spelling rather than replacing the structured reference with an arbitrary range.
Choose the right formula and avoid common mistakes
Match the formula to the intended meaning, not simply to the word “count.” These examples summarize the most useful choices:
ROWS(range)counts row positions, including empty ones.COUNTA(range)counts nonblank cells, including text and formulas.COUNT(range)counts numeric cells, including dates and times.SUBTOTAL(103,range)counts visible nonblank cells and excludes manually hidden rows.
Check the range boundaries before interpreting the result. Including a header adds a count for that header with COUNTA, COUNT, or SUBTOTAL when the header’s cell meets the function’s counting rule. Starting one row too early or ending too late can also include a title, a note, or an unused row. For recurring work, convert the list to a table or use a clearly defined range so that the formula is easier to maintain.
Another misconception is that a formula’s visible output determines whether a cell is empty. A formula that returns an empty string can look blank, yet COUNTA can count the cell because it contains a formula. If counts appear higher than the visible entries, inspect formulas in the supposedly empty part of the range. Conversely, spaces typed into a cell are content, so they can be counted as nonblank even though the cell looks empty at a glance.
For a quick manual selection, click a cell in the data and press Ctrl+A to select the current region in many worksheet layouts; check the selection before relying on it, because blank rows or columns can divide regions. On Mac, use Command+A. A selected range’s status-bar summary can be useful for a one-off check, but formulas are preferable for a value that must update, be documented, or feed another calculation.
Frequently Asked Questions
What is the formula to count rows in Excel?
Use =ROWS(A2:A100) for the number of row positions in that range. It returns 99 whether the cells are full or empty. Use COUNTA on a required field instead if you mean populated records.
How do I count rows that are not blank?
Use =COUNTA(A2:A100) when column A is filled for each record. It counts nonblank cells, not entire rows across multiple columns, so select a field that reliably represents each record.
How can I count only filtered rows?
Use =SUBTOTAL(103,A2:A100) on a column with a value in every record. It counts visible nonblank cells and excludes filtered-out and manually hidden rows. Use 3 instead of 103 if manually hidden rows should still be included.
Does COUNTA count cells with formulas that look blank?
Yes. COUNTA counts a cell containing a formula even when the formula displays an empty-looking result. That behavior can make the count higher than the number of visibly populated entries.
How do I count rows in an Excel table?
Use =ROWS(Table1[Order ID]) to count the table’s data-row positions in that column, or =COUNTA(Table1[Order ID]) to count the nonblank IDs. Replace the example table and column names with the ones in your workbook.
Related
- Excel Sum Duplicates: How to Total Repeated Values
- Excel: Sum a Filtered Column with SUBTOTAL
- Excel Sum Above: How to Add Cells Above Your Total
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Set an Out of Office Reply in Outlook 365To set an out of office reply in Outlook 365, open Automatic Replies, switch them on, write your…
- Can You Insert a Page Break in Excel? Yes, Here’s HowYes. In Excel for Microsoft 365 on Windows, select the row below or the column to the right…
- How to Create a Digital Signature in OutlookTo create the signature most people mean—a name, contact details, or a sign-off that appears at the bottom…
- Excel Shortcut Cheat Sheet for Windows and MacAn Excel shortcut cheat sheet is a quick reference to keyboard commands for editing cells, moving around worksheets,…
- Can Excel Goal Seek Multiple Cells at Once?No. In Microsoft Excel for Microsoft 365 on Windows, Goal Seek can adjust only one changing cell to…
- Compare Documents in Word 365: A Practical GuideTo compare two files in Word 365 on Windows, open Word and choose Review > Compare > Compare,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.