How to Deal with Heavy Excel Files?

There are various reasons why the size of an Excel file becomes too large, such as having high-resolution images, complex formulas, and more. This makes opening the Excel file slow.

In this article, Mytour will guide you on some ways to fix the problem of heavy Excel files.

1. Fixing Heavy Excel Files

Method 1: Delete Excel File Formats

While creating Excel spreadsheets, we apply various formats, which also cause heavy Excel file errors, leading to slower file openings, taking more time. The solution is to delete formats to reduce the size of the Excel file.

Follow the steps below:

Step 1: First, select the range of cells you want to clear the format of.
Step 2: Next, go to tab Home =>Editing Group =>Clear =>Clear Formats.

Method 2: Reduce the Size of Images Added in Excel Files

Follow the steps below to compress or change the resolution of images inserted in the Excel file to reduce size and fix overweight Excel file errors:

Step 1: Open the Excel file containing images or logos, then select the image to compress.
Step 2: Next, go to Picture Tools =>Tab Format, in the Adjust group, find and click Compress Pictures.

Note: If you cannot find Picture Tools => Tab Format, verify the image you have selected. Double-click on the image to select it and open the tab Format.

If you only see group Adjust, the Compress Pictures button may appear without a label.

To reduce the size of a specific image while preserving other images in the Excel file, tick the box Apply only to this picture.

If you uncheck Apply only to this picture, it will overwrite any previous modifications you made to other images in the Excel file.

Next, access Resolution, select the desired resolution, then click OK.

Method 3: Convert Excel file to binary format (XLSB)

By converting the Excel file to binary format to reduce its size. Follow the steps below:

Step 1: Click on the File tab => access the Save As option.

Step 2: Next, click on Browse button to change the file type.

Step 3: On the Save As dialog box, select the file type Excel Binary Workbook (.xlsb), then click Save.

Method 4: Delete unnecessary formulas

Complex formulas can affect the size of the Excel file. The solution is to remove unnecessary formulas from the Excel spreadsheet to reduce the size and fix the issue of the Excel file being too large.

- Follow the steps below to delete formulas in the Excel file:

Step 1: Open the Excel file, then press Ctrl + A to select the entire worksheet, then press F5 key.
Step 2: Open the Go To option, then click Special.
Step 3: Select Formulas and then click OK.

Next, select all cells containing formulas.

- After completion, follow the steps below to convert the selected formulas into values:

Step 1: Press Ctrl + C to copy the selected cells.
Step 2: Next, click on the Home tab, from the Clipboard group click Paste.
Step 3: Click on the Paste Value icon.

Now all cells containing formulas will be converted into values.

Method 5: Optimize the formulas used

After removing unwanted formulas, the next step is to optimize the formulas used. Follow the steps below:

- Avoid using unstable functions such as TODAY, NOW, INDIRECT, RAND, RANDBETWEEN, CELL, OFFSET, INFO.

- Replace nested IF functions with any other functions, including: REPT, INDEX & MATCH, SUMIF, or Boolean logic functions.

Method 6: Compress file to ZIP

Step 1: First, right-click on the Excel file you want to compress.

Step 2: Hover over the Send to option, then click Compressed (zipped) folder.

Method 7: Delete unused cells

To delete sheets, columns, and formatted cells, follow the steps below:

Bước 1: Đầu tiên chọn tất cả các hàng thừa trên file Excel của bạn.
Bước 2: Nhấn Ctrl + '-' để xóa các hàng.

Step 3: Then, click on the rubber button located at the top right corner of the Ribbon.

Then click on Clear All.

Repeat the same steps on other worksheets as well as on columns. Finally, save the Excel file and check if the file size has decreased.

Method 8: Reduce Condition Formatting in Excel File

To do this, select the entire Excel file by pressing Ctrl + A.

Next, go to Home =>Conditional Formatting =>Manage Rules.

Finally, select and delete any irrelevant roles and rules.

Method 9: Compress Excel File

This method differs from the ZIP compression method.

Step 1: To compress the Excel file, first locate your Excel file using Windows Explorer, then right-click on the file.
Step 2: Select Properties from the menu, then choose Advanced Button.
Step 3: Finally, check Compress contents to save disk space.

Method 10: Reduce Pivot Tables Size

There are 3 options to reduce Pivot Tables size:

- Option 1: Sometimes, Pivot Tables are created to inspect data or get an overview. If you don't need Pivot Tables in your Excel file anymore, removing unused Pivot Tables is advisable.
- Option 2: Instead of creating multiple Pivot Tables with the same source data, you can copy and use them where needed.
- Option 3: Avoid saving the source data of Pivot Tables in the same file. To do this, right-click on the Pivot Table then access Pivot Table Options. From the Data tab, uncheck the option Save source data with file.

Method 11: Check and remove unnecessary hidden cells

Hidden cells could also be culprits causing Excel files to become too heavy. If you discover unwanted components in your Excel file, it's necessary to unhide rows and columns on the Excel worksheet, then delete these cells.

The article above from Mytour just guided you through some ways to fix excessively heavy Excel files, reducing the size of the Excel file. Furthermore, if there are any doubts or questions needing answers, readers can leave their comments below the article such as Excel file not saving for instance ...