Summary

A workbook with four thousand rows has no business being 40 MB, and when it is, the weight is almost never the data. It is empty cells Excel still thinks are in use, formatting applied to entire columns, images pasted at camera resolution, and conditional formatting rules that multiplied every time somebody copied a row. This article works through the causes in the order that recovers the most space, names every step that deletes something, and shows you how to protect yourself before you start.

Symptoms

• The workbook takes 30 seconds or more to open or save.
• Outlook refuses the attachment, or the message bounces back with a size error.
• Scrolling, sorting, or typing in a cell lags behind your keyboard.
• The scroll bar is tiny, as though the sheet ran to tens of thousands of rows, when your data stops at row 500.
• Saving to a network drive, OneDrive, or SharePoint stalls or times out.
• The .xlsx file is tens of megabytes despite a modest amount of data.

In this guide

• 1. Record the Size and Save a Copy First
• 2. Find the Phantom Used Range
• 3. Delete the Empty Rows and Columns, Then Save and Reopen
• 4. Clear Formatting Applied to Whole Columns
• 5. Compress the Pictures
• 6. Cut Back Multiplied Conditional Formatting Rules
• 7. Stop Saving the Source Data with Every PivotTable
• 8. Replace Whole-Column Formulas with Real Ranges
• 9. Check the File Format

Before You Start

• Microsoft Excel, desktop version. Compress Pictures and the used-range fix are not available in Excel for the web.
• Write access to the workbook and enough disk space to keep a second copy.
• The name of anyone else who edits this file — step 1 explains why.
• No admin rights required.

Instructions

1. Record the Size and Save a Copy First

Several steps below delete rows, formatting, and image data permanently. Establish a baseline and protect the original before you touch anything.

1. In File Explorer, navigate to the workbook, right-click it, and select Properties. The General tab shows the current size. Write it down — you will check it again after each step.
2. Open the workbook in Excel.
3. Click File > Save a Copy (or Save As in older builds).
4. Add -original to the filename and click Save. This copy is your undo button for everything that follows.
5. Close that copy and reopen the working file. Everything from here happens on the working file.
6. If the workbook lives on a shared drive, tell the other editors you are working on it. Deleting rows while somebody else has it open leads to a conflict dialog and a merge nobody enjoys.

Note: Ctrl + Z reverses these changes only while the file stays open. Once you save, the discarded rows and pixels are gone. That is the whole reason for the -original copy.

2. Find the Phantom Used Range

This is the single biggest cause of a bloated workbook, and it takes one keystroke to spot.

1. Click any cell on your first worksheet.
2. Press Ctrl + End. The cursor jumps to what Excel considers the bottom-right corner of the used area, and the Name Box at the left of the formula bar shows that cell's address.
3. Compare it to where your data actually stops. Data ending at D500 with Ctrl + End landing on XFD65536 means Excel is storing more than a billion cells that hold nothing.
4. Repeat on every worksheet. Click each tab in turn and press Ctrl + End. Note down which sheets are affected.

Excel remembers the furthest cell that ever held data or formatting on a sheet, and it keeps storing every cell up to that point. Pressing Delete over a block of rows clears the contents but leaves the cells themselves in the used range, which is exactly how a sheet that once had 60,000 imported rows stays 60,000 rows heavy after somebody tidied it up.

Verification: Look at the vertical scroll bar. On a sheet with 500 real rows it should be a large block filling most of the track. A sliver at the top is the same problem seen from a different angle.

3. Delete the Empty Rows and Columns, Then Save and Reopen

Deleting the rows is half the fix. Saving and reopening is the half that actually reclaims the space.

1. Go to the first affected sheet and scroll to the last row of real data.
2. Click the row number of the first empty row beneath it. The whole row highlights.
3. Press Ctrl + Shift + End. The selection extends to the far corner of the used range.
4. Before deleting, check the selection is genuinely empty. Look at the status bar along the bottom of the window: with an entirely empty selection it shows nothing but Ready, while a Count figure means cells in there still hold something. If it does, scroll through and find out what before you go further. If you delete something you needed, press Ctrl + Z the moment you notice — while the file is unsaved, nothing is lost.
5. Right-click any selected row number and choose Delete. Not Clear Contents — clearing empties the cells and leaves them in the used range, which changes nothing.
6. Do the same for columns: click the column letter to the right of your last real column, press Ctrl + Shift + End, right-click a selected column letter, and choose Delete.
7. Repeat on every sheet you flagged in step 2.
8. Press Ctrl + S to save, close the workbook entirely, and reopen it.
9. Press Ctrl + End again. The cursor now lands beside your real data. Check the size in Properties — this is usually where the megabytes disappear.

