How to Reduce Excel File Size, and Speed Up a Slow Workbook

Find what makes your Excel file heavy and slow: formatting past the data, volatile formulas, duplicated rules, pivot caches and pictures. Fix each in order.

Founder, Glow Reports. , 4 min read
  • Excel
  • Performance
  • Data Cleaning
A bulging spreadsheet file squeezed in a press and coming out as a slim, light file
On this page
  1. Why is my Excel file so large when it has little data?
  2. How do you find and reset the last used cell?
  3. Which formulas make an Excel file slow?
  4. Can conditional formatting slow down Excel?
  5. How do pivot tables and pictures add to file size?
  6. Does saving as .xlsb reduce Excel file size?
  7. Should you stop keeping raw exports in the workbook?
  8. Where does Glow Reports fit?
  9. Common questions

Your workbook holds a few thousand rows, yet it weighs tens of megabytes, takes a minute to save and freezes each time you type in a cell. The rows are rarely the problem. A heavy Excel file usually carries formatting far past the data, formulas that scan whole columns, piles of conditional formatting rules, a pivot cache that duplicates the data and full-size pictures. Find which one applies, fix it in the order below, and save the file again after each step to see what changed.

Why is my Excel file so large when it has little data?

Excel saves every cell that holds data or formatting, so formatting applied far below or to the right of your data inflates the file. Pasted exports, whole formatted columns and deleted rows that kept their formats are the usual causes.

Microsoft states the rule plainly: "When you save a workbook, Excel stores only the part of each worksheet that contains data or formatting." Empty cells with formatting push the last cell past your data. Microsoft adds that this "causes the file size of the workbook to be larger than necessary" Microsoft Support.

The same bloat slows the file down. Microsoft's performance guide says oversized used ranges "can cause slow opening and increased file size, especially if they are caused by hidden rows or columns that have non-standard height or width" Microsoft Learn.

Make a backup copy before any of the fixes below. Several of them can't be undone.

How do you find and reset the last used cell?

Press on each sheet. If Excel jumps far below or to the right of your last row of data, delete the empty rows and columns in between, then save, close and reopen the file.

  1. Click any cell and press . Note where Excel lands.
  2. Click the row heading right below your last row of data, then press to select down to that last cell.
  3. Choose HomeClearClear All, or right-click the selection and delete the rows.
  4. Do the same for the columns right of your data, starting from the first empty column heading.
  5. Save, close and reopen. Press again. It should now land on your real last cell.

Microsoft's own steps end the same way: save, close, and "when you open the workbook again, the last cell of the data should be the last cell on the worksheet" Microsoft Support. Formulas that point into the deleted area shrink or turn into #N/A, so check them afterwards.

Two built-in tools do this for you. In Excel for Microsoft 365, ReviewCheck Performance opens a Workbook Performance pane that counts the cells holding formatting but no data, with Optimize sheet and Optimize all buttons Microsoft Support. On Windows with Microsoft 365 Apps for enterprise, the Inquire tab has Clean Excess Cell Formatting. If the tab is missing, turn it on under FileOptionsAdd-ins, then COM Add-ins. Microsoft warns that this cleanup can't be undone and, in some cases, can increase the file size Microsoft Support.

Which formulas make an Excel file slow?

Volatile functions and formulas over far more cells than the data holds. OFFSET, INDIRECT, TODAY, NOW and RAND recalculate at every change, and array formulas over whole columns check all 1,048,576 rows.

Microsoft lists the volatile functions: "RAND(), NOW(), TODAY()" and the less obvious "OFFSET(), CELL(), INDIRECT(), INFO()". A volatile function "is always recalculated at each recalculation even if it does not seem to have any changed precedents" Microsoft Learn. A dynamic range built with OFFSET, copied down 20,000 rows, runs again every time you type anywhere.

Whole column references are a mixed case. SUM and SUMIF handle well because they stop at the last used row. Array formulas and SUMPRODUCT don't, and Microsoft says an array formula that references a whole column "is extremely slow to calculate" Microsoft Learn. The fix Microsoft recommends is an Excel table (InsertTable). A structured reference such as grows and shrinks with the data.

Three habits help:

  • Replace OFFSET ranges with a table or with INDEX, which Microsoft confirms is not volatile.
  • Compute a repeated lookup once in a helper column and point other formulas at it.
  • Once a month is closed, copy its formula columns and paste them as values.

When a formula shows an old result after these changes, the workbook may be in manual mode. See why an Excel formula is not calculating.

