Excel sheet
Microsoft’s Excel spreadsheet program is a part of the Office family of business software programs. Users of Excel may compute data in a spreadsheet. Do check out: compress excel file
The more data/formula/images are in the Excel sheet/workbooks it gets slow or sometimes hangs So it’s always simpler to cut some flab and keep the size in check, but it’s not always the answer. Another drawback is that because Excel workbooks are not web-based, you must send them through email after you’re done, and email has a size limit, making it challenging to share large files. Another option for sending workbooks is through IBM (Lotus Notes email client) but this takes a long time to send.
8 ways to reduce excel file size
You may use the methods listed below to make your Excel workbook reduced in file size.
- remove unwanted data or worksheets
This is one of the easiest ways to reduce your file size, you just have to remove unwanted data or worksheets from your workbook, this is the most direct way to reduce the file size.
You can do this by re-checking your sheets.
- transform/convert the format
XLSB – It’s a format to save the workbooks. ( saves as binary.bin file )
Other formats – XLSX & XLSM ( saves as XML file )
XLSB format helps to reduce the file size so it’s preferred.
XLSB vs XLSX/ XLSM
| XLSB | XLSX/ XLSM |
| This format has a higher reduction rate of files. | These comparatively consume more size. |
| This format tend to save faster and opens quickly | These are slow and takes time to save |
| You have no means of knowing whether a macro is included in an XLSB format. | You will be able to know if it includes macro |
| With XLSB file Power query cannot read data | If you use power query it’s recommended to use other formats. |
| It supports larger files | In general, it’s best to stay with the XLSX/XLSM formats if your file is short (less than 5 MB). |
- Remove unwanted formula
Formulas will increase the file size so if there are unwanted formulas you should remove it with the special key option in the dialog box.
Tip: be aware of volatile formulas that increase the file size massively.
- 4. Compress image
If there is a requirement of images or photos in the worksheets you should compress it before or after you import it to the worksheet.
- Remove pivot cache
If you use pivot tables in your excel sheet then you should remove the pivot cache because they consume space.
- You remove the source data and keep the pivot cache
- Or you can remove the pivot cache and keep the source data
- Delete data formatting
Data formatting – background color, borders, changed font styles etc
So if you remove these data formatting you will be able to save up some space.
- Remove conditional format
This also adds up to the size and removes it if it’s unwanted.
- Compress the file
Just like how you compress an image you will be able to compress a file.
Conclusion
These techniques do not magically reduce the size but a practical way to do so. Your data will determine how these Excel file size reduction approaches affect you. It could help some people shrink the size by 50%, while for some people reduce the size by 5%.