Excel recalculates the used range when the workbook is saved, which is why the file size on disk does not move while the file is still open. If Ctrl + End still overshoots after reopening, one sheet was missed — work back through them.

4. Clear Formatting Applied to Whole Columns

Selecting a column by its letter and applying a fill colour, a border, or a number format tells Excel to store that formatting for all 1,048,576 rows.

1. Open the sheet and click the column letter to the right of your data.
2. Press Ctrl + Shift + End to select everything out to the used-range corner.
3. On the Home tab, in the Editing group at the far right, click Clear — the eraser icon — and choose Clear All.
4. Select the empty rows below your data the same way and clear those too.
5. Delete the cleared rows and columns as in step 3, then save, close, and reopen.

Clearing here removes formatting from cells that hold nothing. If you overshoot and clear a column you needed, Ctrl + Z restores its formatting and contents together — before the save.

Tip: To stop this recurring, format the data range rather than the column. Select your actual cells, or better, click inside the data and press Ctrl + T to make it an Excel table. Table formatting grows and shrinks with the data instead of running to the bottom of the sheet.

5. Compress the Pictures

A logo pasted straight from a phone or a screenshot tool carries far more resolution than a worksheet ever displays.

1. Click any picture in the workbook. A Picture Format tab appears at the end of the ribbon — it is labelled Format in Excel 2019 and earlier.
2. Click Picture Format, then click Compress Pictures in the Adjust group at the far left.
3. In the Compress Pictures dialog, under Compression options, clear the checkbox for Apply only to this picture. Clearing it covers every image in the workbook in one pass.
4. Check Delete cropped areas of pictures. Excel keeps the whole original image behind every crop until you do this.
5. Under Resolution, choose Web (150 ppi) for anything read on screen, or E-mail (96 ppi) for the smallest possible file.
6. Click OK. The dialog closes with no progress bar — the work is already done.
7. Look at every image before you save. Compression shows first on logos and on fine text inside screenshots. If anything looks rough, press Ctrl + Z immediately and repeat at Print (220 ppi).
8. Press Ctrl + S and recheck the size.

To stop it recurring, click File > Options > Advanced, scroll to Image Size and Quality, check Discard editing data, confirm Do not compress images in file is unchecked, and set Default resolution to 150 ppi. These settings apply to the current workbook only.

6. Cut Back Multiplied Conditional Formatting Rules

Copying and pasting rows splits one rule into many, and a workbook can end up carrying thousands of near-identical rules.

1. Open the sheet, then on the Home tab click Conditional Formatting in the Styles group and choose Manage Rules.
2. In the Conditional Formatting Rules Manager, set Show formatting rules for to This Worksheet. The list fills with every rule on the sheet.
3. Read the Applies to column. One rule reading =$C$2:$C$500 is healthy. Two hundred rules reading =$C$2, =$C$3, =$C$4 are the same rule, shattered by copy-paste.
4. Select a duplicate rule and click Delete Rule. Deleting a rule removes the colour it applied — the cell values underneath are untouched, and you rebuild the rule in the next step.
5. Repeat until one copy of each distinct rule remains.
6. Select the surviving rule, click Edit Rule, and set Applies to to the full range the rule should cover, then click OK.
7. Click OK to close the manager, then save and check the size.

Tip: When you copy rows into a sheet that carries conditional formatting, paste with Values rather than a plain paste — right-click the destination and click the Values icon under Paste Options. That keeps the existing rules intact instead of fragmenting them.

7. Stop Saving the Source Data with Every PivotTable

A PivotTable stores its own hidden copy of the data it was built from, so a workbook with three pivots may be carrying the same rows four times over.

1. Click any cell inside a PivotTable.
2. Open the PivotTable Analyze tab on the ribbon — it is called Analyze or Options in older builds — and click Options in the PivotTable group at the far left.
3. In the PivotTable Options dialog, open the Data tab.
4. Clear the checkbox for Save source data with file.
5. Check Refresh data when opening the file in the same section. With the cached copy gone, the PivotTable rebuilds itself from the source range each time the workbook opens.
6. Click OK, then repeat for every PivotTable in the workbook.
7. Save, close, and reopen. The PivotTable populates as the file loads.

Clearing that box means the PivotTable is empty until it refreshes. Keep it checked if the source data lives in another workbook that recipients will not have — otherwise they open a blank pivot. Nothing is deleted either way; re-checking the box restores the cached copy on the next save.

