

Contents
The important question: What part of your Excel workbook causes the large file size. Basically, there are several options:
For each of these types, the approach of reducing the file size is different. So how do you find out, which type(s) might be the case for you?
Either, you just go ahead and try the following methods. Or – and that’s the recommended way – you do it more systematically. We recommend you to check the file size of each worksheet. Then you just start with the largest worksheet and depending on the type, you just do the specific methods.
How do you get to know the file size of each worksheet? There is no simple built-in way in Excel. But our Excel add-in helps (30-day free trial without sign-up): Just press the “Reduce File Size” button and then click on “Check size of each worksheet”.
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
Steps for compressing pictures in Excel workbooks
One common reason for large Excel files are pictures within the workbook. Fortunately Excel offers a function for compressing images: The resolution will be decreased and the invisible areas can be removed. Just follow these steps (the numbers are corresponding to the picture on the right hand side):
Delete formatted but otherwise unused cell ranges.
When you apply a cell format to whole rows or columns, Excel often needs a lot of disk space. You can try to delete all the unused areas below and on the right hand side of your data:
Repeat these steps on every worksheet, also for the columns. Save the workbook afterwards for checking, if the file size is reduced.
This method could reduce the file size – depending on your formatting and style – by max. 94%.
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Save files as .zip files.
If your Excel workbook contains large amounts of data, zipping the whole file after you are done editing often helps a lot. Basically, Excel files are zip files already (you can try it by just replacing .xlsx by .zip), but surprisingly, zipping the files again helps.
This method could reduce the file size – again depending on your file – by app. 15%.
Depending on how urgent you need to reduce the file size of your workbook you might consider splitting the file: Divide the workbook into two or more files, for example the first workbook for all the inputs and one sheet for consolidating the inputs. The second workbook has the actual calculations based on just the consolidation sheet from workbook 1.
Reduce the data of Pivot Tables.
Pivot Tables sometimes consume a lot of space: They use a cache in which the data is stored. There are basically three options to reduce the size of Pivot Tables:
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Delete unused content, e.g. worksheets.
This is the most obvious step so you’ve probably considered it already: Do you have any unused contents which you can delete? Are there hidden sheets which are not needed any more? If there is anything you don’t need any longer (if you are not sure, save a backup copy of the file), please go forward and remove the sheets or cell ranges.
In case of worksheets, follow these steps:
Get to know the file size of each worksheet.
Probably the most comfortable way of reducing the file size of your Excel workbook is with the Excel add-in ‘Professor Excel Tools‘. It provides the following functionalities:
Please use the free trial version for checking if it works for you (download button below).
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
This is one of the easiest methods of reducing the file size and at the same time the opening time of Excel workbooks: Save your Excel file in the XLSB file format. Besides these two obvious advantages, the XLSB format has some (usually minor) disadvantages though: The main disadvantage of XLSB files is that they can contain VBA macros (for more information please refer to this article: XLSX, XLSM or XLSB: Which File Type Is the Best for You?)
Changing the file format of your Excel file is very simple:
Reduction of file size when saving a XLSX file in the XLSB format.
The effect: You can easily reduce the file size by 70%. A quick test with a file containing mostly formulas and hard values showed a reduction of the file size between 60% and 70%. As you can see in the figure on the right side, it’s especially worth changing the file format for XLSX files larger than 10 MB. On the horizontal x-axis, you have the file size of a XLSX file. If you change the file format to XLSB, the file size reduces significantly, shown on the vertical y-axis.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 1
Swapna
Hi Henrik,
My excel has table in 2013 format saved in .xlsb format. Files has nearly 24 sheets. Of which 12 sheets have 1 Pivot table each and others are paste of pivot table values. Since we need to refer pivot table data in other tables/sheets.
Now after applying all the suggested methods. My file size is still 7MB+ and I want to get it reduced below 5MB.
Users are facing excel crash issues due to this.
Could you please suggest any other alternative methods.
Vielen Dank,
Swapna