7 Effective Methods to Reduce the Size of Your Excel Files
Eliminate Conditional Formatting
Conditional formatting is useful for highlighting specific data, but it can significantly increase the Excel file’s size. Consider removing conditional formatting once it’s no longer needed. Here’s how:
- Select cells with applied formatting on your sheet.
- Go to the Home tab > Conditional formatting > Clear Rules > Clear from Selected Cells.
- To remove conditional formatting from all cells, select “Clear Rules from Entire Sheet.”
Trim Each Worksheet to Its Used Range
Excel sheets can contain up to one million rows. Reducing file size involves eliminating empty rows and columns. Follow these steps:
- Select all unused rows and columns (Shortcut: Ctrl+Shift+Right/Down Arrow).
- Right-click and choose “Delete.”
Remove Unnecessary Formulas
Excel formulas are commonly used for data manipulation, but they are often left in the file even when no longer needed. To downsize your file and retain data, replace formulas with their values:
- Select the cells containing formulas (you can use Ctrl+A to select the entire sheet).
- Copy and Paste Special: Values.
This also removes hyperlinks that can contribute to file size.
Compress Pictures in Excel
Ideally, it’s best to avoid using pictures in Excel. However, if necessary, always compress them to reduce file size:
- Select the picture you want to reduce.
- Go to the Picture Format tab.
- Click on “Compress.”
- Consider unchecking the first box (“Apply only on this picture”) to compress all pictures in the Excel file.
- Optionally, choose your preferred resolution (typically, email resolution is recommended).
Clear Data Formatting
Formatting enhances data presentation but can add to the file size. If you encounter file loading issues, consider removing unnecessary formatting:
- Select all formatted rows, columns, and cells without critical data.
- Go to Home > Editing > Clear Format.
Delete Watches
Watches, often used by those dealing with large files and complex formulas, can impact spreadsheet performance. After their use, it’s advisable to remove watches from your files:
- Go to the Formula tab > Watch Window.
- Select all watches and click “Delete.”
Eliminate Hidden Data (Sheets, Rows, and Columns)
Important: Apply this step as the final optimization, as it may affect formulas and cannot be undone. Create a backup copy before proceeding.
Let’s start with hidden sheets:
- Right-click on a sheet.
- Select “Unhide.”
- Repeat these steps for all sheets.
Now, let’s address hidden rows and columns:
- Select your entire sheet (Ctrl+A).
- Right-click on the rows/columns and choose “Unhide.”
With all hidden data revealed, you can confidently delete unnecessary sheets, columns, and rows.