How to Open Large Excel Files?

So many users face lags and crashes while opening large Excel files. But files normally get larger when the data and information grow in size within the workbook. Although larger files tend to crash most while opening, there are more underlying issues for crashes than just the file size. In this article, we will focus on eliminating these issues so that we can open large Excel files without crashing.

1. Removing Excel Add-ins

COM Add-ins are supplementary programs for Microsoft Excel that expand the capabilities of the program and help us enhance our experience with Excel. However, these COM add-ins can also slow Excel down by a bit. If you have a lot of add-ins added to your program, it may cause serious lags even while running simple processes, especially if your system is on the lower spectrum of specifications. Additionally, third-party add-ins can cause issues when we start Excel and fight over memory, which can lead to large files crashing while opening.

Steps to remove Excel add-ins:

1. Go to the File tab on your ribbon.
2. Select Options from the left side of the backstage view.
3. In the Excel Options box, choose the Add-ins tab.
4. Beside Manage, click on the Manage drop-down and select COM Add-ins.
5. Click Go.
6. In the COM Add-ins box, uncheck all the add-ins.
7. Click OK.

By removing unnecessary add-ins, you can significantly improve the performance of Excel and open large files without crashing.

2. Uncheck “Disable Hardware Graphics Acceleration” Option

Another feature that can cause Microsoft Excel to lag, freeze, or crash while opening is Excel animations. These animations are usually turned on automatically on all devices unless you change the setting. Disabling hardware graphics acceleration can help prevent these issues.

Steps to disable hardware graphics acceleration:

1. Go to File > Options.
2. Select Trust Center and then Trust Center Settings.
3. Choose Protected View.
4. Uncheck the relevant options.

3. Optimize Formulas and Functions

Formulas and functions are the backbone of any Excel workbook. However, complex formulas and resource-intensive functions can significantly slow down Excel, especially when dealing with large datasets. Here are some strategies to optimize them:

• Use Array Formulas Sparingly: Array formulas can be powerful, but they can also be resource-hungry. Use them only when necessary and consider alternative approaches.
• Replace Volatile Functions: Volatile functions like NOW(), TODAY(), and RAND() recalculate every time you make any change in the workbook. Replace them with non-volatile alternatives where possible.
• Evaluate Formulas: Use the Evaluate Formula feature to understand how each formula is calculated step by step. This helps identify bottlenecks and optimize them.

4. Adjust Calculation Settings

Excel recalculates formulas automatically by default. For large files, this can be a performance killer. Adjust the calculation settings to manual mode:

1. Go to File > Options > Formulas.
2. Under Calculation options, select Manual.
3. Click OK.

Now, Excel will only recalculate when you explicitly ask it to, reducing the chances of crashes while opening large files.

5. Use 64-Bit Excel

If you’re using a 32-bit version of Excel, consider switching to the 64-bit version. The 64-bit version can handle larger files and more memory, which is crucial for opening large Excel files without crashing.

6. Enable Multi-Threading

Excel 2016 and later versions support multi-threading, which allows Excel to use multiple processor cores simultaneously. To enable multi-threading:

1. Go to File > Options > Advanced.
2. Scroll down to the Formulas section.
3. Check the box for Enable multi-threaded calculation.
4. Click OK.

7. Use Power Query

Power Query (now called Get & Transform in Excel) is a powerful tool for data transformation and cleaning. It can handle large datasets efficiently and load only the necessary data into your workbook. Use it to import data from external sources and shape it before loading it into Excel.

8. Split Data into Multiple Sheets

If your workbook contains multiple sheets, consider splitting them into separate files. This reduces the overall file size and makes it easier to manage. You can link these smaller files using formulas or Power Query.

9. Compress Images

Images embedded in Excel files can significantly increase the file size. Compress images before adding them to your workbook. Use tools like TinyPNG or Compress Pictures in Excel to reduce image size without compromising quality.

10. Use a Reliable SSD

The speed of your storage device affects how quickly Excel can read and write data. If you’re still using an HDD (hard disk drive), consider upgrading to a reliable SSD (solid-state drive). SSDs are faster and more efficient, especially when dealing with large files.