Are you encountering the error message “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupted” when trying to open the MS Excel workbook, and you are not able to open the workbook? If your answer to this question is a Yes, know all about this error & its solutions.
MS Excel Error: “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupted.”
In the MS Excel application, there are chances of encountering the error mentioned above when you try to open the workbook created in it. This error is an indication of corruption in the Excel file. This makes you unable to open Excel workbook that may have significant business data. An unfavorable situation indeed!
Therefore, it becomes a prime necessity to fix the error and remove corruption. Nonetheless, to come up with a solution for this error, it is necessary to recognize the reasons for its occurrence. The next section emphasizes the reasons as well as the preventive measures for the error.
Reasons & Preventive Measures for Excel File Corruption
There are several reasons and preventive measures for Excel file corruption, and these are:
- Sudden System Shutdown – If the system in which you are working on the Excel workbook closes suddenly because of power failure, corruption may engulf the workbook. Avoid such situation by having a good power backup.
- Large-Sized Excel Files – Large-sized Excel files can become corrupt easily. The best way to avoid this corruption cause is to maintain Excel files in small sizes.
- Virus or Other Malware Attack – Attack by a computer virus or any other malware is a common cause of corruption in Excel files. To keep this cause of Excel file corruption at a distance, keep an updated and technologically advanced antivirus in your system.
- Bad Sectors in HDD – The bad sectors in the hard drive of your system can also turn the Excel files corrupt. As prevention, keep a check on your system’s hard drive and make sure that it is free of bad sectors.
- Mishandling - An Excel file can become corrupted if it is not handled correctly and as a result of which you may be unable to open Excel workbook. The best way to shun this Excel file corruption cause is handling the workbooks carefully and efficiently.
Solutions to Repair a Damaged Excel Workbook
When a corrupted Excel workbook with this error is tried to open, it fails to open, and Excel starts the ‘File Recovery’ mode on its own to repair and open the file successfully. However, Excel does not start the File Recovery mode always. In such situations, try fixing this Excel error using manual methods or automated tools. But before that create the online or offline backup of the Excel file to prevent further damage to it.
To recover data when you cannot open the Excel workbook, do one of the following:
- Change the calculation setting - Change the calculation setting from automatic to manual. With this, as the workbook will not be recalculated, it may open. To set the calculation option in Excel to manual, the steps are:
- Check if a new, blank workbook is open in Excel. If not, execute the following:
- Go to the File tab and click on the New button.
- Click Blank workbook that is under Available Templates.
- Click on the File tab and then on the Options tab.
- Go to the Formulas category, and under the several Calculation options, click on the Manual tab.
- Click on the OK button.
- Go to the File tab and click on it.
- Click the Open tab.
- Choose the damaged workbook, and click on the Open tab.
- Use external references to establish a link to the damaged workbook if you wish to recover data without formulas or calculated values from the workbook. To use external references for linking to the damaged workbook, do these:
- Click on the File tab and then on the Open tab.
- Go to the folder having the damaged workbook, copy the file name of the corrupted workbook, and then click on the Cancel tab.
- Click on the File tab, and then on the New tab.
- Go to Available Templates, and click on the Blank workbook that is under it. Go to the new blank workbook. In its cell A1, type = File Name!A1. Further, press the Enter key.
- Note 1 - File Name is the name of the damaged workbook copied in Step ii.
- Note 2 - Enter the name of the workbook and not the file name extension.
- Choose the damaged workbook and click on the OK tab. Do so, if the Update Values dialog box appears after executing Step iv. Or Choose the suitable sheet and click on the OK tab. Do so, if the Select Sheet dialog box comes up after finishing Step iv.
- Choose cell A1.
- Go to the Home tab and in the Clipboard group, click on the Copy button.
- Choose an area, beginning in cell A1.
- Note - The size of cell A1 is nearly same as the range of cells having data in the damaged workbook.
- Go to the Home tab and in the Clipboard group, click on the Paste button.
- With the range of cells that are still selected, click Copy on the Home tab that is in the Clipboard group.
- On the Home tab of the Clipboard group, click the arrow that is below the Paste button. Further, click Values that is under PasteValues.
Note – The ‘Pasting values’ step keeps the data but eliminates the links to the damaged workbook.
- Use a macro to recover data from a corrupted workbook - If a chart is linked to the corrupted workbook, use a macro to extract the source data of the chart. To use a macro, execute the following:
- In a module sheet, enter the following macro code:
(Source of Code)
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
- Insert a new worksheet into the workbook and name it ChartData.
- Choose a chart to extract the underlying data values.
- Note - The chart can be embedded in a worksheet or a separate chart sheet.
- Run the GetChartValues macro. In doing so, the data in the chart is placed on the ChartData worksheet.
- Use Stellar Phoenix Excel Repair – Use this third-party Excel recovery software that is specialized to repair erroneous workbooks created in Excel and restore all workbook data. It quickly fixes “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.” error in both Excel XLS and XLSX file, thereby making the damaged workbook a usable one. To use, download, install and run the software.
Now that you know the different methods to fix “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.” error, you can use any of them. However, this post recommends the use of Stellar Phoenix Excel Repair software if you are unable to open Excel workbook. It is the best bet as it fixes the error in just a few clicks without requiring you to perform the lengthy manual processes.