Lesson#149: 11 ways to reduce Excel file size. How to reduce excel file size?

Hello and welcome to my blog Excelabcd. Today in this post we are going to have a lesson on How to reduce excel file size. I shall discuss 11 ways to reduce Excel file size. Sometimes we have to work in huge excel files which contain many sheets and many data. But if the excel file is too heavy in size then it becomes very annoying and frustrating to work with. It may take too much time to execute a formula or function or too much time to move and save. Unnecessary size gaining may happen in files with huge data or old files which has been copied and modified a thousand times. So I have come up with some solutions about How to reduce excel file size in this post. Here are the tips.

Remove unnecessary Conditional Formatting

Conditional Formatting is too good to highlight specified data but it also increases excel file size. So it is recommended to keep conditional formatting on those cells where absolutely necessary. You should remove Conditional Formatting where it is not necessary.

Select those cells from where you need to remove Conditional Formatting then go to Home> Conditional Formatting> Clear Rules> Clear Rules from Selected Cells.

Remove unnecessary Formulas

Formulas increase file size and when you put thousands of formulas in one worksheet then the file size has to be increased significantly. If possible then just paste values instead of formulas it may reduce file size.

Clear unnecessary data and formats beyond the used range in the worksheet.

Don’t put your data here and there in the whole worksheet. Specify the used range worksheet. To be sure click and select the next column of your used range then press Ctrl+Shift+→ and then go to Home> Clear> Clear All

Do the same thing for the rows. Select the next row below the using area and press Ctrl+Shift+↓ and then go to Home> Clear> Clear All

It will clear all unnecessary formats and data beyond your used range in the worksheet.

Delete Watches

When you have a large workbook with many sheets and formulas you can use the Watch feature of Excel. But when these become too many in number they may increase file size heavily. So it will always be a good practice to delete watches after using them. To delete them you have to go to Formula tab > Watch Window select them all and Delete Watch.

Eliminate unnecessary hidden columns, rows, and sheets.

All unnecessary sheets, rows, or columns which are hidden should be deleted.

Right-click on a sheet and select Unhide and then it will open a list of hidden sheets. Click on every sheet and then OK. delete those sheets if they are not necessary.

For unhiding all hidden rows and columns at once you should click on the sheet and press Ctrl+A. Then right-click on any cell and click on Unhide. Delete those hidden rows or columns if they are not necessary.

Compress pictures in Excel

If you have inserted pictures in your worksheet then it is recommended to compress them to reduce file size. To do this you have to select pictures.

Then click on the Picture Tools tab and click on Compress Pictures.

Then I shall recommend choosing the E-mail (96 PPI) option and clicking OK.

Remove hidden objects

If your sheet is many times copied and used for a long time with many users there may be a chance of having many hidden objects which can increase file size heavily. To remove these objects you need to press Ctrl+G

Click on Special then choose Objects and click OK. Wait for a few seconds when the objects would appear on worksheets just press delete.

Delete unused names containing broken links

Some files which have been copied too many times by many users may contain names with broken links. Go to Formulas> Names Manager or simply press Ctrl+F3

Click on Filter and click on Names with Errors and then select all the names and click on Delete.

Remove pivot cache

Excel automatically creates a Pivot Cache that contains a copy of the original data source when a Pivot Table is inserted. Any changes made on the Pivot Table are not directly connected to the source data but to the Pivot Cache instead. Removing these duplicated data before you save the file may help to decrease the file size.

You can delete the Pivot Cache while keeping the source data. It needs to uncheck Save source data with the file under the Data tab in PivotTable Options. You can check to Refresh the data when opening the file under the same tab to allow Excel to automatically general a Pivot Cache when the file is open or refresh the pivot table manually.

Save in Binary format (.xlsb)

When you save an Excel sheet in Binary format (.xlsb) then it can reduce file size drastically.

Compress a file

Last one but out of the box. You should compress an Excel file in .rar or .zip mode when sending someone through e-mail. This may help reduce file size.

Now you have known 11 ways to reduce Excel file size.