Excel spreadsheet files, as well as Word (especially for those of you who do office work and book a lot), once corrupted, it will cause a lot of trouble for users, because it contains a lot of numbers. material, important information.
- 1. Try manual recovery:
- 2. If an error occurs while the workbook file is open:
- 3. Recover data when you cannot open the workbook in Excel:
- 4. Automatically save backups of workbooks
- 5. Automatically generate recovery file at set time interval
- 6. Turn off automatic calculation mode
- 7. Using Microsoft Office Tools
- 8. Try moving the file somewhere else:
- 9. Handling damaged cells – data cells to recover data
- 10. Restore macros
1. Try manual recovery:
If Excel’s auto-recovery feature is lacking, you can do this manually:
- Open the file from the File menu. For Excel 2007, click the Office button and select Open.
- Using the Look In control panel, find and identify the faulty workbook.
- From the Open button drop-down list, select Open And Repair:
If this is the first attempt to restore the workbook, click Repair.
- With luck, Excel will repair the file. However, the file recovery feature is not always effective. At this point, choose the second solution, which is data recovery. Click Extract Data in step 4.
2. If an error occurs while the workbook 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. In this way, you will avoid the error that happened just now without losing data, even if this possibility is very small.
To return to a previously saved session, follow these steps:
- Select Open from the File menu For Excel 2007, click the Office button and select Open.
- Use the Look In panel to identify the corrupted file.
- Click Open.
- Now you will reopen the workbook as before.
3. Recover data when you cannot open the workbook in Excel:
Set the calculation option in Excel to manual. Try changing the calculation settings from automatic to manual. The workbook can be opened because it does not need to be recalculated.
- Click File > New.
- Under New, click Blank workbook.
- Click File > Options.
- In the Formulas section, under Calculation options, select Manual, and then click OK.
- Click File > Open.
Locate and then double-click the damaged workbook to open it.
Use an external reference to link to a broken workbook. If you only want to get the data and not the formulas or calculated values from the workbook, try using an external reference to link to the broken workbook.
- Click File > Open.
- Go to the folder containing the damaged workbook.
- Right-click the file name of the damaged workbook, click Copy, and then click Cancel.
- Click File > New.
- Under New, click Blank workbook.
- In cell A1 of the new workbook, type =File Name!A1, where File Name is the name of the corrupted workbook that you copied in step 3, and then press Enter. (Note: You only have to enter the name of the workbook and not the filename extension).
- If the Update Values dialog box appears, select the damaged workbook, and then click OK.
- If the Select Sheet dialog box appears, select the appropriate sheet, and then click OK.
- Select cell A1.
- Click Home > Copy, or press Ctrl+C.
- Select an area, starting at cell A1, that is approximately the same size as the range of cells containing the data in the damaged workbook.
- Click Home > Paste, or press Ctrl+V.
- With the range of cells still selected, click Home > Copy again or press Ctrl + C.
- Click Home > the arrow under Paste, and then under Paste Values, click Values.
- Pasting the values removes the links to the broken workbook and leaves only the data.
Use macros to extract data from damaged workbooks. If the chart associated with the workbook is broken, try using a macro to extract the chart’s source data.
Copy the following macro code and paste it into the modules panel:
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.
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
‘ 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
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Counter = Counter + 1
To insert or delete a worksheet in a workbook, right-click the sheet, and then rename it ChartData. Select the chart you want to extract the underlying data values from. Note: The chart can be embedded in a worksheet or on a separate chart > run macro > Data from chart placed on ChartData worksheet.
4. Automatically save backups of workbooks
With a workbook backup, you will always have access to your data if your workbook is accidentally deleted or damaged.
- Click File > Save As.
- Click Computer, and then click the Browse button.
- In the Save As dialog box, click the arrow next to Tools, and then click General Options.
- In the General Options dialog box, select Always create backup
5. Automatically generate recovery file at set time interval
A worksheet recovery file also helps ensure you’ll have access to your data if your workbook is accidentally deleted or damaged.
- Click File > Options.
- In the Save section, in Save workbooks, select Save AutoRecover information every box, then enter the number of minutes. (Default is 10).
- In the AutoRecover file location box, enter the location where you want to save the recovered file.
- Make sure that Disable AutoRecover for this workbook only is not selected.
6. Turn off automatic calculation mode
If Excel cannot open the workbook file, or even the Open And Repair option fails, reset the calculation mode to manual processing and try again. Implementation steps:
- Open a new spreadsheet file with empty content.
- From the Tools menu, select Options and click the Calculation tab. On Excel 2007, click the Office button, followed by Excel Options and select Formulas on the panel on the left.
- In the parameter setting for Calculation (Calculation Options on Excel 2007), select Manual.
- Click OK.
At this point, open the corrupted file again. Maybe Excel will “catch the error” and open the problem file.
7. Using Microsoft Office Tools
If Excel cannot open the error workbook file, give Microsoft Office Tools a chance. Usage is as follows:
- From the Start menu, select All Programs (on Windows XP).
- Select Microsoft Office.
- Select Microsoft Office Tools.
- Select Microsoft Office Application Recovery.
- Select Microsoft Office Excel in the dialog box.
- 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 workbook files. With luck, your precious Excel file will be on this list. Just open it up and keep using it!
8. Try moving the file somewhere else:
Sometimes a corrupt spreadsheet file is not necessarily caused by software or user mistakes. Most likely it is temporarily inaccessible, networks and servers often still mark them as corrupted files. In case you encounter an error that has been identified with a similar cause and Excel cannot fix, move the file to a different folder, drive, or server.
Let OpenOffice “take action” – Download OpenOffice software
Some people claim to be completely surprised with the excellent results when using the OpenOffice application suite to repair Excel’s corrupted files. This is the famous free and open source tool. If you can’t handle the corrupted spreadsheet file in Excel, use OpenOffice’s Calc. Of course, this is often more laborious and complicated than you might think.
Open the error file on WordPad or Word
If you can’t fix the corrupted spreadsheet file, try opening it with 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, etc. 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 corrupted .xls files 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.
9. Handling damaged cells – data cells to recover data
Sometimes you can recover data by processing cells on the corrupted file. You won’t be able to get the formatting, charts, macros, etc., but recovering the data is already a good thing. The procedure is as follows:
- Open a new workbook, enter cell A1 the following formula to get to cell A1 in the error file: name of the error file!A1. (You don’t need to add .xls).
- Click Enter.
- If the error file is not in the same directory, Excel displays the Update Values:name of the corrupted file’s dialog box. Use the Look In control panel to identify the corrupted file. Select this file and click OK.
- If the Select Sheet dialog box appears, select the appropriate sheet and click OK. Excel will display the cell A1 value of the error file.
- Select cell A1 and drag across the columns that 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.
- Repeat step 5, duplicating row A as many rows as you feel are sufficient to recover the corrupted file.
- Select the data and click Copy from the Edit menu.
- Choose Paste Special from the Edit menu, select Values.
- Click OK.
- Try SYLK format to recover data:
Microsoft recommends using the SYLK format to avoid errors when using Excel, especially printer-related errors. You can open the corrupted Excel file by doing this:
- From the File menu, choose Save As. On Excel 2007, click the Office button.
- From the Save As Type panel, select SYLK(Symbolic Link) (*.slk).
- To name. If the error file has a sheet, this is not necessary.
- Click Save.
- If the workbook file has multiple sheets, Excel will ask you if you want to continue using the format that doesn’t support multiple sheets at once. Click OK.
- If Excel warns you that the workbook may contain some formats that are not compatible with SYLK, select Yes.
- Note that the SYLK format only saves the sheets in use. To restore the 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 have saved 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 the formulas cannot.
10. Restore macros
If you can recover data but still have trouble with macros, you can save them in the following way:
- Open Excel, don’t open error workbooks.
- Set the calculation mode to manual (tip 3 above).
Select Macros from the Tools menu, choose Security, and then High. On Excel 2007, click the Office button -> Excel Options, and select Trust Center on the left side of the table. Then, click 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:
- Open the corrupted file. If Excel opens, you’ll see a message that the macro is currently disabled. If Excel is turned off, this method is considered a failure.
- Click [Alt]+[F11] to open the Visual Basic Editor (VBE).
- Using Project Explorer (click [Ctrl]+R), right click on the module and select Export File).
- Enter the name and directory of the module.
- Repeat steps 6 and 7 according to your approximation, enough to extract all modules.
- Close the VBE and exit Excel.
- Open a blank workbook (or a new workbook containing newly recovered data) and import the modules.