Microsoft Excel was designed to be a tool for managing relatively small datasets for Finance & Accounting purposes, today we see that Excel is used often for large data analysis, prototyping of complex solutions (built often with VBA Marcos). You probably wondered here struggling with bloated Excel file, frustrated with having to wait for calculations to complete or constantly crashing Excel file.
Reduce Excel file size… or change the tool
Before we start working to reduce the Excel this is a good moment to ask yourself the question…
Is Excel the right tool for the job?
If you are convinced so proceed further, otherwise consider that Microsoft Excel has built several tools dedicated to managing with challenges that appear when working with larger data sets:
- PowerQuery – this Microsoft AddIn for Excel was created specifically for pulling and running queries on large datasets in Excel. You can easily merge worksheets, csv files or run complex relationship queries across your worksheets
- PowerBI – Microsoft PowerBI is a free application you can download from the Microsoft Store that allows you to create custom dashboards, run complex analytics… and easily publish your dashboards and reports online
Save file as Binary (XLSB)
Saving a file in XLSB from my experience often resulted in reducing file size even by 70%. If you don’t know the difference between a XLSX (OpenXML format) and XLSX (Binary Excel format) file format I encourage you read my post on XLSB.
Since Excel 2007 Microsoft was pressured to open up the Excel format so other applications could use it. This meant moving away from the binary file format which was storage friendly and would open efficiently, to the more heavy file format (XMLs files compressed to a ZIP file). In effect the XSLX file format will always be larger than the XLSB.
Remove Used Cell Range
A troublesome thing that can increase your Excel file size is Used Range. Imagine the worksheet below. Let us assume at some point I added some data into cell G16, and afterwards I deleted it. Although de facto I am only now using A1:C4, de jure Excel will keep in memory the entire range of A1:G12 expanding the file size.
Removing Unused Ranged
So what to do to reduce your Used Range only to the actual range you are using? Follow the steps below:
Remove data and formatting from unused cells
Reset Worksheet Used Range
Sub ReduceRangeActiveWorksheet() Debug.Print ActiveSheet.UsedRange.Rows.Count End Sub
Next run the Macro (or use the F5 shortcut). The Used Range should be now updated.
Remove Hidden / Unused Worksheets
Another reason for large file size is having many unused or hidden worksheets in your Workbook. Each Excel Worksheet has it’s own share of metadata, more importantly, however, you may be keeping sheets with similar datasets, copies or unnecessary Pivot Tables that also take up a lot of space. Follow steps below to show and delete unnecessary hidden worksheets.
Unhide hidden worksheets
Delete unused Worksheets
Formatting adds additional kilobytes to your Excel file size. A good approach is to remove any formatting from cells that don’t need formatting.
To remove cell formatting you can read this MSDN article or follow below:
Select Cells and click Clear Formats
Another reason for Excel to have an unreasonable file size is due to media, especially Images. Although you may think cropping and Image and resizing reduce its size, in fact Excel still keeps the entire image in memory. Hence the only way to free Image memory is to Compress Pictures.
Crop and Compress Images in Excel
To Compress your Images click on your Image(s) in Excel and go to Format->Adjust->Compress Pictures. Next select your preffered options. Select Delete cropped Areas of pictures to remove the unseen cropped areas of your images as well as the preffered resolution (the lower the more pixelated the image will seem):
Other honorable mentions to reduce your Excel file size may be:
- Remove unused Pivot Tables
- Remove Pivot Cache
- Replace Formulas with Data (Copy Paste as Data)
Feel free to comment below and add your ideas!