How to reduce Excel file size

1. Find out what's taking the space

An .xlsx file is a zip folder of smaller files. Copy the workbook, rename the copy from .xlsx to .zip, and open it: Windows and macOS list the parts with their sizes. Pictures live in xl/media, each sheet's cells in xl/worksheets, pivot data in xl/pivotCache, and formats in xl/styles.xml. Delete the copy when you're done.

The table is that list for an 18 MB sales workbook. Before touching anything, rank the parts. Here the pictures alone are half the file, so compressing them beats any amount of tidying the formulas.

2. Delete the used range Excel thinks you have

Excel saves every cell in the used range, from A1 to the last cell that has ever held content or formatting. Format a whole column, fill a formula too far, or paste and delete a big block, and the used range can stretch to row 1,048,576. Press Ctrl+End: if it lands far below or to the right of your data, that's waste.

Clearing those cells doesn't shrink the range. Deleting them does. In current Microsoft 365, Review > Check Performance finds formatted empty cells and can clean them up for you, if your version shows it.

1. Click the row number below your last row of data, press Ctrl+Shift+Down, right-click and choose Delete.
2. Click the column letter right of your last column, press Ctrl+Shift+Right, right-click and choose Delete.
3. Repeat on each sheet, then save. Ctrl+End should now land at the end of your real data.

3. Compress the pictures

Photos pasted straight from a phone or a screenshot tool are often several megabytes each, far more detail than a sheet needs. Compress them all at once, and tell Excel to throw away the cropped-off parts it keeps by default.

For future pictures, go to File > Options > Advanced, Image Size and Quality: tick Discard editing data and pick a lower Default resolution. In this workbook, pictures are just over half of the file.

1. Click any picture, then Picture Format > Compress Pictures.
2. Untick Apply only to this picture so every picture is compressed.
3. Tick Delete cropped areas of pictures and choose a resolution such as Web (150 ppi) or Email (96 ppi).
4. Click OK and save.

4. Stop saving pivot caches with the file

Every PivotTable keeps its own copy of the source data, the pivot cache, and saves it in the file. With a large source table, that's a second copy of the data, sometimes several. Right-click the pivot, choose PivotTable Options > Data, untick Save source data with file and tick Refresh data when opening the file. The file shrinks, and the pivot rebuilds its cache when opened.

Pivots built from the same range normally share one cache. Separate caches creep in when pivots point at slightly different ranges or at copies of the data, so point every pivot at one table (Ctrl+T) and each copy of the data is saved once.

5. Clean up formatting and conditional formatting

Copying and pasting rows duplicates conditional formatting rules, and after a few months a sheet can have hundreds of near-identical rules. Go to Home > Conditional Formatting > Manage Rules, set Show formatting rules for to This Worksheet, and delete the duplicates, keeping one rule per job that covers the whole range.

Formatting whole columns or rows by clicking the header is fine when you mean it, but formatting that was applied to the whole sheet "just in case" bloats the used range. Workbooks that have been copied between many files can also collect thousands of unused cell styles; if the Cell Styles gallery is full of names you never created, that's styles bloat.

6. Save as an Excel Binary Workbook (.xlsb)

File > Save As and choose Excel Binary Workbook (*.xlsb). It holds the same sheets, formulas, pivots and charts, stores them in a compact binary format, and is often noticeably smaller and quicker to open for big data sheets. It can also hold macros.

The catch: some other tools, such as certain online viewers, import scripts and older add-ins, can't read .xlsb. Keep .xlsx when you share the file outside Excel, and test that the people you send it to can open it.

7. Smaller wins worth a minute

Turn finished formulas into values: last year's months won't change, so copy those cells and paste them as values. Delete sheets nobody uses, including hidden ones (right-click a tab > Unhide to see them). Remove broken and unused names in Formulas > Name Manager. Break links to other workbooks you no longer need in Data > Edit Links (Workbook Links in newer versions).