The trouble is, the more data you put into your spreadsheet, the bigger the file. If your Excel file becomes too large, it may be impractical to easily email to colleagues or clients. It can also slow down in Excel and stop working properly. If you’re facing this problem, here are some effective tips on how to reduce Excel file size.
How to Reduce File Size in Excel by Deleting Worksheets
Using multiple worksheets in Excel makes it easier to structure your spreadsheets, but the more worksheets you use, the larger your file size will be. Removing unneeded worksheets can help to bring the file size down. To delete worksheets in Excel:
Ensure that the sheet you are intending to delete does not contain any data that is needed or referenced by other sheets.Right-click on the sheet tab at the bottom of the screen. Select Delete.
The sheet will be deleted—repeat the steps for any other sheets you want to delete.
Clear Pivot Table Caches
Pivot tables in Excel are a great way to analyze your data, but they can have a significant impact on the size of your Excel files. That’s because, by default, Excel will save your pivot table source data cache along with your file. It’s possible to change this setting, however, and have the data cache refresh when the spreadsheet is opened. To stop the pivot cache from saving in Excel:
Select any cell of your pivot table.Open the Pivot Table Analyze menu. Select Options. Select the Data tab. Ensure that Save Source Data With File is unchecked. Click OK to confirm.
Save Formulas as Values
Excel formulas allow you to do almost anything you want in Excel using the in-built functions. Formulas take up more space than values, however, so if you have a lot of formulas in your spreadsheet that have already been calculated, you might want to consider converting these formulas to values. To save a formula as a value in Excel:
Select the containing your formula.Use the keyboard shortcut Ctrl+C on Windows or Cmd+C on Mac to copy the cell. Alternatively, you can right-click on it and select Copy. Right-click on the cell and select Paste Special > Values.
Your formula will be replaced with the value of its result.
Remove or Compress Pictures
Pictures aren’t something that are commonly used in Excel spreadsheets, but if you have used some in your document, then these will increase your file size. You can either remove these pictures or compress them. To compress a picture in Excel:
Select the picture in your spreadsheet.The Picture Format tab will appear in the ribbon—select this menu. Press the Compress Pictures icon. Select a resolution—the lower the resolution, the smaller your Excel file size will be. Ensure that Delete Cropped Areas of Pictures is selected. If you want to apply the same compression to all of your pictures, make sure that the Apply Only to This Picture checkbox isn’t selected. Click OK to compress your pictures.
Reduce Excel File Size by Saving in XLSB Format
Another way to reduce the size of your Excel file is to save it in binary format. The default format is XML, which works better with other applications, but if you’re only planning to use your file in Excel, then saving it as a .XLSB file can help to reduce the file size. To save an Excel file in .XLSB format:
Press File > Save As.Select a location to save your file.From the list of file formats, select Excel Binary Workbook. Press Save to confirm.
Solving Excel Problems
If you’re wondering how to reduce Excel file size, you can usually resolve the problem by following the steps above. Excel is a powerful application, but when things go wrong, it can be frustrating to fix. If you’re having problems, you may want to try and stop Excel from crashing. If you’re getting notifications about circular reference errors, Excel can help you to track them down. Comment
Δ
