Repairing a corrupted workbook

When you open a workbook that has been corrupted, Microsoft Office Excel automatically starts File Recovery mode and attempts to reopen and simultaneously repair the workbook.

Excel cannot always start File Recovery mode automatically. If you cannot open a workbook because it has been corrupted, you can try to repair the workbook manually.

You can also try other methods to recover workbook data when repairing a workbook is not successful. As a preventive measure, you may want to save your workbook often and create a backup copy every time that you save it. Or you can specify that Excel automatically creates a recovery file at specific intervals. This way, you will have access to a good copy of the workbook, if the original is deleted accidentally or if it becomes corrupted.What do you want to do?

Repair a corrupted workbook manually

Recover data from a corrupted workbook

Automatically save a backup copy of a workbook

Automatically create a recovery file at specific intervals

Repair a corrupted workbook manually

1. Click the Microsoft Office Button , and then click Open.
2. In the Open dialog box, select the corrupted workbook that you want to open.
3. Click the arrow next to the Open button, and then click Open and Repair.
4. Do one of the following:
   • To recover as much of the workbook data as possible, click Repair.
   • To extract values and formulas from the workbook when an attempt to repair the workbook is not successful, click Extract Data.

Recover data from a corrupted workbook

The following methods may help you to salvage data that might otherwise be lost. If one method is not successful, you can try another. You can also try third-party software solutions to try to recover workbook data if you cannot recover your data by using these methods.

Important If a disk error or network error makes it impossible to open a workbook, move the workbook to a different hard disk drive or from the network to a local disk before you spend time trying any of the following recovery options.

• To recover data when the workbook is open in Excel, do one of the following:
  • Revert the workbook to the last saved version If you are editing a worksheet and the workbook becomes corrupted before you save your changes, you can recover the original worksheet by reverting it to the last saved version.

    To revert the workbook to the last saved version, do the following:

    1. Click the Microsoft Office Button , and then click Open.
    2. Double-click the name of the workbook that you have open in Excel.
    3. Click Yes to reopen the workbook.

       Note The workbook reverts to the last saved version of the workbook. Any changes that may have caused the workbook to become corrupted have been discarded.

  • Save the workbook in SYLK (Symbolic Link) format By saving the workbook in SYLK format, you may be able to filter out the corrupted elements. SYLK format is typically used to remove printer corruption.

    To save the workbook in SYLK format, do the following:

    1. Click the Microsoft Office Button , and then point to the arrow next to Save As.
    2. In the Save as type list, click SYLK (Symbolic Link), and then click Save.

       Note Only the active sheet in the workbook is saved when you use the SYLK file format.

    3. If a message prompts you that the selected file type does not support workbooks that contain multiple sheets, click OK to save only the active sheet.
    4. If a message prompts you that the workbook may contain features that are not compatible with the SYLK format, click Yes.
    5. Click the Microsoft Office Button , and then click Close.
    6. If a message prompts you to save the changes that you made, click Yes.
    7. Click the Microsoft Office Button , and then click Open.
    8. Select the .slk file that you saved, and then click Open.

       Note To see the .slk file, you may need to click All Files or SYLK Files in the Files of type list.

    9. Click the Microsoft Office Button , and then point to the arrow next to Save As.
    10. In the Save as type box, click Excel Workbook.
    11. In the File name box, type a new name for the workbook to create a copy without replacing the original workbook, and then click Save.

        Note Because this format saves only the active worksheet in the workbook, you must open the corrupted workbook repeatedly and save each worksheet separately.

  • Save the workbook in Hypertext Markup Language (HTML) format By saving the workbook in HTML format, you may be able to filter out the corrupted elements.

    To save the workbook in HTML format, do the following:

    1. Click the Microsoft Office Button , and then point to the arrow next to Save As.
    2. In the Save as type list, click Web Page.
    3. Click Publish.
    4. Under Items to publish, in the Choose box, click Entire Workbook, and then click Publish.
    5. Click the Microsoft Office Button , and then click Close.
    6. Click the Microsoft Office Button , and then click Open.
    7. Select the HTML document that you saved, and then click Open.

       Note To see all HTML documents, you may need to click All Files or All Web Pages in the Files of type list.

    8. Click the Microsoft Office Button , and then point to the arrow next to Save As.
    9. In the Save as type list, click Excel Workbook.
    10. In the File name box, type a new name for the workbook to create a copy without replacing the original workbook, and then click Save.

        Note Some features may be lost when you save a workbook in HTML format.

