Excel File Too Large Shrink It by Resetting the Used Range
A file with a few hundred rows should weigh kilobytes. Yours weighs 30 MB, opens slowly, and takes forever to save. Excel has been tricked into believing your sheet is enormous. Here is how to prove it — and how to shrink the file back to its real size.
✍️ Technical Anshu🪟 Windows 10 / 11⏱️ 7 minute read
🎥 Watch the video — the 30 MB file problem demonstrated, and why formatting is usually behind it
Every Excel sheet has a used range — the rectangle Excel believes contains your content. Formatting an entire column, pasting into row 900,000 once, or copying styled cells far beyond your data all stretch that rectangle. Excel then stores, scans and saves the whole phantom area: that is how a small table becomes a 30 MB file, exactly the situation the video demonstrates. The repair is a known routine — find the true end of your data, delete everything beyond it properly (delete, not just clear), and save. Done right, files routinely shrink by 90% or more.
Ctrl End test — where Excel thinks the data ends
Your real data ends at: D480
Ctrl + End jumps to: XFD1048576
Used range Excel believes: the entire sheet
File size: 31.4 MB
After cleanup: 212 KB
If Ctrl End lands nowhere near your real data, the used range is bloated — and the file size is mostly phantom formatting.
1
Measure the Problem With Ctrl End
One keystroke per sheet tells you whether this fix applies to you. Do it on every sheet, including ones you rarely open.
Click any cell on the sheet and press Ctrl + End
Note where you land. A modest distance past your data (a few rows) is harmless. Landing at row 1048576 or column XFD means the used range is stretched to the maximum
Check the file size in Explorer at the same time, so you can prove the improvement afterwards
Also look for forgotten sheets — old drafts, hidden sheets and copied backups inside the same file add weight too
2
Delete the Phantom Rows and Columns — Really Delete Them
The crucial detail most people get wrong: pressing Delete or choosing Clear Contents removes values but leaves formatting, so the used range stays bloated. The rows and columns themselves must go.
Click the row header of the first empty row below your data, then press Ctrl + Shift + Down to select everything below
Right-click the selection and choose Delete (the menu option that removes the rows, not the Delete key)
Repeat for columns: select from the first empty column header with Ctrl + Shift + Right, right-click, Delete
Repeat on every bloated sheet, then save the file — the used range resets at save time, which is why saving is part of the fix, not an afterthought
3
Save in the Right Format and Clear the Other Weight
With the used range reset, two more habits keep the file lean. Each addresses a different kind of stored weight.
If the file is an old .xls, use Save As → Excel Workbook (.xlsx) — the modern format compresses far better and old-format files bloat notoriously
Check for oversized pictures: a pasted screenshot can weigh megabytes. Select images and use Picture Format → Compress Pictures, choosing email or web resolution for working files
Open Home → Conditional Formatting → Manage Rules and delete rules covering huge or fragmented ranges
Delete truly dead sheets instead of hiding them — hidden is not gone, and Very Hidden sheets from old macros are classic stowaways
4
Verify the Shrink and Protect the Result
Confirm the cure, then stop the cause from returning — the bloat came from a habit, usually formatting whole columns or pasting formatted blocks far beyond the data.
Press Ctrl + End again on the cleaned sheets — it should now land just past your real data
Compare the file size with your earlier note. Small data with a still-huge file means something else is stored inside — see the honest note below
Going forward, format only the range you use, and when you need a colour band to grow with data, use an Excel Table style instead of painting entire columns
🔴 When the shrink does not work: if the file stays huge after a clean used-range reset, look for embedded objects — pasted OLE objects, thousands of shapes or comments, or a pivot cache keeping a copy of old source data. Those need their own removal; range cleanup alone will not touch them. And always keep one untouched backup of the original file until the cleaned version is verified.
🎥 Watch the Complete Fix in the Video
The video shows this exact problem with a real bloated file — watch how quickly the diagnosis lands.
Because Clear Contents and the Delete key remove values, not formatting — and it is the formatting across hundreds of thousands of cells that stretches the used range and inflates the file. You must select the unused rows and columns and use right-click → Delete to remove them entirely, then save.
Is it safe to delete rows below my data?
Yes, if you have confirmed they contain nothing you need — check with Ctrl End and a scroll through first, and keep a backup copy of the original file. The deletion removes empty formatted rows; your data range itself is untouched when you start the selection below it.
My file is xlsb. Should I convert it?
Not automatically. The binary xlsb format is legitimately compact and fast for genuinely large workbooks — it is not a bloat symptom. Convert old .xls files to .xlsx, but leave a healthy .xlsb alone unless you have a specific reason to change it.
This guide is based on the Technical Anshu Excel troubleshooting video • Stuck on a step? Ask in the video comments — you will get an answer there 🙏