It’s 4:47 PM on Friday. Your manager just asked for a consolidated report by 5. You have 12 spreadsheets open and no idea how many rows of sales data are actually in Sheet2 — because someone pasted over blank rows, added filters, and hid three rows near the bottom. You click and drag. You lose count at 842.
Quick Answer
Use =ROWS(A:A) to count all rows in column A (1,048,576), or =COUNTA(A:A) to count non-blank cells — but only if your data is clean. For dynamic, filtered, or table-based row counts, use =SUBTOTAL(103,A2:A1000) or convert to an Excel Table and reference its structured name like Table1[#Rows].
All the Methods
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
=ROWS(A:A) | < 0.1 sec | Counts all rows in column (1,048,576) — useless for actual data | Easy |
=COUNTA(A:A) | 0.2 sec | Fails if blanks exist mid-dataset (e.g., empty notes column) | Easy |
=SUBTOTAL(103,A2:A10000) | 0.3 sec | ✓ Ignores hidden/filtered rows. ✗ Requires manual range expansion | Medium |
Table1[#Rows] | < 0.1 sec | ✓ Auto-expands, works with filters, ignores totals row | Medium |
=AGGREGATE(3,5,A2:A10000) | 0.4 sec | ✓ Same as SUBTOTAL + ignores errors. ✗ Harder to remember | Hard |
=COUNTIFS(A:A,"<>",B:B,"<>") | 0.9 sec | ✓ Counts rows where both A and B are non-blank. ✗ Slows down fast | Hard |
VBA ActiveSheet.UsedRange.Rows.Count | 0.1 sec | ✗ Counts used range — breaks if stray formatting or formula exists in row 99999 | Hard |
Method 1 Deep Dive
Start with =SUBTOTAL(103,A2:A10000). Why 103? That’s the code for COUNTA that ignores hidden rows. Type it in cell D1. Now filter column A to show only "Acme Corp" — watch D1 update from 982 to 317 instantly.
Sample data in A2:A12:
| A2 | B2 | C2 |
|---|---|---|
| Sarah Chen | Acme Corp | 2024-03-15 |
| James Wu | Beta Labs | 2024-03-16 |
| Maria Lopez | Acme Corp | 2024-03-17 |
| Raj Patel | Delta Inc | 2024-03-18 |
| Alex Kim | Acme Corp | 2024-03-19 |
| Tina Reed | Beta Labs | 2024-03-20 |
| David Tran | Acme Corp | 2024-03-21 |
| Lisa Park | Gamma LLC | 2024-03-22 |
| Kenji Sato | Acme Corp | 2024-03-23 |
| Maya Jones | Delta Inc | 2024-03-24 |
With this data, =SUBTOTAL(103,A2:A11) returns 10. Filter A:A for "Acme Corp" → result becomes 6. No manual recalc needed. Pro tip: Press Alt+↓, then →, then Enter to auto-expand the range to last populated cell before typing the formula.
Method 2 Deep Dive
Convert your data to an Excel Table (Ctrl+T). Name it Orders using the Design tab > Properties > Table Name. Now type =Orders[#Rows] in any cell. It returns 10 — and if you paste 5 more rows below, it instantly updates to 15.
This works even if you add a Total Row (Design tab > Total Row). Orders[#Rows] excludes the Total Row. Orders[#Data] gives you just the data rows — excluding headers and totals.
Try this: In cell E1, enter =Orders[@[Customer]]&" sold "&TEXT(Orders[@[Amount]],"$#,##0"). Then copy down. The structured reference auto-adjusts — and Orders[#Rows] stays accurate even when you sort, filter, or insert columns.
Surprising tip: If your table has merged cells in the header, Excel won’t let you use [#Rows]. Unmerge them — even if they look fine. Merged cells break structured references silently.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Count visible rows only (filtered) | =SUBTOTAL(103,A2:A10000) | Replace A2:A10000 with your actual range. Use 102 for numeric-only count. |
| Auto-updating count for a table | =Orders[#Rows] | Requires table name. Works with filters, sorts, inserts. |
| Select full data range fast | Ctrl+A (once), then Ctrl+Shift+↓ | First Ctrl+A selects current region. Second combo extends to last row. |
| Count rows where two columns are non-blank | =COUNTIFS(A2:A1000,"<>",B2:B1000,"<>") | Slower above 5K rows — avoid for large datasets. |
| Refresh all SUBTOTAL formulas | Ctrl+Alt+F9 | Forced full recalc — needed after hiding rows manually (not via filter). |
| Check if your table has merged headers | Select header row → Home tab → Merge & Center dropdown | If it says "Unmerge Cells", your table is broken. Fix before using [#Rows]. |
