Summary of ways to recover corrupted Excel files

Excel spreadsheet files, as well as Word (especially for those who do a lot of office work and bookkeeping), once corrupted will cause a series of problems for users, because they contain a lot of numbers. important data and information. In the article below, TipsMake.com will introduce to you simple ways to recover corrupted Excel files and spreadsheets before thinking about. crying or starting over.

1. Try manual recovery:

If Excel's automatic error recovery feature is slow, you can do this manually:

1. Open the file from the File menu . For Excel 2007, click the Office button and select Open .
2. Using the Look In panel , find and identify the corrupted workbook.
3. From the Open button's drop-down list , select Open And Repair:

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

2. Recover data from damaged workbook

2.1. If the error occurs while the spreadsheet file is open:

When an error occurs and the workbook file is still open, you must not save the current session. Instead, roll back to the previous archived version. This way, you will avoid the error that happened just now without losing data, even though this possibility is very small.

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

1. Select Open from the File menu. For , click the Office button and select Open .
2. Use the Look In panel to identify corrupted files.
3. Click Open .
4. At this point you will reopen the workbook as before.

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

1. Set Excel calculation options to manual. Try changing the calculation settings from automatic to manual. The workbook can be opened because it does not need to be recalculated.
   1. Click File > New .
   2. Under New , click Blank workbook .
   3. Click File > Options .
   4. In the Formulas section , under the Calculation option , select Manual , then click OK .
   5. Click File > Open .
   6. Locate and then double-click the damaged workbook to open it.
2. Use an external reference to link to the broken workbook. If you only want to get data and not formulas or calculated values ​​from the workbook, try using an external reference to link to the broken workbook.
   1. Click File > Open .
   2. Go to the folder containing the damaged workbook.
   3. Right-click the file name of the damaged workbook, 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 damaged workbook you copied in step 3, then press Enter . ( Note: You only have to enter the name of the workbook and not the file name extension).
   7. If the Update Values ​​dialog box appears, select the damaged workbook, 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 a range, starting from cell A1, approximately the same size as the range of cells containing data in the damaged workbook.
   12. Click Home > Paste or press Ctrl+V .
   13. With the cell range still selected, click Home > Copy again or press Ctrl + C .
   14. Click Home > the arrow below Paste , then under Paste Values , click Values .
   15. Pasting the values ​​will remove the links to the broken workbook and leave only the data.
3. Use macros to extract data from damaged workbooks. If the chart associated with 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 panel:

Sub GetChartValues() 
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 ) 
= 
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, and then rename it ChartData . Select the chart you want to extract basic data values ​​from. 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 worksheet .

2.3. Automatically save backup copies of workbooks

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

1. Click File > Save As .

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

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

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

2.4. Automatically create recovery files within the set time period

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

1. Click File > Options .

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

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 only is not selected.

3. Turn off automatic calculation mode

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

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 the parameter settings for Calculation ( Calculation Options on Excel 2007), select Manual .
4. Click OK .

At this point, please reopen the corrupted file. It is possible that Excel will 'catch the error' and open the problematic file.

4. Use Microsoft Office Tools

If Excel cannot open the error workbook file, give Microsoft Office Tools a chance. 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 shut down Excel and then restart it, displaying a list of recovered spreadsheet files. If you're lucky, your precious Excel file will be on this list. Just open it and keep using it!

5. Try moving the file somewhere else:

Sometimes a spreadsheet file is corrupted not necessarily due to software reasons or user mistakes when using it. It's very likely that it's temporarily inaccessible; network systems and servers often mark them as corrupt files. In case you encounter an error that is determined to have a similar cause and Excel cannot fix it, move the file to another folder, drive, or server.

6. Let OpenOffice 'take action'

Some people claim to be completely surprised with the satisfactory results when using the OpenOffice application suite to repair corrupted Excel files. This is a famous free and open source tool. If you can't handle the corrupted spreadsheet file on Excel, use OpenOffice 's Calc . Of course, this is often more laborious and complicated than you think.

