How to Work on Large Excel Files Without Crashing (5-Step Guide)
Working with large Excel files, such as those around 10MB, in Windows 10 can sometimes lead to crashes. While file size itself isn't the direct cause of crashes, it can contribute to issues, especially if the file is complex or contains a lot of data. Accountants often work with much larger files, sometimes exceeding 10MB, and may experience crashes due to various factors, including file complexity, Excel's limitations, or system resource constraints. However, the file size itself is not the primary reason for crashes.
While it's often assumed that big file sizes are the primary cause of Excel crashing, the reality is that even smaller files can cause issues. In fact, modern computers can handle workbooks of up to 20MB in size without crashing, so it's likely that other factors are at play when your spreadsheet freezes or crashes, even if it's relatively small.
Why Large Excel Files Crash
Here are the possible reasons why your Excel file is slow to use:
- Formatting, styles, and shapes in a worksheet
- Calculations and formulas in a worksheet
- Computer's RAM issue
How to Make Large Excel Files Work Faster Without Crash
- Important
- To ensure the integrity of your Excel file, it's recommended to make a backup of your workbook regularly, especially when working with large files containing numerous rows of data and complex styles/formulas. This is crucial in case of unexpected data loss due to Excel crashes or unresponsiveness. To mitigate this risk, enable auto-save in Excel, which can save your workbook every 1-5 minutes, providing a safeguard against potential data loss.
To troubleshoot a slow Excel workbook upon opening or editing, we'll explore three key factors that may be causing the issue. Whether it's a small or large file, the solutions provided in each section should help resolve the crashing problem in Excel.
Step 1. Remove Excessive Formatting
Formatting cells on a worksheet can make important information stand out, but it can also cause the file size to grow quickly if unnecessary cells are formatted. To address this issue, Microsoft offers a built-in add-on called Clean Excess Cell Formatting, which can be accessed through the Inquire tab in Microsoft Office 365 and Office Professional Plus 2013. If you don't see the Inquire tab, you can enable the Inquire add-in to access this feature.
- Click File > Options > Add-Ins.
- Make sure COM Add-ins is selected in the Manage box, and click Go.
- In the COM Add-To make the Inquire tab visible in the ribbon, click on the "Developer" tab in the ribbon, click on the "Document Management" group, click on the "Inquire" button, click on the "Inquire" button in the dialog box that pops up, and then click "OK". This should add the Inquire tab to the ribbon.
To remove excess formatting in the current worksheet, select the entire worksheet by pressing Ctrl+A or by clicking on the top left corner of the worksheet and dragging the mouse to the bottom right corner. Then, go to the Home tab in the Excel ribbon and click on the "Clear" button in the Styles group.
- On the Inquire tab, click Clean Excess Cell Formatting.
- After clearing excess formatting, you have the option to save the changes to all worksheets or cancel them. You can choose to save the changes by clicking "Yes" or cancel them by clicking "No".
Step 2. Remove Unused Styles
Using multiple styles on an Excel workbook can cause the "Too many different cell formats" error, leading to constant crashes. To avoid this issue, it's best to limit styles on a single worksheet. If a workbook already contains several styles, you can use one of the third-party tools suggested by Microsoft to clean up the document.
- Excel formats (xlsx, xlsm) - XLStyles Tool.
- The Remove Styles Add-in does not support binary Excel formats (xls, xlsb), workbooks protected by a password, and encrypted workbooks. This means that users cannot use this add-in to remove styles from these types of files.
Step 3. Remove Conditional Formatting
- Under Home, click Conditional Formatting.
- Choose Clear Rules.
- Select Clear Rules from Entire Sheet
- If multiple sheets adopt the rule, repeat the steps to clear them all
Step 4. Remove Calculations and Formulas
If you've removed unnecessary cell formatting, styles, and conditional formatting, but your Excel workbook still works slowly on a large scale, you may need to review your formulas and calculations. You don't have to check every formula, but focus on the primary ones that tend to consume a lot of resources.
- Formulas that reference entire rows or columns
- SUMIF, COUNTIF, and SUMPRODUCT
- Large number of formulas
- Volatile functions
- Array formulas
Step 5. Examine the Computer RAM Issue
If the four-step efforts don't improve performance, it may be due to insufficient computer memory, so refer to tips on how to solve high RAM memory usage issue in Windows 10.
How to Repair Corrupted Large Excel Files Efficiently
If Excel has crashed and corrupted your files, you can try using Qiling file repair to fix the damaged files. This tool can resolve Excel file corruption and restore the data in .XLS or .XLSX files, fixing various types of errors such as unrecognizable format, unreadable content, and Excel runtime errors.
With this file repair tool, you can:
- Repair corrupted Excel with tables, images, charts, formulas, etc.
- Repair multiple .XLS and .XLSX at one time
- You can fix damaged Excel files in various versions, including 2019, 2016, 2013, 2010, 2007, 2003, and 2000, using the built-in repair tool or a third-party software. The repair tool can be accessed by opening the file and going to File > Info > Manage Workbook > Edit Workbook, then selecting the 'Repair' option.
To repair a large Excel file that is inaccessible, follow these guides. Qiling file repair software can also assist in repairing corrupted Word, PowerPoint, and PDF documents.
Step 1.Launch Deep Data Recovery, select the desired file types, and click "Next" to initiate the recovery process.
Step 2. This software allows you to scan and fix corrupted documents, including Word, Excel, PPT, and PDF files, using the same steps to repair damaged files.
Step 3. Qiling data recovery and repair tool scans for all lost and corrupted files. You can find target files by file type or search for them by typing the file name in the search box.
Step 4. Deep Data Recovery can automatically repair damaged documents and save the repaired Word, Excel, and PDF files to a safe location after previewing them.
Related Articles
- How to Recover Deleted Files/Photos on Phone from Hide it Pro
- Get Your Answer: Does Formatting A Laptop Remove Viruses?
- Picture Contains Invalid Image File Header, How Do I Fix It? [5 Ways]
- Fix Corrupted Database/Data Error on PS4