Why Is My Excel File So Large? Formatting Is Often Why

If your Excel file is far larger than the data in it, the cause is usually formatting, not data: formatting applied to cells far below or to the right of your last entry, conditional formatting rules that got copied hundreds of times, or styles dragged in from other workbooks. Press Ctrl+End on each sheet to see where Excel thinks your data stops, open the Conditional Formatting rules manager, and look at the Cell Styles gallery. Those three checks take about five minutes and find the problem more often than any compression trick.

This post gives you six checks in the order I run them, with how to spot each one, the fix, and what that fix can remove. Some fixes are completely safe. A couple will delete something you may have wanted, so I flag those before you click.

How do you tell whether formatting is bloating the file?

Compare the file size to what is actually in it. A workbook with a few thousand rows of plain numbers and text is small. An .xlsx file is a zipped bundle of XML files, and every cell that Excel remembers, every rule and every style takes up room in that XML. When a 40,000-cell dataset produces a file that takes a long time to open, the extra weight is almost never the 40,000 cells.

Here are the six checks in order. Start at the top; the cheapest, most common causes come first.

#	Check	How to spot it	Fix	What the fix can remove
1	Used range	Ctrl+End lands far below or right of your data	Delete the excess rows and columns, save, reopen	Anything you formatted out there on purpose, such as a pre-formatted entry area
2	Conditional formatting rules	Rules manager lists dozens or hundreds of entries, many identical	Delete duplicates, or clear rules and rebuild one	Highlighting you still wanted, if you clear too much
3	Cell styles	Cell Styles gallery runs to hundreds of custom styles	Delete unused styles, or copy sheets into a fresh workbook	Styles a cell still uses fall back to Normal
4	Images	A picture you inserted at full camera resolution	Compress Pictures	Cropped areas and editing data, permanently
5	Pivot caches and hidden sheets	A pivot table beside a large source range, or sheets you forgot	Stop saving source data with the file; delete dead sheets	The cached copy, so the pivot refreshes on open
6	File format	Everything above is clean and the file is still large	Save as .xlsb	Compatibility with tools that read .xlsx

Always work on a copy for checks 1 to 3. Several of these changes cannot be undone once you save.

Why does Ctrl+End jump far below your data?

Click any cell on a sheet and press Ctrl+End. Excel jumps to the last cell it considers used, the point where the farthest-right used column meets the lowest used row. If your data ends at row 800 in column H and you land on row 90,000 in column AB, Excel is carrying around roughly 90,000 rows of cells that hold nothing visible.

A cell becomes "used" when it holds a value, but also when it holds formatting. Someone shaded a whole block, applied borders to a range that was too generous, pasted in and then deleted a longer table, or set a fill on every column to make a sheet look finished. Clearing the contents does not release those cells. They stay in the file.

How to check:

On each sheet, press Ctrl+Home, then Ctrl+End. Note where you land.
If that cell is well past your data, the sheet is carrying excess.
Repeat for every sheet. One bad sheet is enough to bloat the whole file, and it is rarely the one you were looking at.

How to fix it:

Select the first empty row below your real data. Press Ctrl+Shift+Down Arrow, then right-click and choose Delete. Use Delete, not the Delete key: deleting the rows removes them, while pressing the key only clears the contents and leaves the formatting.
Do the same for the empty columns to the right with Ctrl+Shift+Right Arrow.
Save the file, close it and reopen it. Excel recalculates the last cell when it saves, so Ctrl+End often will not snap back until you do this.

What this can remove: any formatting you placed below or beside the data on purpose, for example an entry area you formatted in advance for next month. Check that nothing live sits out there before you delete.

If you would rather have Excel do the sweep, Microsoft ships a command for this. The next sections cover it.

How do duplicated conditional formatting rules pile up?

This is the quiet one. Every time you copy and paste a cell that has conditional formatting, Excel can create a new rule for the pasted range instead of extending the old one. Copy a formatted row a few hundred times, or paste a formatted block over and over while rearranging a sheet, and you can end up with hundreds of rules that all say the same thing about slightly different ranges. Each rule is stored in the file, and each one has to be evaluated when the sheet redraws. This is why a heavily formatted sheet gets sluggish as well as large.

How to check:

Go to Home → Conditional Formatting → Manage Rules.
In the Show formatting rules for list at the top of the dialog, choose This Worksheet. The default is the current selection, which hides the problem.
Scroll the list. A tidy sheet has a handful of rules. A bloated one has a long run of identical rules, each applied to a small separate range.

How to fix it: delete the duplicates, or clear all rules from the sheet (Home → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet) and rebuild a single rule that covers the whole column or table. Rebuilding is faster than it sounds, and a table makes it safer because a rule applied to a table column grows with it.

What this can remove: any highlighting you still wanted. If you clear and rebuild, make a note first of what the rules did. If you are deciding which rules are worth keeping, conditional formatting that actually helps covers which kinds earn their place.

Formatting applied to whole columns or rows is a related trap. Applying a fill or format to an entire column is common and not always costly on its own, but if the format ends up written cell by cell down to the bottom of the sheet, the file pays for it. If Ctrl+End told you the used range runs to the last row, this is a likely reason.

What does Clean Excess Cell Formatting remove?

Microsoft's own Clean Excess Cell Formatting command does check 1 for you. It removes formatting from cells beyond the last cell that is not blank. It lives on the Inquire tab in Excel for Windows, which is available in Microsoft 365 Apps for enterprise plans and equivalent editions. If the tab is missing, go to File → Options → Add-ins, choose COM Add-ins in the Manage box, click Go, tick Inquire and click OK. You then choose to clean the active sheet or all sheets, and finally choose whether to save.

