10 ways to recover corrupted Excel files

Here are 10 simple ways to recover files, Excel spreadsheets are corrupted before considering ... crying or reworking from the beginning.

Excel spreadsheet files, as well as Word (especially for those who do office work, many books), once a mistake will cause a lot of trouble for users, because it contains a lot of numbers. material, important information. In the article below, TipsMake.com will introduce to you 10 simple ways to recover files, Excel spreadsheets are corrupted before considering . sitting crying or redoing from the beginning.

  1. Summary of expensive shortcuts in Microsoft Excel
  2. How to fix Errors were detected while saving files in Excel 2010

1. Try recovering by manual method:

If Excel's auto-recovery feature is incomplete, you can do this manually:

  1. Open the file from the File menu. For Excel 2007, click the Office button and select Open .
  2. Use the Control Panel Look In , find and identify the failed workbook.
  3. From the Open list of the Open button, select Open And Repair:

10 ways to recover corrupted Excel files Picture 110 ways to recover corrupted Excel files Picture 1

  1. If this is the first attempt to recover the workbook, click Repair .
  2. If you're lucky, Excel will fix the file. However, the file recovery feature is not always effective. Now, choose the second solution is to restore the data. Click Extract Data in step 4.

2. Recover data from a corrupted workbook

2.1. If an error occurs when the spreadsheet file is open:

When an error occurs while the workbook file is still open, you must not save the current session. Instead, go back to the previous archived version. In this way, you will avoid the recent error without losing data, even if this capability is very small.

To return to the last saved session, follow these steps:

  1. Select Open from the File menu menu. For Excel 2007, click the Office button and select Open .
  2. Use the Control Panel Look In to identify the file in error.
  3. Click Open .
  4. Now you will reopen the same workbook.

2.2. Recovering data when you cannot open a workbook in Excel:

  1. Set the calculation option in Excel to manual. Try changing the calculation settings from auto to manual. Workbooks can be opened because it doesn't need to be recalculated.
    1. Click File> New .
    2. Under New , click Blank workbook .
    3. Click File> Options .
    4. In the Formulas section, under Calculation option, select Manual , and then click OK .
    5. Click File> Open .
    6. Locate and then double-click the broken workbook to open it.
  2. Use external references to link to a broken workbook. If you just want to get the data, not the formula or the value from the workbook, try using the external reference to link to the broken workbook.
    1. Click File> Open .
    2. Go to the corrupted workbook folder.
    3. Right-click the corrupted workbook file name, click Copy , and then click Cancel .
    4. Click File> New .
    5. Under New , click Blank workbook .
    6. In cell A1 of the new workbook, type = File Name! A1 , where File Name is the name of the broken workbook that you copied in step 3, and then press Enter . ( Note: You only have to enter the name of the workbook and do not have to enter the file name extension).
    7. If the Update Values dialog box appears, select the corrupted workbook, and then click OK .
    8. If the Select Sheet dialog box appears, select the appropriate sheet, and then click OK .
    9. Select cell A1 .
    10. Click Home> Copy or press Ctrl + C.
    11. Select an area, starting from cell A1, approximately the size of the corrupted workbook data range.
    12. Click on Home> Paste or press Ctrl + V.
    13. With the cell range still selected, click Home> Copy or press Ctrl + C.
    14. Click Home > arrow under Paste , then in Paste Values , click Values .
    15. Pasting values ​​will delete the links to the broken workbook and leave only the data.
  3. Use macros to extract data from a corrupted workbook. If the chart linked to the workbook is corrupted, try using a macro to extract the chart's source data.
    1. Copy the following macro code and paste it into the module table:
 Sub GetChartValues ​​() 
Dim NumberOfRows As Integer
Dim X As Object
Counter = 2
'Calculate các số này của dữ liệu.
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 các các dòng trong các mục đã ghi rõ và ghi sang
'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

To insert or delete a worksheet in a workbook, right-click the worksheet, then rename it to ChartData . Select the chart to extract the basic data values. Note: The chart can be embedded in a worksheet or on a separate chart> run macro> Data from the chart is placed on the ChartData spreadsheet.

2.3. Automatically save backups of workbooks

With a workbook backup, you will always have access to your data if your workbook is accidentally deleted or corrupted.

1. Click File> Save As .

2. Click Computer , and then click the Browse button.

10 ways to recover corrupted Excel files Picture 210 ways to recover corrupted Excel files Picture 2

3. In the Save As dialog box, click the arrow next to Tools , and then click General Options .

10 ways to recover corrupted Excel files Picture 310 ways to recover corrupted Excel files Picture 3

4. In the General Options dialog box, select Always create backup .

10 ways to recover corrupted Excel files Picture 410 ways to recover corrupted Excel files Picture 4

2.4. Automatically create recovery files in the set time period

A recovery file of the worksheet also helps ensure you will have access to your data if your workbook is accidentally deleted or corrupted.

1. Click File> Options .

2. In the Save section, in Save workbooks , select Save AutoRecover information every box , then enter the minutes. (Default is 10).

10 ways to recover corrupted Excel files Picture 510 ways to recover corrupted Excel files Picture 5

3. In the AutoRecover file location box, enter the location where you want to save the recovery file.

4. Make sure Disable AutoRecover for this workbook is not selected.

3. Turn off automatic calculation

If Excel cannot open the spreadsheet file, or even the Open And Repair option is a problem, reset the calculation mode to manual processing and try again. Steps to take:

  1. Open a new spreadsheet file with empty content.
  2. From the Tools menu select Options and click on the Calculation tab. In Excel 2007, click the Office button, followed by Excel Options, and select Formulas on the left panel.
  3. In parameter settings for Calculation ( Calculation Options in Excel 2007), select Manual .
  4. Click OK .

