July 17, 2024

How to Repair a Corrupted Excel Workbook

How to Repair a Corrupted Excel Workbook

If you’re struggling to open a corrupt Excel workbook, you’re not alone. Most of us have been in such a situation at least once in our lives. The good news, however, is that there’s a good chance that you can repair your corrupt Excel workbook by following a few simple steps.

If not, at least you’ll be able to extract most of the data from your Excel workbook in most cases. So before you lose all hope, follow the methods given below:

Manual Recovery of a Corrupted Excel Workbook

When you open a corrupt Excel workbook, Excel will automatically start its File Recovery mode to repair the corrupt file. If Excel’s automatic File Recovery fails to repair the corrupted workbook, you can follow the steps below to repair your workbook:

  1. Open Excel and click File at the top left and select Open from the drop-down menu.
    Open dialog box Excel

  2. From the Open dialog box, select the folder that contains the corrupted file and locate the file. You can also search for it by entering its name in the text box.
  3. Select the corrupted workbook and click the arrow beside the Open button. Then select Open and Repair.
  4. You’ll receive the following prompt:
    Excel Workbook Repair

  5. Click Repair to recover most of your data.
  6. If that doesn’t work, you can extract the values and formulas from your corrupted workbook by selecting Extract Data.

What if That Doesn’t Work?

Sometimes, neither automatic File Recovery nor manual recovery work. But that doesn’t mean you should lose all hopes of recovery.

Following are some other methods you can use to recover a corrupted Excel workbook:

Open the Last Saved Version

If the workbook gets corrupted when it’s open in Excel, there’s a chance that the changes you made to the workbook might have corrupted it. In such a case, you should return to the last saved version of your workbook by following the steps listed below:

  1. Go to File > Open.
  2. Locate the corrupted file and double-click it.
  3. It will reopen the version of the workbook before you made the changes that corrupted the file.

Change Excel’s Calculation Settings

If the corrupted workbook isn’t open in Excel, and you cannot access it, try opening it after turning off automatic calculations. Follow the steps given below to change Excel’s calculation settings:

  1. Open a new workbook in Excel.
  2. Go to File at the top left and click Options.
  3. Choose the Formulas category in the Options dialog box and select Manual under the Workbook Calculation.
    Excel Options

  4. Close the dialog box by clicking Ok.
  5. Go to File > Open, locate the corrupted workbook and open it.

Use External References

If changing the calculation settings to manual doesn’t work, use external references to try and link to the corrupted Excel workbook. This method will only recover the data, i.e. any formulas, formats, macros, etc., won’t be recovered. Follow the steps detailed below:

  1. Open a new Excel workbook and type the following formula in cell A1 to reference cell A1 of the corrupted workbook and press Enter:
    =File Name!A1
    Using External References to link to the corrupted Excel File

    Enter the name of the corrupted Excel workbook in place of File Name. Don’t include the file name extension, i.e. .xls, etc.

  2. If the corrupted workbook is in a different folder, you’ll see an Update Values dialog box. Find and select the corrupted workbook and click OK.
  3. If the corrupted workbook has multiple sheets, you’ll see a Select Sheet dialog box. Find and select the sheet you want to repair and click OK.
  4. Now you’ll see the value of cell A1 of the corrupted workbook in the cell A1 of your new Excel workbook.
  5. Select cell A1 and press Ctrl + C to copy.
  6. Now select an area of cells (including cell A1) similar in size to the area in the corrupted workbook that contains the required data.
  7. Press Ctrl + V to paste the values from the corrupted workbook into the new workbook.
  8. Copy the selected area again by pressing Ctrl + C.
  9. Now go to Home at the top left (next to FILE) and click the arrow below PASTE. Click Values under Paste Values to remove links to the corrupted workbook.
    Excel Paste Options

  10. Now you’ve got all the values from the corrupted workbook in your new workbook that you can save.

Move the Corrupted Workbook

Sometimes your Excel workbook isn’t corrupted, but it’s still inaccessible because of a disk or network error. In such cases, Excel won’t be able to repair the file (because it wasn’t corrupted in the first place).

To access such workbooks, simply move the file into another hard disk drive, folder, or server, and it should work.

How to Prevent Excel Workbook Corruption

Excel workbooks can get corrupted due to various reasons, most of which you can avoid. Ensure the following and minimize the chances of Excel workbook corruption:

  • Always save your Excel document before closing and avoid system shutdown or restart when your Excel workbook is open.
  • Regularly scan your Excel workbooks with a trusted antivirus.
  • Avoid connecting your laptop to a public network, as it can contain malware that can harm your computer files, including Excel workbooks. Similarly, don’t plug your USB drive into a public computer to avoid catching malware.
  • Whether you’re installing Excel plugins to make visually pleasing spreadsheets or to save time on your business tasks, make sure you’re only installing them from trusted publishers.
  • Always back up your Excel data.

Let’s expand on the last point in more detail.

How to Back Up Your Excel Workbooks

You should always back up your Excel files to prevent data loss in case your Excel workbook gets corrupted. To automatically save a backup copy of your Excel files, follow the steps below:

  1. Open Excel and click File > Save As.
  2. You’ll see the Save As dialog box.
    Automatically Bakcup Excel Workbook-1

  3. Click the arrow beside Tools and select General Options.
  4. The General Options dialog box will pop up.
    General Options Dialog Box

  5. Check the Always create backup box and click OK.

Another prevention tactic is to automatically create a recovery file of your Excel workbook after every few minutes or so by following the steps given below:

  1. Go to File > Options.
  2. Select Save in the Excel Options dialog box.
  3. Make sure the Save AutoRecover information every box is checked.
    Automatically save recovery Excel file

  4. Enter the number of minutes to set the interval, after which Excel will automatically save your workbook.
  5. Enter the location where you want to save the recovery file in the AutoRecover file location box.
  6. Uncheck the Disable AutoRecover for this workbook only box and click OK.

Keep the Calculations Going

The next time your Excel workbook gets corrupted, don’t fret. Follow the methods detailed above until one of them fixes the workbook or helps extract valuable data.

But Excel workbooks are not the only files that get corrupted; word files, JPEGs, and other important files can also get corrupted. Fortunately, you can find several software tools designed specifically to recover important files of various formats that you can use to recover those files.