We all use Excel for executing formulas, filtering, summarizing, or consolidating large amounts of data. This “much appreciated” software is used by people of all trades like Finance, Marketing, Advertising, HR, Manufacturing, Sales, etc. What would happen if your crucial excel file gets corrupted for any reason? Buggered I suppose? Well, before you face such a nightmare, read this article on how to recover the contents of a corrupted Excel file.
Ways to Repair Corrupted Excel files
If an Excel file gets corrupted, then all the data in the file would get lost and you will have to put in all the effort all over again to recreate that big file of yours. What if you could repair Excel file?
It would save you a lot of time and effort if the file is recovered. But, have you ever wondered how would you repair a corrupted file? Luckily, Excel has inbuilt tools to recover a corrupted file. Here are a few ways I have tried in order to recover the data from a corrupt file.
Method 1: Open the file in Repair Mode
The first thing you must try is to open the corrupted file with the repair option. To do so, you should open the Open Dialog Box by clicking on the Open option in the File menu. Alternatively, you can use the Ctrl + O shortcut keys.
In the Open Dialog Box, select the file you want to repair and click on the small arrow on the Open button. Here, many options will be displayed to you. Now, select the Open and Repair… option.
Once you click this option, the following dialog box will appear in front of you.
Here, you will get two options.
- Repair Option: – You should select this option if you wish to recover as much data as possible.
- Extract Data Option: – You should select this option if you wish to extract the formulas or values from the workbook.
Note: Use the repair option prior to using the extract data option in order to recover maximum data.
You will have to wait for some time for your file to open. If recovered successfully, the file opens. Now, you can save it to your PC by using the save as option from the file menu.
Method 2: Changing the File Type
File types have their own importance. Sometimes a file created in an older version of Excel becomes unstable for use in newer versions. So, another way to recover the corrupted file is to save it in another file format.
Excel supports various file formats like xlsx, htm, pdf, slk, txt to name a few. In order to rename the file with a different extension do as follows:
- Open file explorer. Locate your file in explorer.
- On the top Excel menu, click on the View button. The following toolbar will appear on the screen.
- Next, click on the File Name Extensions option in the Show/hide section.
- On clicking this, file names will be displayed along with their extensions in the file explorer.
- Now, select the file and click on the Home Tab.
- In the home tab select rename. Now, change the extension of the file to an extension that is compatible with Excel. If it is already xlsx then try converting it to xls or HTML.
- Now, try opening the file in Excel. If it opens save it again using the save as In this way, your file is recovered successfully.
Method 3 – Opening the document in Word or WordPad
If you are still not able to open your file in Excel then try to open it in MS Word or WordPad. Opening the file in either of these will leave you only with values in your file. You are bound to lose all data stored in the form of charts, dialog sheets, macro sheets as well as formulas.
But, if you open the file in WordPad then there is a chance that you might be able to save your macros as WordPad might show the Visual Basic code in the modules.
Method 4: Set the Calculation option to manual
If you are still not able to recover your corrupted file, then try to change the calculation settings in Excel from automatic to manual. Changing this setting will be of great help as the workbook will not be recalculated so there is a chance that it might open.
To do so, just follow these steps:
- Click on the Office icon and next click on the button named Excel Options.
- Click on the Formulas Here select the Manual Option in the Calculation Option.
- Now, click OK.
- After changing the setting try to open your file.
If you are still not able to open the file, then go to the next method.
Method 5: Use Excel Viewer
Try using Excel Viewer to try to open your file. Excel Viewer is a tool also developed by Microsoft which allows you to open, view, print, and copy data from one spreadsheet to another. This functions well even if you do not have Excel installed on your system.
Hence, if the file opens in Excel Viewer, then you can copy & paste the contents of your workbook to a new workbook. And there you have your data again.
Method 6: Use External Reference to Link your Corrupted Workbook
This method will help you retrieve values only from your workbook. This method will not help you bring back your formulas or calculations from your workbook. But, something is better than nothing.
So in order to retrieve values from your workbook, follow the following steps:
- Open a new blank workbook where you want to create a link to your corrupted file.
- Now, in cell A1 of this new workbook type =File_Name!A1, where File_Name is the name of your corrupted file (without mentioning the extension). After typing it, press Enter key.
- For example: if the name of your corrupted file is merge.xlsx then you need to type =merge!A1 and press the enter key.
- On pressing Enter the following dialog box will appear on the screen and you will be prompted to select the name of the sheet from which you want to extract the data.
- If everything goes well, then on clicking Ok, a link between the new workbook and the old workbook will be formed and the contents of the first cell i.e. the contents of cell A1 of the corrupted worksheet will be displayed in the new worksheet.
- Now, you can simply drag the corner of the box and drag it to expand the selection. And you will be able to see the contents of the corrupted workbook.
- Go ahead and save the values of the new workbook.
Method 7: Try using a new version of Excel
If none of the above-mentioned ways work for you then you can also try using a newer version of Excel to open the file for you. This might work for you as the newer versions have more ability to recover files than the previous versions.
Method 8: Delete contents of the Temp Folder
If nothing has worked till now, then try to delete the contents of the temp folder. This folder is present at C:\Windows\Temp location. Try deleting all the temporary files it contains and restarting your computer. Once your computer starts, try opening your file in Excel now.
Some of the ways discussed above would help you recover all of the data from the file. While others would help you recover a part of it in the form of values only. But still, if you are unable to open your Excel file, you can send me a query and I will try to resolve it for you.