Reduce Excel file size for Optimising Performance

10 Top tips: How to reduce Excel file size without deleting data?

1) Save the file in the right version as:

  • Excel Workbook (xlsx)
    Save as new version instead of the older one or
  • Zip the Excel file (zip).

2) Clear unused cells

  • Clear visible unused tables, columns and formatted cells.
    Select it and Clear All or select rows or columns and delete (Ctr+-)
  • Clear invisible content using the below method:
    Ctr+G / Special… / blanks / Clear All
    Note: CTR + End

3) Reduce graphics

  • Delete unused images/objects
    To find objects that are hidden, stacked, or behind text, click:
    Home, Find & Select, Selection Pane…

  • Compressing Picture
    Select image / Compress Picture / Minimise document size for sharing

  • Resizing

4) Reduce Condition Formatting

  • Select the entire worksheet
    (Ctrl+A and you may require use Ctrl+A again) or simple click Select All button

  • Home / Conditional Formatting / Manage Roles…
    Select irrelevant roles and Delete Rule

5) Reduce on Data Validation

  • Select the entire worksheet as above
  • Data / Data Validation / Setting / Allow: Any value

6) Use manual calculation

  • Formulas / Calculation options / Manual

7) Use Pivot Tables and Excel Tables format and named Range

8) Optimise formulas

  • Avoid using volatile function such as: TODAY, NOW, INDIRECT, RAND, RANDBETWEEN, CELL, OFFSET, INFO.
  • Replace VLOOKUP, HLOOKUP and LOOKUP function with INDEX & MATCH
  • Replace nested IF functions with any of below alternative: REPT, INDEX & MATCH, SUMIF(s) or Boolean logic
  • Check cells containing formulas and replace them with values where a formula is unnecessary or rarely used
    Note: Advanced Excel Test

9) Reduce worksheets

Check if you have unused worksheets including hidden ones and delete them.

10) How to Reduce Excel file size Optimise using VBA

Tags: clear, Excel, reduce, size