Whenever any kind of corruption prevails in your Excel file, it not only hampers its efficient working but also adds to your worries by damaging the data which in turn requires you to re-enter it. Taking back-ups at regular intervals proves to b a good precautionary step but in certain situation it also could not serve as savior. However, for the future, users can activate the automated backup for their Excel files, by clicking on Save as option from the File menu to save their Excel file and then clicking on Tools option to the Save as dialogue box to select the General Options from the drop-down list. Now, from the General Options dialogue box, select the checkbox saying Always create backup and click OK. It will always create a backup for Excel files from now on.
Corruption in an Excel file is identified by pop up of an error like “Microsoft Excel has stopped working” or “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupted” on accessing the XLS or XLSX file.
Prominent Reasons for Corruption & Damage in Excel File
- Over-sized Excel files
- Technical issues in the application
- Bad sectors on hard drive
- Human errors – intentional or unintentional data deletion, third-party installations, downloading malicious links online and more
- Abrupt application and system shut down
- Virus intrusions, hacking activities, malware attack
- Hardware malfunctioning
One cannot confirm the reason but can adopt different free tricks to repair the corrupt Excel file and restore the data.
Free Solutions to Repair Excel Files
First solutions can be restarting the Microsoft Excel application, rebooting the system, disabling Windows Firewall and reinstalling the Microsoft Office Suite. If doing this is in vain, proceed with the following solutions.
Use inbuilt Open and Repair option in Microsoft Excel
There is a built-in option available for repairing files in Excel.
- Go to ‘Open’ by clicking on the ‘File’ menu or ‘Microsoft Office button’ in Excel 2007 and higher versions respectively. It will open a dialogue box which will contain various files.
- Select the corrupted Excel file. Do not click on ‘Open’ rather click on its drop-down menu. Select ‘Open and Repair’ option from there.
- In case if the file is repaired for the first time through this method, another dialogue box will appear. Click Repair on it.
- If not got the results, click on the Extract option and then select from the two given options – Convert to Values and Recover Formulas.
This is the simplest method in order to repair corrupted Excel file. This method works only if you are able to open the workbook in Excel application.
In case, if the method does not work, follow these particular methods to recover the data from corrupted Excel file.
If you are unable to open the workbook in Excel, one of the following methods could be used:
Use a macro to extract the data from a corrupted workbook
A particular macro code is present which could be pasted in a different module sheet. Now in your workbook, add a new worksheet with CharData name. After this, select the chart from which the values are to be extracted. The chart selected could be any present in the workbook or on a separate chart sheet. Now run the macro. This will extract the chart values and will be available on ChartData worksheet.
Use External references to link the corrupted workbook
This method could be used to retrieve only the data from the corrupted workbook as it does not allow retrieving formulas or calculated values. Steps of this method are described here:
- Go to the ‘File’ or ‘Microsoft Office button’ and select ‘Open’ option under it.
- Select the corrupted file present in the look in box, right click on it and then select Copy. Now cancel the operation.
- Again under ‘File’ menu, select ‘New’ option and create a new workbook.
- Type XYZ!A1 in cell A1 of new workbook where XYZ is the file name of corrupted workbook.
- If any dialog box for Select Sheet and Update Values appear, click OK on it.
- Now select cell A1 and copy by using Copy option or by pressing Ctrl+C.
- Select the area corresponding to the range of cells present in corrupted workbook.
- Paste by using Paste option or by pressing Ctrl+V.
- With cells being selected, repeat the copy and while pasting select Paste Special and then select Values under it.
- Now click OK.
If you can access your corrupted workbook then follow these particular methods to recover Excel data:
Revert the workbook to last saved version
If the workbook gets corrupted while you were trying to edit it, then you can recover the original worksheet before saving your changes. For reverting the workbook to the last saved version go to the ‘File’ or ‘Microsoft Office button’ depending on the version and then click on the option of ‘Open’. Double click on the necessary file available in the look in window. Now click ‘Yes’ on the dialog box to reopen the file.
This will end up in opening the last saved version of Excel file and hence discards all the changes made after which could have resulted corruption.
Save the workbook in SYLK (Symbolic Link) format
This method helps in filtering out the corrupted elements and to remove printer corruption. Particular steps could be followed to save the workbook in SYLK format:
- Click on ‘File’ or ‘Microsoft Office button’ option and then go to ‘Save As’.
- A list will appear for ‘Save As’, click on SYLK and then select Save. This allows saving of only the active sheet in SYLK format.
- If any message appears, click ‘Yes’ on it and proceed.
- Now click on ‘Close’ under ‘File’ menu or by clicking on ‘Microsoft Office button’.
- If any message appears to save the changes click ‘Yes’ on it.
- Again go to ‘Open’ under ‘File’ menu option or ‘Microsoft Office button’.
- Make sure that in the ‘Type’ option either select ‘All files’ or ‘SYLK files’. Now select the saved SYLK files.
- Now again save the current worksheet with a new name in ‘Excel Workbook’ from ‘Save As’ menu box by making a copy of the worksheet saved in SYLK format without replacing it.
As this method allows only active worksheets to be saved, you need to perform the same steps for individual worksheets.
User can also try automatic solution to repair corrupt Excel file, Excel Repair tool can repair any large corrupt and damaged Excel file without modifying its formatting and properties.
Automatic solution is available with the free demo version. User can evaluate its performance by downloading and installing it from the software website.