What Most People Miss About How Many Rows in Excel Sheet

Excel supports exactly 1,048,576 rows per worksheet. But if you’ve ever scrolled all the way down and seen row 1,048,576 blank — or worse, found your data mysteriously cut off at row 65,536 — you’re not miscounting. You’re hitting a version wall, a formatting ghost, or a hidden filter.

The Problem

You paste 900,000 sales records from your ERP into Sheet1 — names, dates, amounts — and everything looks fine until you sort column C. Suddenly, rows 782,451 through 1,048,576 vanish. No error. No warning. Just silence where data should be. You check Ctrl+End, and Excel jumps to row 127,891 instead of the bottom. You scroll manually… and stop at row 131,072. You’re baffled. Your colleague swears Excel ‘only does 65K rows’. Another says ‘it’s unlimited now’. Neither is fully right.

This isn’t about memory or file size. It’s about how Excel interprets ‘used range’ — and how easily it gets fooled.

Row Number	Sales Rep	Region	Amount	Date
1	Sarah Chen	APAC	$45,200	2024-03-15
2	Diego Mora	LATAM	$32,890	2024-03-16
3	Amina Diallo	EMEA	$51,030	2024-03-16
4	Rajiv Patel	APAC	$28,410	2024-03-17
5	Lena Kim	NA	$67,120	2024-03-17
127,891	(blank)	—	—	—
1,048,576	(blank)	—	—	—

That gap between row 5 and row 127,891? That’s where Excel *thinks* your data ends — because somewhere in columns A through Z, there’s a stray space in cell D127891, or a hidden formula in G127890 that returned "" (empty string), or a formatting brush accidentally applied to row 127,892. Excel treats those as ‘used’, even though they’re invisible.

The Solution

We fix this in three steps — no macros, no add-ins. Just native Excel logic and one critical shortcut.

Select the entire worksheet: Click the triangle at the top-left corner (above row 1, left of column A) — or press Ctrl+A twice. The first Ctrl+A selects the current used range. The second selects everything.
Clear formats & contents safely: With all cells selected, go to Home → Clear → Clear All. Do not use Delete. Why? Delete only removes values — formats, hidden rows/columns, and conditional formatting stay behind and poison the used range. Clear All resets everything cleanly. (Trust me, I learned this the hard way after losing 3 hours to a phantom merged cell in row 999,999.)
Re-enter your data — or better yet, paste into A1 of a fresh sheet. Then verify the true last row with Ctrl+End. It should land on your actual final row — say, B924,118 if you pasted 924,118 rows of real data.

Here’s what your clean sheet looks like after Step 2:

Row Number	Sales Rep	Region	Amount	Date
1	Sarah Chen	APAC	$45,200	2024-03-15
2	Diego Mora	LATAM	$32,890	2024-03-16
3	Amina Diallo	EMEA	$51,030	2024-03-16
4	Rajiv Patel	APAC	$28,410	2024-03-17
5	Lena Kim	NA	$67,120	2024-03-17
924,118	Yuki Tanaka	APAC	$38,920	2024-03-28

Now Ctrl+End lands precisely on row 924,118 — not row 127,891. And if you type =ROWS(A:A) in any empty cell, it returns 1048576. That’s your answer: how many rows in Excel sheet? Always 1,048,576. But how many are *yours*? That depends entirely on what Excel thinks is ‘used’.

Going Further

So you know the ceiling is 1,048,576. What else do you need to know?

How many rows in one Excel sheet? Still 1,048,576 — no exceptions. Not per workbook, not per version, not per license. Every .xlsx or .xlsb worksheet since Excel 2007 has exactly that number. Excel 2003 and earlier had 65,536 rows — that’s why your colleague swore by ‘65K’. If you open an old .xls file in modern Excel, it still respects that limit *for that file format*, but save it as .xlsx and the full million-plus unlocks.

Want to see the exact last row without scrolling? Type =CELL("row",INDEX(A:A,1048576)) — it’ll return 1048576 every time. Or just select column A, then look at the status bar: it shows “Count: 1048576”.

Here’s the counterintuitive tip: Blank rows don’t reduce your row count — but blank rows *with formatting* absolutely do. Try this: In a new sheet, go to row 500,000, select column A, and apply bold + yellow fill. Now press Ctrl+End. Excel jumps to row 500,000 — even though A1:A499999 is empty. That formatting ‘used’ the row. Delete the formatting (Home → Clear → Clear Formats), and Ctrl+End snaps back to row 1.

You can also check used range programmatically: Press Alt+F11 to open VBA editor, then in Immediate Window (Ctrl+G), type ?ActiveSheet.UsedRange.Rows.Count and hit Enter. That tells you how many rows Excel currently recognizes as used — which may be far less than 1,048,576.

When NOT to Use This

Don’t run Clear All on a sheet that contains:

Conditional formatting rules tied to dynamic ranges — clearing wipes those rules. Rebuild them after.
Data validation lists pointing to external sheets — those references survive, but the list dropdowns may break until you re-select the source range.
Charts or PivotTables built directly on the sheet — they’ll lose their source and show #REF! errors. Copy chart objects first, or rebuild pivots from scratch.
Shared workbooks in legacy mode — Clear All triggers a ‘shared workbook cannot be edited’ error. Save a local copy first.

Also: Never clear a sheet that’s part of a Power Query dataflow unless you’ve disconnected all queries first. Power Query caches metadata — and wiping the sheet mid-refresh can corrupt the query definition in ways Excel won’t warn you about.

If you’re working with >500,000 rows regularly, consider switching to Power Pivot or Excel Tables with external data connections. Native Excel starts lagging past ~300K rows — not because of row limits, but because calculation engine overhead multiplies exponentially.

Keyboard Shortcuts

Action	Shortcut	Notes
Jump to last used cell	`Ctrl+End`	Respects current used range — often misleading
Select entire worksheet	`Ctrl+A` (twice)	First press = current region; second = all 1,048,576 rows
Clear formats only	`Alt+H+E+F`	Home tab → Clear → Clear Formats
Clear contents only	`Alt+H+E+C`	Home tab → Clear → Clear Contents
Go to specific row	`F5` or `Ctrl+G`, then type `1048576`	Directly navigates to last row