Three things from Microsoft's own page are worth knowing before you run it:

There is no undo. Make a backup copy of the file first.
It can occasionally make a file bigger. Microsoft says there are certain cases where the process increases the file size, so compare sizes afterward.
It can strip conditional formatting. Because it works on everything past the last non-blank cell, a rule applied across a whole row can disappear from the columns beyond your data's final column.

Newer builds of Excel for Microsoft 365 also have Review → Check Performance. It opens a Workbook Performance pane that reports how many cells the workbook uses and how many can be optimized, then offers Optimize sheet or Optimize all. Microsoft's cleanup documentation says it finds cells that hold no data but still add bulk, such as formatted blanks. The one caution it states plainly: do not run it on a workbook that uses cells as pixel art, because Excel cannot tell those cells from unwanted formatted ones and will remove them. If you ran it by mistake, Undo recovers them. Which of these commands you have depends on your version and edition, so check your own ribbon rather than assuming.

Are images, pivot caches or hidden sheets the real cause?

If checks 1 to 3 are clean, look at what else is inside the file. Microsoft's guide to reducing the file size of Excel spreadsheets lists the other usual suspects, and the ones that come up most are these.

Pictures. A logo or screenshot pasted at full resolution can outweigh all of your data. Select a picture, open the Picture Format tab, choose Compress Pictures, and clear Apply only to this picture to compress every image in the file. You can tick Delete cropped areas of pictures. Under File → Options → Advanced, in the Image Size and Quality section, Microsoft suggests a default resolution of 150 ppi or lower and notes you rarely need more. What this removes: the cropped parts of images and the stored editing data, and you cannot get either back, so keep the original image files.

Pivot caches. A pivot table keeps a copy of its source data inside the workbook by default. If the source range is large, that copy roughly doubles the weight. Select a cell in the pivot table, open PivotTable Analyze → Options → Data, untick Save source data with file, and tick Refresh data when opening the file. What this removes: the cached copy, so the file takes a moment longer to open and needs access to its source to refresh.

Unused worksheets. Microsoft's advice here is plain: the more data points in the workbook, the bigger the file. If a sheet holds old data and no formulas you rely on, delete it. Check for hidden sheets as well, by right-clicking any sheet tab and looking at Unhide. A sheet that is hidden still counts toward the file size.

Does saving as .xlsb help?

Often, yes, and it is the one fix that does not require you to find the cause. Microsoft states that saving as an Excel Binary Workbook (.xlsb) instead of the default .xlsx can reduce the file size. The trade-off is in the format itself. The .xlsx format is XML-based, which is an open standard that third-party programs read. The binary format is smaller for most purposes, but tools that expect .xlsx may not open it.

To do it, go to File → Save As, pick Excel Binary Workbook (*.xlsb) in the Save as type list. To make it the default, use File → Options → Save and change the Save files in this format setting. If the file is in a SharePoint library and you do not see Save As, save a copy locally, convert that copy and replace the original.

I treat .xlsb as the last step, not the first. It shrinks the symptom and leaves the cause in place. The bloated used range or the five hundred duplicate rules are still there, and now they are in a format fewer tools can inspect. Fix the cause, then decide whether you need .xlsb at all. Do not expect a particular percentage from it; how much it saves depends on what the file contains.

Why the formatting causes keep coming back

A file does not get this way on its own. Someone formats an entire sheet to make it look finished. Someone copies a formatted block twenty times. Someone pastes a table from another workbook and brings its styles along. Each habit is reasonable in the moment and none of them show up on screen. The cleanup in this post only lasts if the next person formats the data range and nothing else.

That also makes the checks above a good habit before you send a file out. A file that opens quickly and sits at a sensible size is one of the quieter signs that a spreadsheet is ready. The messy spreadsheet checklist covers the rest of the pre-send list.

FAQ

Why is my Excel file so big with so little data?

Because the size comes from what Excel remembers, not only what you see. A used range that runs to the bottom of the sheet, hundreds of duplicated conditional formatting rules, a large uncompressed picture, a pivot cache holding a second copy of the data, or accumulated styles can each add far more weight than the visible cells. Work through the six checks above in order.

How do I reset the used range in Excel?

Press Ctrl+End to find where Excel thinks the data ends. Select the empty rows below your data (Ctrl+Shift+Down Arrow from the first empty row), right-click and choose Delete, then do the same for empty columns to the right. Save, close and reopen the file. Deleting matters: pressing the Delete key clears contents but can leave the formatting that keeps the cells "used". On Windows, the Inquire tab's Clean Excess Cell Formatting command does the same job.

Does conditional formatting make Excel slow?

A few well-built rules do not. Hundreds of duplicated rules applied to small scattered ranges do, because each one is stored in the file and each one is evaluated when the sheet redraws. Open Home → Conditional Formatting → Manage Rules, set Show formatting rules for to This Worksheet, and consolidate duplicates into a single rule over the whole range.

Is .xlsb smaller than .xlsx?

Microsoft says saving as Excel Binary Workbook can reduce file size, because .xlsx is XML-based and .xlsb is not. How much smaller depends on the file. The cost is compatibility: .xlsx is an open standard that third-party programs read, and .xlsb is not.

Start over with a clean copy

If your file is already tangled and you would rather not hunt through it, export a fresh copy. The SuperSheet Excel formatter writes a new .xlsx with formatting on the data range only, so the used range matches your data, and nothing is uploaded: it runs in your browser. Drop your file on it and download the new one.