To count records in Excel, use =COUNTA(A2:A20) when each record has a filled-in value in column A; it counts nonempty cells in that range. To count every row position, including blanks, use =ROWS(A2:A20). The right method depends on whether you want nonblank records, all rows in a selected range, or only rows visible after filtering.
What You’ll Need
- An Excel workbook open in Microsoft Excel for Microsoft 365 on Windows. The methods below also work in recent desktop versions; Excel for the web has the same core formulas.
- A clear idea of what counts as a record. For a list of orders, for example, use a column that should contain an order number on every valid row.
- Optionally, a filtered list or an Excel table if you want to count only visible records or have a count that adjusts as the table changes.
You do not need an add-in or a special setting. If you are only checking a small selection, Excel’s status bar can show a count without adding a formula.
Step-by-Step
- Decide which rows you mean. A worksheet has a large grid of row positions, but a dataset usually occupies only part of it. Decide whether you need the count of populated records, the number of positions in a range even if some are blank, or the number currently shown after filtering. These are different totals. If every record has an ID in column A, column A is usually the simplest column to count.
- Identify the data range and the reliable column. Look for the first row of data and the last row you intend to include. If row 1 contains headings and records run from row 2 to row 20, the record range is A2:A20 when IDs are in column A. Do not include the heading in the formula unless you want it counted as a nonempty cell too. A column that is sometimes blank is not a reliable basis for counting all records.
- Count nonblank records with COUNTA. With IDs in A2:A20 and 17 filled ID cells in that range, enter
=COUNTA(A2:A20)in an empty cell and press Enter. The result is 17. COUNTA counts cells that contain text, numbers, dates, or formulas; it does not count genuinely empty cells. Use this when one filled field reliably represents one record. A formula that displays an empty string can still be counted because the cell contains a formula, so check that detail if the result seems unexpectedly high. - Count every row position in a range with ROWS. If you need to know how many row positions the range covers, enter
=ROWS(A2:A20). This returns 19 because rows 2 through 20 inclusive contain 19 positions. ROWS counts positions whether their cells are filled or empty. It does not inspect the data, so it is not a count of completed records if the range has blank rows. - Use a table reference for a growing list. If your records are formatted as an Excel table, click inside it and check the Table Design tab for its table name, often something like Table1. With a table named Table1 and an Order ID column, enter
=ROWS(Table1[Order ID])in a cell outside the table. If the table currently has 19 data rows, the result is 19. The reference expands when you add table rows, which saves you from editing a fixed range. Replace the example table and column names with the actual names shown in your workbook. The formula counts table data rows, including rows where that particular field might be blank. - Count visible records after filtering. If IDs are in A2:A20 and a filter leaves eight visible, filled IDs, enter
=SUBTOTAL(103,A2:A20)outside the filtered list. The result is 8. The 103 option counts nonempty visible cells and excludes rows hidden by a filter; it also excludes rows hidden manually. Make sure the chosen column has an entry for every record you want included. If you use a column with missing values, the result will be lower than the number of visible records. - Check a quick count with the status bar. Select only the data cells in a reliable column, such as A2:A20, and look at the bottom status bar. If Count is not displayed, right-click the status bar and turn on Count. Excel shows how many selected cells are nonempty. This is handy for a one-time check, but it does not create a result in a worksheet cell and does not automatically update a separate report. On a Mac, Control-click the status bar to open its options if a regular right-click is unavailable; Excel for the web may show fewer status-bar options.
- Verify the result before using it. Compare the count with the range boundaries, inspect any blank rows, and confirm whether hidden or filtered records should be included. For example, if your range has 19 positions but COUNTA returns 17, there are two cells without counted values—or cells that appear blank but are not truly empty may affect your expectations. A quick visual check of the key column helps catch an incorrectly selected range.
For most lists, the most useful result is the number of records with a value in a field that every record must have. For a list with a unique ID in each row, COUNTA is generally the clearest choice. If rows may have missing IDs, fill or repair that key field before relying on it, or use a different field that is consistently populated.
Common Mistakes
- Counting a heading as a record. If a formula includes A1 and A1 contains a heading, COUNTA includes it. Start the range at the first data row, or subtract one only when the range always includes exactly one heading.
- Using ROWS to count completed records. ROWS returns the number of row positions, not the number of filled records. A range with blank lines still has the same ROWS result. Use COUNTA on a dependable field when blanks should not count.
- Counting the wrong column. COUNTA only reports nonempty cells in the range you give it. If some orders do not have a value in the column chosen, those records will be missed. Choose a field required for every record or fix incomplete entries first.
- Assuming a cell that looks blank is empty. A formula that returns an empty-looking result, or a cell containing spaces, may be treated as nonempty. COUNTA can include those cells. Inspect the cell or formula bar, and remove unintended spaces or formulas if appropriate.
- Including a total row or notes. A range that extends below the dataset may include a note, subtotal, or another value. Keep the count range limited to actual records, or use a table reference that covers the data body.
- Expecting a normal count to respond to filters. COUNTA counts matching nonempty cells in its range whether rows are filtered out or not. Use SUBTOTAL with 103 when the requested number is only the visible nonempty entries.
Tips
- Use a unique ID column when available. It makes the meaning of the count easy to explain and avoids counting optional fields such as comments or delivery dates.
- Turn a changing list into a table with Insert > Table, or use Home > Format as Table. Tables expand as records are added and make formula references easier to read.
- Put the formula outside the range it counts. Placing a count formula inside its own counted range can create a circular reference or make the total include its own cell.
- Label the result clearly, such as “Nonblank order IDs” or “Visible records.” This prevents someone reading the workbook from mistaking a filtered count for the total number of records.
- Keep counts for different purposes separate. A total of all data rows, a count of filled IDs, and a count of visible filtered rows can all be useful, but they are not interchangeable.
- If you need the number of worksheet rows rather than records in your list, a modern Excel worksheet has 1,048,576 rows. That grid size is not the number of rows containing your data.
Troubleshooting
The result is zero or lower than expected. Check that the range points to the correct column and rows. Look for blank key cells, a heading accidentally excluded or included, and entries stored in a different part of the sheet. If IDs are formulas that display nothing, remember that COUNTA may still count the formula cells.
The result is higher than the visible number of records. The range may include extra cells, a heading, notes, or formulas that return empty-looking values. Select a smaller range that covers only the data, or use a table column reference. To find space-only entries, inspect suspicious cells and remove accidental spaces rather than assuming they are empty.
The filtered count does not change as expected. Confirm that the formula uses =SUBTOTAL(103,A2:A20), that the range matches the filtered data, and that the counted column is populated on every row. SUBTOTAL only counts nonempty cells in the specified range. It will not infer a record from other columns when the chosen cell is blank.
The formula appears as text instead of a result. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, edit the formula, and press Enter. Also confirm that the formula begins with an equals sign.
A table formula returns an error. Check the table name and column heading spelling, including spaces. Click in the table and use Table Design to confirm its name; use the actual heading inside the structured reference. Ensure the formula is in a cell outside the table if you want a separate summary.
The status bar does not show Count. Select a range containing cells rather than only a single row label, then right-click the status bar and enable Count. If the option is unavailable or the interface differs, use COUNTA in a worksheet cell instead; it provides a saved, auditable result.
Frequently Asked Questions
How do I count all rows in a selected range, including blanks?
Use ROWS with the range, such as =ROWS(A2:A20). It returns 19 for that range, whether the cells contain data or not.
How do I count only rows that contain data?
Use COUNTA on a column that should have a value in every record, such as =COUNTA(A2:A20). It counts nonempty cells in that column, not entire rows with any content anywhere.
Does COUNTA include cells with formulas that look blank?
Yes. A cell containing a formula is not truly empty, even if the formula displays an empty string. That can make COUNTA higher than the number of visibly filled-in values.
How can I count rows after applying a filter?
Use SUBTOTAL with function number 103 on a consistently filled column. It counts visible nonempty cells and ignores filtered-out and manually hidden rows.
Can Excel count rows automatically when I add new data?
Yes. Format the data as a table and count a table column with a formula such as =ROWS(Table1[Order ID]). The structured reference adjusts as table rows are added or removed.
What is the shortcut for seeing a quick count?
There is no need to use a keyboard shortcut: select the data cells and read Count on the status bar. If Count is missing, enable it from the status bar’s right-click menu. This count is a quick check, not a formula saved in the sheet.
Related
- How to Color Negative Values in Excel Automatically
- How to Color Code Names in Excel with Conditional Formatting
- How to Color Every Other Row in Excel Automatically
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Range Between Two Dates: Formulas and ExamplesTo calculate the number of days between two dates in Excel, subtract the earlier date from the later…
- Calendar Day Formula in Excel: Dates, Weekends & MoreFor the day number within a date, use =DAY(A2), where A2 contains an Excel date; for example, January…
- Excel Filter Duplicates: Keep the First RecordTo keep the first occurrence of each value in Excel, select the full data range and choose Data…
- Excel Sum Between Dates: SUMIFS Formulas and ExamplesIf dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in E2…
- Find Unique Names in Excel: 3 Reliable MethodsTo find a list of distinct names in Excel, enter =UNIQUE(A2:A100) in an empty cell if you use…
- Formula to Capitalize Each Word in ExcelIn Excel, use the PROPER function to capitalize the first letter of each word: with the text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.