Reduce Excel file size for Optimising Performance
10 Top tips: How to reduce Excel file size without deleting data?
1) Save the file in right version as:
- • Excel Workbook (xlsx)
Save as new version instead of then older or
You can significantly reduce Excel size file compared to default version or
- • Zip the Excel file (zip).
2) Clear unused cells
- • Clear visible unused table, columns and formatted cells.
Select it and Clear All or select rows or columns and delete (Ctr+-)
- • Clear invisible content using method:
Ctr+G / Special… / blanks / Clear All
Note: CTR + End
3) Reduce graphics
- • Delete unused images/object
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 maybe requite 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 and delete them.
10) How to Reduce Excel file size Optimize using VBA