10 ways to recover corrupted Excel files Picture 610 ways to recover corrupted Excel files Picture 6

Now open the corrupted file again. Maybe Excel will 'catch error' and open the problem file.

4. Use Microsoft Office Tools

If Excel cannot open the corrupted workbook, take the opportunity for Microsoft Office Tools. Usage is as follows:

  1. From the Start menu, select All Programs (on Windows XP).
  2. Select Microsoft Office .
  3. Select Microsoft Office Tools .
  4. Select Microsoft Office Application Recovery .
  5. Select Microsoft Office Excel in the dialog box.
  6. Click Recover Application . Processing may take a few minutes.

This recovery tool will turn off Excel and then reboot, displaying a list of restored spreadsheet files. If you're lucky, your precious Excel file will be on this list. Just open and continue using!

5. Try moving the file to another location:

Sometimes a spreadsheet file fails, not because of software reasons or users' mistakes. Most likely it is temporarily unavailable, network systems and servers often still mark them as corrupted files. In the event of a known error with similar causes and Excel cannot be fixed, please transfer the file to another folder, drive or server.

6. Let OpenOffice 'launch'

  1. Download the OpenOffice software

Some people claim to feel completely surprised with the satisfactory results when using the OpenOffice application suite to repair Excel error files. This is a famous free open source tool. If you cannot handle the spreadsheet file with errors on Excel, use OpenOffice Calc . Of course, this is often more complex and complicated than you think.

10 ways to recover corrupted Excel files Picture 710 ways to recover corrupted Excel files Picture 7

7. Open the error file on WordPad or Word

If you cannot fix the corrupted spreadsheet file, try opening it in WordPad. If successful, WordPad will convert everything to text. Of course, this does not necessarily make you completely happy beyond the joy of being able to retrieve data. This option will not be able to recover font and table formats . However, unlike some other data recovery options, WordPad will help you recover macros. Use the search command Sub and Function on the recovered data block to find them.

You can also open a corrupted .xls file on Word, but the results are usually quite limited. With this option, you must install the Microsoft Office Excel converter. Unlike WordPad, Word does not recover macros.

8. Handling cells - data cells are corrupted to recover data

Sometimes you can recover data by processing the cells on the corrupted file. You will not be able to retrieve formatting, charts, macros . but recovering data is already too lucky. The process is as follows:

  1. Open a new workbook, enter cell A1 the following formula to lead to cell A1 in the error file: the name of the error file ! A1 . (You do not need to add .xls).
  2. Click Enter .
  3. If the error file is not in the same folder, Excel displays the Update Values dialog box : the name of the file that is corrupted. Please use the control panel Look In to identify the corrupted file. Select this file and click OK .
  4. If the Select Sheet dialog box appears, select the appropriate sheet and click OK . Excel displays the cell A1 value of the error file.
  5. Select cell A1 and drag across the columns you feel you need to be able to recover the data. If Excel displays the Update Values dialog box - the file name is corrupted, select the error file and click OK .
  6. Repeat step 5, duplicate row A according to the number of goods you feel is sufficient to restore the error file.
  7. Select the data and click Copy from the Edit menu.
  8. Select Paste Special from the Edit menu, select Values .
  9. Click OK .
  10. Try the SYLK format to recover data:

10 ways to recover corrupted Excel files Picture 810 ways to recover corrupted Excel files Picture 8
Switching to another format is an effective solution when Excel fails

Microsoft recommends using the SYLK format to avoid errors when using Excel, especially printer-related errors. You can open the corrupted Excel file in this way:

  1. From the File menu, select Save As . In Excel 2007, click the Office button.
  2. From the Save As Type control panel, select SYLK (Symbolic Link) (* .slk) .
  3. To name. If the error file has a sheet, this is not necessary.
  4. Click Save .
  5. If the workbook file has multiple sheets, Excel will ask if you can continue using the format that doesn't support multiple sheets at the same time. Click OK .
  6. If Excel warns you that you can workbook with some formats that are incompatible with SYLK, select Yes .
  1. Note that the SYLK format only stores the currently used sheets . To restore data, you need to reopen the Excel file and save each sheet one by one. That's why in step 3 you need to name each sheet on the workbook to avoid confusion later.

After you've saved all the new sheets, open the .slk file and edit it to .xls. Be careful, don't use the name of the failed workbook file. If successful, you can save the values ​​from the entered formula but the formulas do not.

10. Restore macros

If you can restore the data but still bundle the macros, you can save them in the following way:

  1. Open Excel, do not open the error workbook.
  2. Set the calculation mode to manual mode (tip 3 above).
  3. Select Macro from the Tools menu, select Security and then select High . In Excel 2007, click the Office button -> Excel Options , and select Trust Center on the left of the table. Then, click the Trust Center Settings button, select Macro Settings , then select Disable All Macros Without Notification on the Macro Settings area . Finally click OK twice:

10 ways to recover corrupted Excel files Picture 910 ways to recover corrupted Excel files Picture 9

  1. Open the corrupted file. If Excel is open, you will see a message indicating that the macro is currently turned off. If Excel is turned off, this method is considered a failure.
  2. Click [Alt] + [F11] to open the Visual Basic Editor (VBE) .
  3. Use Project Explorer (click [Ctrl] + R ), right-click the module and select Export File ).
  4. Enter the name and directory of the module.
  5. Repeat steps 6 and 7 according to your estimates, enough to extract all modules.
  6. Close VBE and exit Excel.
  7. Open an empty workbook (or a new workbook containing newly recovered data) and import modules.

Good luck!

See more:

  1. Recovering unsaved Word files, retrieving Word, Excel and PowerPoint files before saving
  2. How to convert PDF files to Word simply, without font errors
  3. How to create the index on the bottom, write the exponent in Word
4.3 ★ | 4 Vote