You have saved your Excel file and it is suddenly very big? Dozens of Megabyte although it was supposed to be rather small? In this article you learn how to find out what is causing large file sizes of Excel workbooks and how to fix it.
Check which sheet causes the large file size!
Method 1: Conveniently check with an Excel add-in why the file is very big
The fastest method of checking which worksheet causes the workbook to be very big is with Professor Excel Tools.
- Open the “Reduce File” feature on the Professor Excel ribbon.
- Click on “Check size of each sheet”. Lean back until the checking is done. You can now see for all worksheets, how many Kilobyte they cause.
The next step would be to reduce the size of these worksheets. Professor Excel Tools can next try the reduce the size for the two most common problems: Unnecessary formatting and pictures.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Method 2: Copy sheets or groups of sheets to new file
If you don’t use Professor Excel Tools, you could still do the same steps manually. Copy each worksheet into a new workbook and check the file size:
- Right-click on the sheet name on the bottom of the screen.
- Click on “Move or Copy…”
- Select “(new book)” and set the checkmark at “Create a copy”.
- Save the new workbook on your hard drive and check the size.
You can also speed that up by copying groups of worksheets into a new workbook.
Possible reasons, why the Excel file is big
After you have figured out, which worksheet causes large workbook sizes, you should look for the most common reasons. These are:
Reason 1: Formatting needs a lot of disk space
Description: In some cases, when you apply formatting to an entire column or row, Excel needs a lot of space. Usually, Excel is smart enough not to save the formatting for each cell separately, but sometimes it doesn’t work.
Solution: Clear all formatting below and to the right of the cells with content.
Our Excel add-in Professor Excel Tools can help you with that.
Reason 2: Pictures and other object increase file size
Description: Images can increase your Excel file significantly. It depends of the format, size, compression and number of pictures, but this problem is relatively simple to solve.
Solution: You could either delete or compress the images in your Excel file.
For deleting all pictures, first select the pictures and then press Del on the keyboard. If you want to delete all pictures in the whole workbook, you can use Professor Excel Tools: Click on Reduce File on the Professor Excel ribbon and then set the checkmarks at “Reduce size of pictures” and “Delete all pictures”.
For compressing pictures, select one image in your workbook. Then activate the Format ribbon which is now available (Mac: “Picture Format”). Click on “Compress Pictures” so that a new window opens for defining the compression settings. Follow the steps on the screen.
Reason 3: A lot of data can cause big Excel files
Description: A lot of data can also increase the file size. This one is actually tricky to solve.
Solution: Are you sure that you need all the (raw) data? Is there duplicate data? Is there data on hidden or very hidden sheets. Whenever you don’t necessarily need the data for your workbook to function, you can consider deleting it. Also splitting the workbook in separate files (one for importing data, one for data manipulation, etc.) could be a solution.
More reasons
For more reasons for large Excel files and the fixes, please refer to this article. In this article, I have also measured the potential reduction.
Image by Sasin Tipchai from Pixabay