Tip: Build extra PivotTables by copying an existing one rather than starting from Insert > PivotTable. A copy shares the original's cache; a new one creates a second copy of the same data.

8. Replace Whole-Column Formulas with Real Ranges

This one buys speed more than bytes, and on a laggy workbook it is the difference you actually feel.

1. Press Ctrl + F, click Options, set Within to Workbook and Look in to Formulas.
2. Search for :A) or A:A to find formulas pointing at entire columns — =SUMIF(Sheet2!A:A,...) and =VLOOKUP(B2,Sheet2!A:D,...) are the usual shapes.
3. Click Find All to list every match with its sheet and cell, then click an entry to jump to it.
4. Edit the formula to name the rows that exist: change A:A to A2:A5000.
5. Better still, select the source data, press Ctrl + T to convert it to a table, name the table in the Table Design tab, and reference the table name. The range then grows and shrinks with the data by itself.
6. Press Ctrl + S and open the workbook again to feel the difference.

A whole-column reference asks Excel to consider 1,048,576 rows every time the sheet recalculates. Multiply that by a few hundred formulas and the workbook spends its time counting empty cells.

9. Check the File Format

The format the workbook is saved in sets a floor on how small it can get.

1. Look at the title bar. A file showing Compatibility Mode beside the name is in the legacy .xls format, which stores everything uncompressed.
2. To convert it, click File > Info, then click Convert, and click OK when Excel explains the workbook will be upgraded. Press Ctrl + S. Converting typically halves the size on its own.
3. If Convert is not offered, use File > Save As, set Save as type to Excel Workbook (*.xlsx), and click Save.
4. For a workbook that is genuinely enormous after everything above — hundreds of thousands of rows of real data — click File > Save As, set Save as type to Excel Binary Workbook (*.xlsb), and click Save. The binary format is usually substantially smaller and opens faster.

Be deliberate about .xlsb. It does not open in Excel for the web at all, and systems that accept .xlsx uploads — finance portals, reporting tools, some document management systems — frequently reject it. Use it for a heavy workbook that stays inside your team on desktop Excel, and keep an .xlsx copy of anything you send outside.

If the file is still too large to email after all this, stop compressing and change how you send it: save it to OneDrive and share a link instead, which How to Manage Email Attachments and Send Large Files in Outlook covers step by step.

Troubleshooting

WARNING

Steps 3 and 4 delete rows and columns, and the deletion becomes permanent the moment you save. Before you delete, select the range and read the status bar: a Count figure appearing there means the selection still holds data. Keep the -original copy from step 1 until you have opened the shrunk workbook and confirmed every sheet, formula, and PivotTable still works.

Symptom / Error	Potential Cause	Solution
File size unchanged after deleting thousands of rows	The workbook was not saved and reopened	Excel recalculates the used range on save. Press Ctrl + S, close the file completely, and reopen it (step 3).
Ctrl + End still overshoots after saving	Another sheet still carries the phantom range	Click each tab in turn and press Ctrl + End. Fix every sheet, then save and reopen once more.
Excel cannot complete this task with available resources	Too many cells or rules for the memory Excel has	Close other workbooks, then work one sheet at a time. If it persists, see How to Fix Excel Crashing or Freezing.
A PivotTable is empty after reopening	Save source data with file cleared while the source is unreachable	Right-click the PivotTable, choose Refresh. If the source is in another file, re-check the box in PivotTable Options > Data (step 7).
#REF! appears after deleting columns	A formula pointed into the deleted range	Press Ctrl + Z if you have not saved. Otherwise reopen the -original copy and delete a narrower range.
The workbook will not save as .xlsb	The file is open elsewhere, or the destination is read-only	Close the file in any other window, then save to your desktop first and move it afterwards.
Still 20 MB with no images and a clean used range	Hidden sheets, or a very large embedded object	Right-click any sheet tab and choose Unhide to see what else is in the workbook, then check embedded objects and links to other files under Data > Workbook Links — How to Pull Data from Another Sheet in Excel explains that pane.
Attachment rejected at 22 MB despite a 25 MB limit	Encoding adds around 30 percent in transit	Send a OneDrive link instead — see How to Manage Email Attachments and Send Large Files in Outlook.

Related Articles

• How to Shrink a Large PowerPoint File
• How to Fix Excel Crashing or Freezing
• How to Manage Email Attachments and Send Large Files in Outlook
• How to Shrink a Large PDF File Size for Emailing

Did this solve your problem?