Reduce Excel file size to enhance your workbook’s performance and accessibility. By implementing strategies such as removing unused content, compressing images, and optimizing data storage, you can significantly decrease load times and improve efficiency. Streamlining your Excel files not only facilitates easier sharing and storage but also ensures smoother operation and quicker data processing. Embrace these techniques to maintain compact, efficient Excel documents, paving the way for more effective data management and analysis.
This Content Covers:
- What is the necessity of reducing Excel file
- How to Reduce Excel File size
- By removing Conditional Formatting
- By clearing Data Formatting
- By removing Unnecessary Formulas
- Converting to Binary Format
- Cropping the Worksheet to its used range
- By Compressing the File
- By compressing the Picture using the Picture tool Format tab
- Deleting Watches
- By Deleting Hidden Data
- Remove Pivot Cache
1.What is the necessity of reducing Excel file
The main reason for reducing an Excel file is that it takes longer to open or save since it is so sluggish to operate because of the large amount of data it contains. Reducing the size of our Excel file is really necessary to make it easier to use and to save some time.
2. How to Reduce Excel File size
There are a number of easy but effective ways you can use to reduce the size of your Excel file.
2.1 By removing Conditional Formatting
Step 1: Select Home tab>>Conditional Formatting.
Step 2: From the drop-down menu select Clear Rules>>Clear Rules from Entire Sheet/Clear Rules from Selected Cells.
2.2 By clearing Data Formatting
Step 1: Select Home tab>>Clear>>Clear Format.
2.3 By removing Unnecessary Formulas
Step 1: Select the cells which contain data or select the entire worksheet using CTRL+A command and right-click. Click on Paste Special.
Step 2: From the Paste special dialogue box select Values and None as shown below in the picture and hit OK.
2.4 Converting to Binary Format
Step 1: Select File>>Save As>>Browse.
Step 2: From the Save as Type box change the file type to Excel Binary Workbook(*.xlsb). Converting to this Binary Format will reduce your file size.
2.5 Cropping the Worksheet to its used range
Step 1: Select the unused cells and right-click. Select Delete.
Step 2: From the Delete dialogue box select Entire Row and hit OK. Now repeat the process again and select the Entire column and hit OK.
2.6 By Compressing the File
Step 1: Right-click on your file then select Send to>>Compressed(zipped) folder.
Step 2: A compressed file has been created which is also reduced in size.
2.7 By compressing the Picture using the Picture tool Format tab
Step 1: Double-click on the picture and it will activate the Picture Format tab under Picture Tools. Select Compress Pictures.
Step 2: From the Compress Pictures dialogue box uncheck Apply only to this picture if you want to compress all the pictures in your worksheet at once. You can also choose any resolution (E-mail is suggested) and hit OK.
2.8 Deleting Watches
Step 1: Select Formulas>>Watch Window.
Step 2: From the Watch Window dialogue box select all the watches and click on Delete Watch. All the selected watches will be deleted.
2.9 By Deleting Hidden Data
Step 1: To view the hidden data first, right click on your worksheet and select Unhide. All the hidden data will show now and you can delete what u don’t need.
2.10 Remove Pivot Cache
Step 1: Click on any cell of your Pivot table which will activate the Pivot Table Analyze. Now select Options.
Step 2: From the Pivot Table Options dialogue box select the Data tab, uncheck Save source data with file and check Refresh data when opening the file. Hit OK. The pivot cache will be removed and the file size reduced.
Application of Reduce Excel file size
- Remove Unused Cells: Trim excess rows and columns beyond your data range to decrease file size and improve workbook performance.
- Compress Images: Reduce the resolution or size of images embedded in the workbook to lower the overall file size without significantly impacting visual quality.
- Limit Use of Volatile Functions: Minimize the use of volatile functions like INDIRECT, TODAY, RAND, etc., as they increase recalculation time and file size.
- Convert to Binary Format: Save your Excel file in a binary format (.xlsb) instead of the standard format to compress the file size without losing data or functionality.
- Clear Formatting: Remove unnecessary cell formatting across the workbook to decrease the file size and enhance simplicity and readability.
- Remove Pivot Table Caches and Unused Data: Delete any old or unused pivot table caches and remove unnecessary sheets or data that are not being used for analysis.
You may be interested:
