Spreadsheets

Reduce an Excel file's size: used range, formatting and XLSB

How to reduce an Excel file's size: reset the used range, clear blank formatted rows, compress images, trim pivot caches, and when XLSB is worth using.

Editorial illustration for Reduce an Excel file's size: used range, formatting and XLSB

An Excel expense file is usually big for one of four reasons: Excel thinks the sheet extends far below your data, formatting has been applied to entire columns, there are pictures or pivot caches inside, or the workbook holds a lot of real data in the heavy XLSX format. Fix them in that order, and record the file size before and after each step.

Step 0: record the starting size

In File Explorer, note the size, for example 14.2 MB. Make a copy of the workbook to experiment on. After each step save, check the size again and write it down. You will quickly see which step mattered.

Why is my file big when the data is small?

Press Ctrl+End on each sheet. Excel jumps to the last cell it believes is in use. If that cell is far below or to the right of your data, perhaps row 1,000,000 because someone once formatted a whole column, Excel is storing information about all those blank cells.

  1. Select the first unused row below your data, press Ctrl+Shift+Down to extend to the bottom, right-click a row number and choose Delete. Clearing is not enough; the rows must be deleted.
  2. Do the same for unused columns to the right with Ctrl+Shift+Right.
  3. Save and close the workbook, then reopen it and press Ctrl+End again. It should land on your last real cell.

Which hidden items add weight?

  • Pictures: scanned receipts pasted into cells are the biggest culprit. Select a picture, open Picture Format, Compress Pictures and choose a lower resolution, or keep receipts in a folder and write the file name in a cell.
  • Pivot caches: each PivotTable keeps a copy of its source data. In PivotTable Options, Data tab, untick Save source data with file and tick Refresh data when opening the file. The file shrinks, but you must refresh after opening.
  • Conditional formatting: pasting rows over and over multiplies rules. Open Home, Conditional Formatting, Manage Rules, choose This Worksheet and delete duplicates.
  • Old tabs: monthly copies of a sheet you no longer use. Move them to an archive workbook.

Is XLSB smaller than XLSX?

Usually, yes. XLSB is Excel's binary format. For large, data-heavy files it saves smaller and opens faster. Use File, Save As, and choose Excel Binary Workbook.

FormatGood forWatch out for
XLSXSharing with anyone, opening in other appsLarger for big data sets
XLSBLarge files only you use, faster openingSome other programs cannot read it, so check before sending to a CA or uploading anywhere
CSVRaw transactions onlyNo formulas, formatting or extra tabs

Try it on the copy and compare the size to the XLSX. If the saving is small, stay with XLSX for the sake of compatibility.

A worked before and after

Say a household workbook is 14.2 MB. After deleting empty formatted rows the file is 9.8 MB, after compressing six receipt photos it is 4.1 MB, and after saving as XLSB it is 2.6 MB. Those figures are only an illustration; yours will differ, but the table you keep tells you which step to repeat on other files. In this example the first two steps removed 10.1 MB of the 11.6 MB saving: (14.2 minus 4.1) is 10.1, and (14.2 minus 2.6) is 11.6.

When to stop shrinking

If the file is still large because it holds years of statement lines, split by financial year, April to March, so each workbook stays manageable, and keep the older ones read-only. SpendixAI can hold the statements instead, and receipts attach to their transactions there, so the pictures do not live inside your workbook.

  • Used range reset: Ctrl+End lands on real data.
  • Pictures compressed or moved out.
  • Pivot caches trimmed or refreshed on open.
  • Duplicate formatting rules removed.
  • Size recorded before and after for each step.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports