How to Reduce the Size of an Excel File
To reduce the size of an Excel file, compress images, delete unused sheets and blank rows to reset the used range, clear formatting on whole columns, remove unused styles and named ranges, and save the workbook as a binary .xlsb file. After cleaning up, save the file again so Excel discards the cached old data and rewrites a smaller file.
Welcome to our blog post on how to reduce the size of an Excel file. If you find yourself struggling with a large Excel file that takes up too much space on your computer, then this post is for you. As an expert in Microsoft Excel, we understand the importance of optimizing your spreadsheets to improve performance and functionality. In this post, we will guide you through various methods that you can use to compress and reduce the size of your Excel files, making them more manageable and easier to share with others.
Introduction
Excel is a powerful tool for organizing, manipulating, and visualizing data. However, as you add more data to your spreadsheets, the file size can quickly grow out of control. Large Excel files can be hard to work with and take up valuable space on your computer. In this blog post, we will discuss multiple ways to reduce the size of an Excel file so that you can maintain optimal performance while keeping your files organized.
Compress Images
One of the common reasons for large Excel file sizes is high-resolution images. Fortunately, it’s easy to fix by compressing images in Excel. To compress images, right-click on the image, select Format Picture > Picture, and then select the “Compress…” button. A new dialog box will appear, allowing you to choose between different compression options. For the best results, select the “Delete cropped areas of pictures” and “Apply only to this picture” checkboxes. Now, click on the “OK” button to see the file size decrease. Because images are often the single biggest culprit, our dedicated guide to compressing an Excel file walks through the picture-compression dialog in more detail.
Delete Unnecessary Sheets and Rows
If your Excel file has multiple sheets, consider removing any unnecessary sheets that don’t add value to your workbook. To delete a sheet, right-click on the sheet’s tab and select “Delete.” Also, removing any unused rows at the end of your data can help lower your file size. To remove a row, right-click on the row number and select “Delete.” The same logic applies to columns — clearing out unused columns to reduce the size can shrink a bloated used range dramatically. After deleting the blank rows or columns, save the workbook so Excel resets the used range and rewrites a smaller file.
Remove Formatting
As data is added to an Excel sheet, the formatting for each cell can add up and lead to a larger file size. Removing the unnecessary formatting can help reduce the file size. To remove formatting, click on a cell to select it, go to Home > Editing > Clear > Clear Formats.
Convert Objects to Data
If your Excel file has charts, pivot tables, or other complex objects, consider converting them to values. To convert a chart to values, select the chart, copy it, and then paste it as values. To paste as values, right-click on the cell where you want to paste and select “Paste Special” > “Values.”
Save as Binary File
Saving an Excel file as a binary file can help reduce the file size. To save your workbook as a binary file, click on File > Save As > Browse, and then under the “Save as type” drop-down menu, select “Excel Binary Workbook (* .xlsb).” This will create a new file with the “.xlsb” extension, which is a binary file format that can drastically reduce your file size. If you later need to share that file, see how to open an .xlsb file so collaborators aren’t caught out by the format.
Using these methods, you can significantly reduce the size of your Excel files, which will help eliminate performance issues and make it easier to share them with others. Always remember to save a backup copy of your file before making any changes in case you need to revert back to the original version.
Use Conditional Formatting Sparingly
Conditional formatting can be an excellent tool for highlighting specific data but can also increase the file size by a significant amount, especially if you are using multiple rules. To reduce the file size, use conditional formatting sparingly and avoid overlaying many rules at once. Consider removing any unnecessary rules or consolidating multiple rules into one for more efficient data highlighting.
Remove Unnecessary Add-ins
Microsoft Excel enables users to customize their workbooks through add-ins. However, some add-ins can be memory-intensive and increase file size. To reduce the file size of your Excel file, remove any unnecessary add-ins that you don’t need or use. To remove an add-in, click on File > Options > Add-ins, and then click on “Manage” under “Excel Add-ins” and “COM Add-ins.” Finally, uncheck the add-in that you want to remove and click “OK.”
Use External References Cautiously
Linking Excel files with external references can increase file size, especially if you’re linking to multiple workbooks. To reduce file size, avoid using external references or use them sparingly. If possible, copy data from external workbooks and paste it into your current workbook instead of using an external link.
Use Table Instead of Range
Excel tables are a powerful tool for organizing and analyzing data. They also have a small file size compared to ranges, reducing the overall file size of your workbook. Using tables instead of ranges allows you to add formulas, filter data, and automatically expand your table as new data is added. To convert data to a table, select the data range, click on “Insert” > “Table,” and choose your table style.
Consider Splitting Large Files
If you’re still struggling with a large Excel file after trying all these methods, consider splitting the file into smaller, individual workbooks. You can split the file by separating worksheets into different files, or splitting data into separate files based on year, region, or any other category. This can make the files smaller, more manageable, and easier to work with. For more focused walkthroughs, see our guides on how to make an Excel file smaller and reduce Excel file size.
If a bloated file is also making Excel sluggish, file size and performance often go hand in hand — our tips for when Excel is slow and how to speed up a slow Excel workbook tackle the lag directly.
Closing Thoughts
Reducing the size of an Excel file can have a significant impact on its performance and usability. By following these tips and tricks, you can keep your files organized, efficient, and enhance your work experience with Excel. Remember to save a backup copy of your file before making any changes and check the file’s compatibility with external data sources.
FAQs
Below are some frequently asked questions that people have about reducing the size of an Excel file:
1. What is causing my Excel file to be too large?
Several factors can cause your Excel file to be too large, including high-resolution images, excessive formatting, unused sheets, external links, and complex objects like charts and pivot tables.
2. Can I compress my images after I’ve already inserted them into my Excel file?
Yes, you can compress high-resolution images in your Excel file even after you’ve inserted them. Right-click on the image, select Format Picture > Picture, and then select the “Compress…” button to compress the image and reduce the file size.
3. Will removing formatting affect my calculations and data?
No, removing formatting won’t affect your calculations or data in any way. Formatting only affects how data is visualized and displayed in your Excel sheet.
4. Can I reduce the size of my Excel file without losing any data?
Yes, you can reduce the size of your Excel file without losing any data. You can remove unnecessary objects, unused sheets, and formatting, as well as compress images and use tables instead of ranges to reduce the file size. However, remember to save a backup copy of your file before making any changes in case you need to revert back to the original version.
5. Can I convert my Excel binary file to a regular Excel file?
Yes, you can convert your Excel binary file back to a regular Excel file by saving the workbook as a “.xlsx” file extension. The data and formatting carry over intact — .xlsx and .xlsb store the same content — so the conversion is safe. The only practical difference is file size and open/save speed, so keep the .xlsb copy when size matters and save an .xlsx copy whenever you need maximum compatibility.
6. Why does my file stay large even after deleting rows?
Excel tracks a “used range,” and deleting the contents of cells does not always shrink it — the empty rows or columns are still counted. Select the truly blank rows or columns below and to the right of your data, delete the entire rows/columns (not just the cell contents), then save the file. Saving forces Excel to recalculate and reset the used range, which is when the size finally drops.
7. How do I clear a pivot table cache to reduce file size?
Every PivotTable stores a hidden copy of its source data, called the pivot cache, which can double the effective size of that data. To shrink it, right-click the PivotTable, choose PivotTable Options, open the Data tab, and uncheck “Save source data with file” (optionally enable “Refresh data when opening the file”). Save the workbook afterward so the cached copy is discarded.
8. Do unused styles and named ranges make a file bigger?
Yes. Workbooks that have been copied between files for years often accumulate thousands of orphaned cell styles and broken named ranges, which inflate the file and can cause “too many cell formats” errors. Delete unused named ranges from Formulas > Name Manager, and clear unused styles with the Cell Styles gallery or by saving as .xlsx and reopening. This kind of cleanup is one of the quickest ways to make an Excel file smaller without touching your data.
