Ms excel reduce file size

The more data points you have in your workbook, the larger your file size will be. Removing unused data will reduce your file size. Select Discard editing data. This option removes stored data that's used to restore the image to its original state after it's been edited. Note that if you discard the editing data, you won't be able to restore the image. In the Default resolution list, select a resolution of ppi or lower. In most cases, you won't need the resolution to be higher than that.

Select a picture in your document. The Picture Format tab appears on the ribbon. To compress all the pictures in the file, clear Apply only to this picture. If this option is selected the changes you make here will only affect the selected picture. Select Delete cropped areas of pictures. This option removes the cropped picture data, but note that you won't be able to restore it. Select Use default resolution. If your spreadsheet contains a pivot table, you can reduce the file size by not saving the pivot table source data cache with the file and instead having the data cache refresh when you open the spreadsheet.

In the PivotTable Options dialog box, select the Data tab, and do the following:. Data formatting such as applying a background color, or adding borders, or changing font style can all add to the file size. Below are the sizes of two Excel workbooks, one with 2. You can see the difference between the two files is not that huge although it does make an impact when you have a lot of data.

Removing it can save you some disk space. Just like regular data formatting, conditional formatting also adds to the file size. However, based on my tests, I noticed that the difference is less unless you have multiple rules applied to multiple sheets with huge datasets. Conditional formatting is volatile and whenever there is any change in the worksheet, it recalculates. This can lead to a slower workbook. While the above methods will help you reduce Excel file size, here are some additional tips to improve the performance of Excel Workbooks:.

What else I could add to this tutorial to make it more useful? Please let me know in the comments section. Awesome content, really helpful. You covered all the points with the most precise knowledge for a layman to know. Also, I would like you to please make some content for using power queries to connect data in cell pivot tables. Hi, I reduced my file from 40 to 1,3 MB, by deleting all formating from row to the bottom of the document.

If multiple pivot tables are used with the same root data rather than creating a new pivot table, copy an existing table and change the pivot selections. Each new table creates a new pivot cache — increasing the file size, copying an existing table enables the same cahe to be used multiple times, and also holds an advantage of updating one pivot updates all. No tips were helping and I already wanted to leave the page… when I reached your mentioning of pivots. What a nonsense to save the complete data source with every pivot… reduced my excel from KB to KB, so a reduction of Thanks a lot!

Awesome post. Changing the file format to binary reduced my file size from 40 mb to 3 mb. That really made my day! Thank you. Sir I really appreciate to this articals about reducing excel sheet i applied this today thanks so much guidence us. Thanks so much for your article.

You may want to check it out. Thanks again for putting it up. Does anyone have a reliable experience working with XLSB files over a period of months vs. And with file sizes larger than the 5MB threshold mentioned. I found that using Tables, saves space when there are formulas in the columns. While formula calculation is set to Manual, you can force a recalculation of the entire workbook by pressing the F9 key. Now, you might notice some strange behaviour when Manual is selected.

If you edit a cell containing a formula and press Enter, that formula will calculate as usual. If you then copy that formula to other cells, the previous result will be displayed temporarily! And if you want a single formula to be updated, you can edit the cell F2 , hit Enter, and that formula will calculate.

Do you have issues with slow Excel files? Also in the Formulas menu, you can click on the Watch Window option to open up a small dialog box which appears in front of your Excel workbook. The Watch Window contains a list of cells that you specifically want to keep an eye on.

For example, you might be working on a detailed budget which has workings across multiple sheets. All of those sheets feed values into a high-level budget summary sheet, and you want to know the final profit or loss while working on other sheets. In the Watch Window, click on the Add Watch button and select the cell you want to keep a close eye on.

The value will always be visible, regardless of which sheet is active. Apart from shrinking file size directly, you can use the below tips to optimise your Excel formulas so they run faster. Seven functions in Excel are known as volatile — ie, they calculate every time any cell is updated in the workbook. Microsoft lists them here on their help page. Minimising the use of these functions in your workbook will cut down on unnecessary calculation time. It may seem slightly counter-intuitive, but using PivotTables instead of a series of formulas is a very effective way to show your results.

PivotTables are designed by Microsoft to operate efficiently in Excel, so use them if it makes sense!

Similarly, putting your data in a Table is also very efficient. Any formulas calculated fields within the Table contribute less to file size compared to a series of formulas on a normal data range. Want to learn more about Excel Tables? So instead of having:. For this tip, you want to reduce the number of cell references that your formulas have.

In this case, the total number of cell references is two hundred, despite the number of unique cells only being two. So if you have a large number of formulas that perform the same calculation, consider breaking them up in your sheet to speed up the calculation. Learn more about Excel formulas here.

Apart from updating the file contents directly in Excel, you can try this other method to reduce file size. Because of the way. So there you have it.

And even better, your files should spend less time to open, and formulas should take less time to calculate! Have any of the above tips helped you out, or do you still have issues cutting down on file size? Let us know in the comments! Hi, thanks for your tips above. The method that i found most use useful is to save the Excel Sheet as a Text tab delimited , close it. As a percentage, approximately how much would the file size shrink after you import the data back into Excel?

I tried this Lynn Sandy suggestion and it worked great. Turned a file that was 18, KB into 97 KB. You can edit that when converting if needed. I had already tried most of the items listed on the article post already to no avail.

In one of my Files, the File Size went from Out of curiosity, did your data have a lot of formatting, borders or highlighting? That information may have been contributing to the initial large file size. Just go for PIVOT and take data from there selecting any of the field double click on the numbers you will get a sheet open copy and paste in any other excel sheet it will redue like 9 mb file into some KB. Hi Chris, I have a 3 sheets containing the inquiry, order, billing details of customers for months which runs into rows and 30 — 40 columns.

I have this data in tables. The file size is 11 mb due to pivot tables and other formulae. How can i reduce the file size without affecting the formulae. I tried saving the file in binary format but file reduced to 9 mb. Otherwise, you might want to consider splitting the data and the PivotTables into separate Excel files.

The PivotTables can still refer to data that is in another file, but you will need to have both files open to make it update. If I delete the rows from the bottom of my data row 84 down to row 1,, and press Ctrl and End again, I still end up in cell T! For some reason, this is not working for me…I have Office Microsoft Office on a 64bit desktop. I have a 44 MB Application that I am trying to reduce in size. I have selected the bottom portion of a worksheets, as described here.

At first I deleted, then I cleared contents, then I cleared all… My blank cell range keeps enlarging!!! It started at row and I am now at row What can I do? No conditional formatting, no formulas going to the bottom of the columns, etc. The sheet is 31MB as an. Only by turning off calc and using F9 will it function.

It was working fine for weeks, then overnight after no significant additions, it became slow as molasses. Thanks very much! My excel work book file was MB. A great combination of methods to get the file size down. I wonder what the size would be if you saved the Excel Workbook as a binary file.

This is excellent! Thanks for the article.