7. Open the error file on WordPad or Word

If you can't fix the corrupted spreadsheet file, try opening it in WordPad. If successful, WordPad will convert everything to text. Of course, this doesn't necessarily make you completely satisfied other than the joy of being able to get your data back. This option will not be able to recover font formats, tables. However, unlike some other data recovery options, WordPad will help you recover macros. Use the Sub and Function search commands on the recovered data block to find them.

You can also open the corrupted .xls file in Word, but the results are usually quite limited. With this option, you must install the Microsoft Office Excel conversion tool. Unlike WordPad, Word cannot restore macros.

8. Handle corrupted data cells to restore data

Sometimes you can recover data by processing the cells on the corrupted file. You won't be able to get back the formatting, charts, macros. but recovering the data is also a blessing. The procedure is as follows:

1. Open a new workbook, enter in cell A1 the following formula to get to cell A1 in the error file: name of error file !A1 . (You don't need to add .xls).
2. Click Enter .
3. If the error file is not in the same folder, Excel displays the Update Values: name of the error file dialog box. Use the Look In panel to identify corrupted files. Select this file and click OK .
4. If the Select Sheet dialog box appears, select the appropriate sheet and click OK . Excel will display the value of cell A1 of the error file.
5. Select cell A1 and drag across the columns you feel are necessary to be able to restore the data. If Excel displays the Update Values ​​- error file name dialog box, select the error file and click OK .
6. Repeat step 5, duplicating row A to the number of rows you feel is enough to recover the corrupted 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 SYLK format to recover data:

Switching to another format is a quite 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 this way:

1. From the File menu, select Save As . On Excel 2007, click the Office button .
2. From the Save As Type panel , select SYLK(Symbolic Link) (*.slk) .
3. To name. If the error file has one sheet, this is not necessary.
4. Click Save .
5. If the workbook file has multiple sheets, Excel will ask you whether to continue using a format that does not support multiple sheets at the same time. Click OK .
6. If Excel warns you that your workbook may contain some format that is not compatible with SYLK, select Yes .

1. Note that the SYLK format only saves the sheets in use . 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 saving all the sheets in the new format, open the .slk file and edit it to .xls. Be careful not to use the name of the corrupted workbook file. If successful, you can save the values ​​from the entered formula but not the formulas.

10. Restore macros

If you can recover data but still have trouble dealing with macros, you can save them in the following way:

1. Open Excel, don't open error workbooks.
2. Set calculation mode to manual (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 side of the table. Then, click on the Trust Center Settings button , select Macro Settings , then select Disable All Macros Without Notification on the Macro Settings settings area . Finally click OK twice:

1. Open the corrupted file. If Excel opens, you'll see a message stating that macros are currently disabled. If Excel turns off, this method is considered a failure.
2. Click [Alt]+[F11] to open the Visual Basic Editor (VBE) .
3. Using Project Explorer (click [Ctrl]+R ), right click on the module and select Export File ).
4. Enter the name and directory of the module.
5. Repeat steps 6 and 7 to your satisfaction, 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 the modules.

Ways to prevent workbook errors in Excel

Excel spreadsheets can become corrupted for many reasons, most of which you can avoid. The small but 'powerful' tips below will help you minimize the possibility of Excel workbook errors:

1. Always save an Excel document before closing it and avoid shutting down the system or restarting it when the Excel workbook is open.
2. Regularly scan Excel workbooks with reliable antivirus software.
3. Avoid connecting your laptop to a public network because it may contain malware that can harm computer files, including Excel workbooks. Similarly, do not plug your USB into a public computer to avoid being infected with malware.
4. Whether you're planning to install Excel plugins so they display beautifully on spreadsheets or save time performing tasks for your work, you should only install them from trusted sources and vendors.
5. Regularly back up Excel data to avoid losing important data.

Good luck!