Can conditional formatting slow down Excel?

Yes. Every visible conditional format is checked at each calculation, and copying or inserting rows tends to split one rule into dozens of near-copies over fragmented ranges.

Microsoft's guide says that using a lot of conditional formats and data validation "can significantly slow down calculation", because each conditional format formula of a displayed cell is evaluated at each calculation Microsoft Learn.

Open HomeConditional FormattingManage Rules and pick This Worksheet in "Show formatting rules for" Microsoft Support. A long list of the same rule with ranges such as is the sign. Delete the copies, keep one rule, and set its "Applies to" box to the full column of the table.

How do pivot tables and pictures add to file size?

A pivot table keeps a copy of its source data, called the pivot cache, inside the file. Pictures are stored at full resolution unless you compress them. Both can weigh more than the data itself.

For the pivot cache, select a cell in the pivot table, go to PivotTable AnalyzeOptionsData, clear "Save source data with file" and tick "Refresh data when opening the file" Microsoft Support. The file then rebuilds the cache when it opens. If a pivot table stops updating, how to refresh a pivot table covers the causes.

For pictures, select one and choose Picture FormatCompress Pictures. Clear "Apply only to this picture", tick "Delete cropped areas of pictures" and pick the default resolution. To set it for the whole file, go to FileOptionsAdvanced and, under Image Size and Quality, tick "Discard editing data" and set the default resolution to 150ppi or lower. Microsoft notes that discarded editing data and cropped areas can't be restored Microsoft Support.

Does saving as .xlsb reduce Excel file size?

Usually, yes. The binary workbook format stores the same content in a more compact form. Use FileSave As and pick Excel Binary Workbook (*.xlsb) in the type list.

Microsoft's support page says "for most purposes, the binary format is sufficient and saves to a smaller file" Microsoft Support. Its performance guide adds that .xlsx files are "1.5 times larger on average" than .xlsb Microsoft Learn. Save as .xlsb after the cleanups above, not instead of them. A compact file full of empty formatted rows still opens slowly. Check first that the people and tools who read the file accept .xlsb.

Should you stop keeping raw exports in the workbook?

If each month you paste a new export below the last one and add formulas over it, yes. Load the exports with Power Query instead, and keep only the summary on the sheet.

Power Query reads a folder of CSV or Excel files and appends them on refresh, so the raw rows don't need to sit in cells with formulas next to them. The steps are in how to merge Excel and CSV files with Power Query. Clean the export before it lands, as in how to clean data in Excel, so you are not fixing types with extra formula columns.

While you rework a heavy file, switch calculation to manual with FormulasCalculation OptionsManual and press when you want results Microsoft Support. Microsoft's guide says workbooks that take more than a fraction of a second to recalculate should be set to manual to avoid the delay after each change Microsoft Learn. Switch back to Automatic when you are done, or the figures stay stale.

Google Sheets has a ceiling of its own: up to 20 million cells or 100MB for a spreadsheet created in or converted to Sheets Google Drive Help. Moving a bloated workbook there doesn't remove the bloat.

Where does Glow Reports fit?

The fixes above make a heavy workbook light again. The weight often comes back because the workbook does two jobs: it stores months of raw exports and it computes the report on top of them.

Glow Reports separates the two. You upload one or several CSV or Excel exports and ask for the report in plain language, such as revenue by month and by region. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure and can be edited. The report exports to PDF and stays in the workspace, so the raw rows don't need to live in a workbook full of formulas. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

Why is my Excel file still large after deleting rows?

The deleted rows may have kept their formatting, or the file was not saved and reopened. Press to see where Excel thinks the sheet ends, delete everything past your data, then save, close and reopen.

Does .xlsb change my formulas or data?

No. It stores the same workbook in a binary format instead of XML. Check that any tool or person reading the file can open .xlsb before you switch.

How do I find which sheet makes the file heavy?

Save a copy, delete one sheet, save and compare the size. Repeat for each sheet. Microsoft's performance guide also suggests pressing on each sheet in turn to spot an oversized used range.

Can I reduce the file size in Excel for the web?

Microsoft's steps for compression, binary format and the pivot cache use the desktop app menus. Open the file in the desktop app, run the fixes, then save it back to OneDrive or SharePoint. On SharePoint, Microsoft notes you may not see Save As, so save a local copy as .xlsb and replace the original.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your CSV or Excel exports to Glow Reports and ask for the report in plain language, with the SQL behind each figure.

Keep reading