How to reduce Excel File size
Are you thinking of increasing or decreasing the Excel file size? In this article, we will look into some of the ways to reduce the excel file size. The size of the file may become large because of its excess complexity and the number of data that is being added to it and hence it makes the app run slow. To circulate large files that are more than 50 MB to your colleagues can also be a problem. Learning to reduce the file size is important and it can be done in several ways. Do visit https://lio.io
The ways to reduce the Excel file size
1) Remove the unwanted data, worksheets, and formulas
The amount of data and the number of worksheets that are included in excel determines the size of the file. By deleting unnecessary data and worksheets, the file size can be reduced. Converting the unnecessary formulas to values aid in reducing the file size. Copy the selected cell and then paste them as values in the paste options tab. You can also use the shortcut key Ctrl+Alt+V+V
2) Remove formatting
Formatting includes applying borders, highlighting, coloring, etc. Conditional formatting will improve the quality of the excel worksheet but at the same time will inflate the size of the file. All the formatting can be cleared by using the clear format button that is found in the editing section under the home tab. The conditional formatting can be cleared by selecting clear rules that are found in the style section.
3) Removing the Pivot Cache
All the duplicate files from the original data source are stored in the excel pivot cache when inserting the pivot table. The duplicate data can be removed before it is saved to reduce the file size. The pivot cache can be deleted and the source data can be retained. The save source data with the file box can be unchecked. It is found in the Data tab under PivotTable Options.
4) Save the file in binary format
All the excel files are generally saved in XML format which is .xlsm or .xlsx. Choosing .xlsb binary format while you save the file helps in reducing the file size. But, there are certain limitations like the power query is unable to read the data and it may end up malfunctioning or some threats.
5) Compressing the file
For easy and quick sharing, the documents can be compressed to a zip file which can help in reducing the size of the file by 10 to 15 %. Right-click the excel file and then choose the compressed (zipped) folder in the ‘Send to’ tab.