AjapaReporter

Home life · Updated

How to Shrink Excel File Size: 5 Proven Methods

Time it takes10 min
Steps5in the list below
LevelEasyno special skills
Official sourceMicrosoft Supportcheck it for changes

Quick answer You can shrink an Excel file by removing unused formatting, compressing images, deleting hidden sheets, and saving as .xlsx instead of .xls. These methods can reduce file size by up to 80%.

Large Excel files are slow to open, hard to email, and can crash. The main culprits are unused formatting, embedded images, and hidden data. This guide shows you five practical ways to reduce file size without losing data.

What You’ll Need

  • Access to the Excel file you want to shrink
  • Microsoft Excel (2010 or newer recommended)
  • A backup copy of the file (optional but wise)

Step-by-Step

  1. Save as .xlsx format – If your file is currently in the older .xls format, go to File > Save As and choose “Excel Workbook (*.xlsx)” from the dropdown. The .xlsx format uses better compression and can significantly reduce file size. If your file is already .xlsx, skip this step.
  2. Remove unused formatting – Select all cells in each worksheet by clicking the triangle at the top-left corner (or press Ctrl+A). Then go to Home > Clear > Clear Formats. This removes any formatting applied beyond the used range. Alternatively, you can use the “Find & Select” > “Go To Special” > “Last Cell” to see where the used range ends. If the last cell is far beyond your data, delete the extra rows and columns.
  3. Compress images – If your file contains pictures, click on any image to select it, then go to Picture Format > Compress Pictures. Choose a lower resolution (e.g., 150 ppi or 96 ppi) and check “Apply to all pictures”. This can drastically reduce size if you have many high-resolution images.
  4. Delete hidden sheets and unused data – Right-click on any sheet tab and select “Unhide” to see if there are hidden sheets. If you find any that you don’t need, right-click and delete them. Also, check for hidden rows or columns within sheets and delete them if they contain no data.
  5. Remove conditional formatting and data validation – Go to Home > Conditional Formatting > Manage Rules and delete any rules that are not needed. Similarly, go to Data > Data Validation and clear any validation rules that are no longer necessary. These features can bloat the file size even if they don’t show data.

Common Mistakes

  • Not clearing formatting on all sheets – If you only clear formatting on the active sheet, other sheets may still have hidden formatting.
  • Using “Clear All” instead of “Clear Formats” – “Clear All” removes data and formulas, which you don’t want. Always use “Clear Formats” to keep your content.
  • Forgetting to check for hidden rows/columns – Hidden rows and columns can still contain data and formatting, so they add to file size.
  • Compressing images only once – If you add new images later, you’ll need to compress them again.
  • Saving as .xls after doing all this – If you save back to .xls, you lose the compression benefits of .xlsx.

Tips

  • Use “Inspect Workbook” – Go to File > Info > Check for Issues > Inspect Workbook. This tool can find hidden content that you might have missed.
  • Break up large files – If your file is still too big, consider splitting it into multiple workbooks by topic or by month.
  • Use tables instead of ranges – Converting data ranges to Excel Tables (Ctrl+T) can sometimes reduce file size because Excel stores them more efficiently.
  • Avoid using entire rows/columns for formatting – Instead of formatting a whole column, format only the cells you need.
  • Regularly clean up your files – Make it a habit to remove unused formatting and data at least once a month.

Troubleshooting

  • File size still large after following steps – Check for embedded objects (like Word docs or PDFs) by going to Insert > Object. If you find any, delete them if they are not needed. Also, look for PivotTables with large cache data – right-click a PivotTable, go to PivotTable Options > Data, and uncheck “Save source data with file”.
  • Cannot select all cells due to file being huge – If the file is so large that selecting all cells is slow, try using the “Name Box” (the box left of the formula bar) and type “A1” then press Enter, then use Ctrl+Shift+End to select the used range, and then clear formats.
  • Compressing images doesn’t reduce size – Some images are already compressed. Try removing them and re-inserting them at a lower resolution. Also, check if there are images in headers/footers – go to Insert > Header & Footer and remove any images there.
  • Hidden sheets won’t unhide – If the Unhide option is grayed out, the workbook might be protected. Go to Review > Unprotect Workbook, then try again.
  • File becomes corrupted after clearing formatting – This is rare, but if it happens, you can undo (Ctrl+Z) or restore from your backup. Always keep a backup before making major changes.

Frequently Asked Questions

Why is my Excel file so large even with little data?

Often it’s due to formatting applied to entire rows or columns, hidden sheets, or embedded images. Even a small amount of data can have a huge used range if you’ve formatted cells far beyond your data.

Will removing formatting affect my formulas?

No, clearing formats only removes visual formatting (like colors, borders, fonts). It does not affect the data or formulas in the cells.

Can I shrink an Excel file without opening it?

Not easily. You need Excel to remove formatting and compress images. However, you can try using a zip tool to compress the file itself, but that only helps for transfer, not for opening in Excel.

How much can I expect to reduce the file size?

It varies. If your file is bloated with unused formatting and high-res images, you could see a reduction of 50-80%. If it’s already clean, the reduction will be minimal.

Is it safe to delete hidden sheets?

Only if you are sure they contain no data you need. Check each hidden sheet’s content before deleting. If you’re unsure, copy the data to a new workbook first.

Does saving as .xlsx always work?

Yes, .xlsx is the default format for Excel 2007 and later. It uses ZIP compression, which is more efficient than the .xls format. However, if you need to use the file with older versions of Excel, you may need to keep the .xls format.

Sources: Microsoft Support. Menus and buttons can differ slightly by version. Spotted something out of date? Tell us.