Can you compact an excel 2007 file

And with file sizes larger than the 5MB threshold mentioned. I found that using Tables, saves space when there are formulas in the columns. I had a large range, something like 25, rows x columns, with the bulk of columns containing formulas. This reduced my xlsm file from 18Mb to 5Mb and going to xlsb dropped it further. I suspect it only stores the formula once per column for a table, rather for every row!

Compress the file ZIP it may not really save you much. Best Excel Shortcuts. Conditional Formatting. Creating a Pivot Table. Excel Tables. Creating a Drop Down List. Recording a Macro. VBA Loops. Privacy Policy Sitemap.

Note: The impact of these techniques to reduce Excel file size would depend on your data. This Tutorial Covers:. NOTE : Volatile formulas can really bloat your file size. So if you have volatile formulas that you can replace with non-volatile formulas, or can convert these into values, it will help you reduce the file size. Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster.

Thanks a lot. Super cool! It worked for me. Hi Sumit, Thanks so much for your article. Delete cropped areas of pictures option helps reduce image size as well. Another important point is to select the E-mail option for the lowest ppi value, as well as picture size. Graphics are great way to improve visual presentation of spreadsheets. They get viewer's focus faster than anything else. However, but of course, this can come at a cost.

Extensive use of graphics increases the file size. Therefore, this is one of the items you may want to check when file size is an issue. Try to remove unnecessary graphics or combine them into fewer diagrams.

Cell formatting needs disk space too. This can become problematic especially when used in large ranges. Try selecting 50, or , rows and see if that will work. Then i stumbled across Name Manager and saw all the cell names are still stored and copied over from other workbooks, once deleted, i saved and closed it before reopening the file again.

The first suggestion totally worked. Anyway, thanks! In recent years Excel has added a ton of options, including but not limited to the extended of rows, Adding data to a data model to get special functions in pivot tables. I find using data models is a huge size factor, but it is the only way to get some of the functions.

And I now have a MB file that is impossible to share almost everywhere. We still need more answers. I am on Office and ever since I switched to this, I have a spreadsheet that keeps growing in size until it will no longer open. Does anyone know what could be causing this? This has happened 4 times. My file has 26 tabs is nothing other than downloaded data from Management Report and only one tab has formulas which are nothing more than summary formulas — nothing fancy.

Any ideas anyone? This is a great post, thank you! I had forgotten you can unzip spreadsheets, and the reminder was exactly what I needed to track down a problem. Sheets can be hidden, and they can also be very hidden. The unhide option on the right-click menu will be greyed out, exactly as if there were no hidden sheets. The Visible property is the last one in the list.

The thing that reduced my file to less than 1 mb is when i remove the links in the file. Hi Lin, did you check the file size of data source? As soon as you open the file in Excel, it will be decompressed and your Excel instance will hog the same amount of memory!

Thank you very much. I found out that some illiterate person managed to put data into Line 10,,, making the file size 56 Mb. Your tips reduced the file size to 2. Just by deleting the used range i was able to cut the file size from 68MB to KB.

Applying the delete empty rows reduced this to 79kb!!!! Thanks a ton for helping me reduce my macro-enabled worksheet size from a whopping MB to 8. Tip 1 helped me solve the issue. I wonder whether you have written any VBA for performing the same.

The macro in my sheet opens another workbook and copies multiple sheets, performs engineering calculations and presents the results in printable format.

Another tip for those of you who may have embedded images in your spreadsheets e. You can also set your target resolution. I am using MS Office , so it should be the latest one.

Interesting thing is that I have added a dummy value in the last cell XFD of my excel sheet. The step was the saver for me as I did everything else. Thanks a lot for publishing it, great work. Wow, thanks a lot! Oh my goodness — thank you so much!! All that blank space that Excel thought was used. So weird! But took my file size from 21mb to 2mb. Thank you!!! This post definitely helped me out! Almost impossible to transfer to other devices outside of cloud storage.

I even deleted worksheets that were over ten years old, and still the file was way too big. Totally awesome and quick fix. The empty rows tip is amazing. But what I would like to know is why does a sheet suddenly has 1 million rows instead of the real number of rows. What causes that problem? The bonus option did it for me!!! 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. 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. One common reason for large Excel files are pictures within the workbook.