• To recover the data when you cannot open the workbook in Excel, do one of the following:
  • Set the calculation option in Excel to manual To open a workbook, try changing the calculation setting from automatic to manual. Because the workbook won't be recalculated, it may open.

    To set the calculation option in Excel to manual, do the following:

    1. Make sure that a new, blank workbook is open in Excel. If a new, blank workbook is not open, do the following:
       1. Click the Microsoft Office Button , and then click New.
       2. Under New Blank, click Blank document.
    2. Click the Microsoft Office Button , and then click Excel Options.
    3. In the Formulas category, under Calculation options, click Manually.
    4. Click OK.
    5. Click the Microsoft Office Button , and then click Open.
    6. Select the corrupted workbook, and then click Open.
  • Use external references to link to the corrupted workbook If you want to retrieve only data and not formulas or values from the workbook, you can use external references to link to the corrupted workbook.

    To use external references to link to the corrupted workbook, do the following:

    1. Click the Microsoft Office Button , and then click Open.
    2. In the Look in box, select the folder that contains the corrupted workbook, and then click Cancel.
    3. Click the Microsoft Office Button , and then click New.
    4. Under New Blank, click Blank document.
    5. In cell A1 of the new workbook, type =File Name!A1, where File Name is the name of the corrupted workbook, and then press ENTER.

       Tip You have to enter only the name of the workbook - you do not have to type the extension.

    6. Click the workbook, and then click OK.
    7. If the Select Sheet dialog box appears, select the appropriate sheet, and then click OK.
    8. Select cell A1.
    9. On the Home tab, in the Clipboard group, click Copy.

       Keyboard shortcut You can also press CTRL+C.

    10. Select an area that is approximately the same size as the range of cells that contain data in the corrupted workbook.
    11. On the Home tab, in the Clipboard group, click Paste.

        Keyboard shortcut You can also press CTRL+V.

    12. With the range of cells still selected, on the Home tab, in the Clipboard group, click Copy again.

        Keyboard shortcut You can also press CTRL+C.

    13. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
    14. Under Paste, select Values, and then click OK.

        Note Pasting values removes the links to the corrupted workbook and leaves only the data.

  • Open the corrupted workbook in Microsoft Office Word or Microsoft WordPad If you have the Microsoft Office Excel converter installed, you might be able to open the workbook in Word. If the workbook opens in Word, you can recover the data. You cannot use this method to recover module sheets, dialog sheets, chart sheets, macro sheets, or any embedded charts. You won't be able to recover cell formulas either. You can recover only the results of those formulas that are currently displayed in the cells of the corrupted workbook.

    You can also open your Excel workbook in WordPad. If the workbook does open, you may be able to recover the Microsoft Visual Basic code in your modules and class modules. You can search for the words "Sub" or "Function" to find your code.

  • Use a macro to extract data from a corrupted workbook If a chart is linked to the corrupted workbook, you can use a macro to extract the source data of the chart.

    To use a macro, do the following:

    1. Enter the following macro code in a module sheet:

        Sub GetChartValues97() Dim NumberOfRows As Integer Dim X As Object Counter = 2 ' Calculate the number of rows of data. NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values) Worksheets("ChartData").Cells(1, 1) = "X Values" ' Write x-axis values to worksheet. With Worksheets("ChartData") .Range(.Cells(2, 1), _ .Cells(NumberOfRows + 1, 1)) = _ Application.Transpose(ActiveChart.SeriesCollection(1).XValues) End With ' Loop through all series in the chart and write their values to ' the worksheet. For Each X In ActiveChart.SeriesCollection Worksheets("ChartData").Cells(1, Counter) = X.Name With Worksheets("ChartData") .Range(.Cells(2, Counter), _ .Cells(NumberOfRows + 1, Counter)) = _ Application.Transpose(X.Values) End With Counter = Counter + 1 Next End Sub 

    2. Insert a new worksheet into your workbook and rename it ChartData.
    3. Select the chart from which you want to extract the underlying data values.

       Note The chart can be embedded on a worksheet or on a separate chart sheet.

    4. Run the GetChartValues97 macro.

       The data from the chart will be placed on the ChartData worksheet.

Automatically save a backup copy of a workbook

1. Click the Microsoft Office Button , and then point to the arrow next to Save As.
2. Click the arrow next to Tools, and then click General Options.
3. Select the Always create backup check box.

Automatically create a recovery file at specific intervals

1. Click the Microsoft Office Button , and then click Excel Options.
2. In the Save category, under Preserve backup information for your workbooks, select the Save AutoRecover information every check box, and then enter a number of minutes.
3. In the AutoRecover file location box, enter the location where you want to save the recovery file.
4. Make sure that the Disable AutoRecover for this workbook only check box is not selected.