MrExcel.com - Your One Stop for Excel Tips & Solutions

How to Repair a Corrupt Excel File?

January 24, 2018 - by

How to Repair a Corrupt Excel File?

It can happen: the long-running Excel file that has all of your information suddenly won't save or won't open. Excel tells you that they are going to attempt a repair, but that repair either does not work or it removes all of your macros.

What do you do?

First Step:

Try opening the workbook in a later version of Excel

Yes - I get it. Your I.T. department still has you working in Excel 2013. But what do you have at home? You are probably running Office 365 at home. In my experience, 90% of the time that a file becomes corrupted in Excel 2013 and Microsoft can not repair the file, my home computer running Office 365 will happily open the file.

Here are the steps:

  1. Close the workbook in Excel 2013 without saving. You want to go back to the last good version of the file.
  2. Copy that file to a USB drive or Dropbox.
  3. Take the workbook to the computer running Office 365.
  4. Save the workbook to the hard drive.
  5. From Excel 2016 in Office 365, do File, Open. Does the file open correctly? If it does, continue with step 6.
  6. Once the workbook opens correctly, do File, Save As and save the file with a new name. Close the workbook.
  7. Transfer the workbook back to your Work computer. It should open without a problem in Excel 2013.

It is awesome that Excel 2016/Office 365 can repair those files that Excel 2013 can not. You don't hear about this a lot, but it is a huge benefit of being on Office 365.

Step 2:

Try to pull the data from the corrupt workbook using Power Query.

This gets the data, but not the formatting or formulas.

This video will show you how:

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2183: Load Data from a Corrupt Excel Workbook.

If you're watching this video, I feel bad for you. I could be in the same boat right now.  This repaired file is not repaired at all. This file, I've been working on this file all weekend, and I can open it, I can see the data. But, the thing is corrupt. See, I have two sheets. No formulas here, just data-- tons and tons of data-- data out to column L, down to row 214-- lots of work. And any time I try to do anything with this, the file crashes. In fact, even if I just try and select the data to copy it, to take it somewhere new, Ctrl+C, Microsoft Excel has stopped working. This thing is just lost.

Alright, here's my approach when I have a corrupt workbook. The very first thing I try and do, especially if I'm working in Excel 2013, is take that workbook to the computer that has 2016 and open it there. Nine times out of ten, that solves the problem. Excel 2016 can deal with corrupt data better than Excel 2013 can. Although, in this case, I'm already in Excel 2016, in the latest version of Excel 2016, so, that's not going to help.Try to copy and paste to a new workbook; as you just saw, that's not going to work.

Step three: Power Query. We're going to cover that in this video.

But, today we don't have to do that because I'm going to be able to solve this with Power Query. Ctrl+N for a new workbook. I'm in Excel 2016. This only works in Windows; if you're on a Mac, I'm so sorry for you. If you're in Excel 2010 or Excel 2013, you're going to download Power Query for free from Microsoft. So, here's what we're going to do: Excel 2016 Data tab; I'm going to Get Data (this is in the Get & Transform Data); Get Data From a File; From a Workbook; find the file; click Import, and they show me the two lists. The first worksheet I had was VideoList, and that second worksheet was RecapWork. So I'm going to choose VideoList; they show me my data; I'm just going to simply click Load. Alright, beautiful. It comes in in a table, and I really don't want it as a table, but that's easy enough. Select all this data; Ctrl+C (actually, I got too many columns there...Ctrl+C...); go to a brand new workbook, Ctrl+N; and then Paste Special Values. That was Alt+E+S+V-- there's many different ways to do that, use your favorite; and all we have to do is change the column widths, and then here this date has to be a short date, and then here-- this is also short date-- press F4.  Alright, so now I'm going to save this: File; Save As; "RecoveredYoutubeArticleList." 

Alright. We got the first half back. Now, let's see. Let's do Ctrl+N again; Data; Get Data; From a File; From a Workbook; go back to the exact same workbook, the corrupt workbook; this time, I want to go to the other worksheet-- the recap worksheet; again, just click Load; the data comes back; select the data; Ctrl+C; and I guess I can come here to my recovered file; Insert a new worksheet; Alt+E+S+V to Paste Special Values; and that data is back. Now, you're going to lose formulas. You're going to lose links.  But for me, right now, today, all I wanted was the data back! There were no formulas in there! I just didn't want to have to recreate this data that I spent all weekend working on.

That worked for me, alright? Most of the time, just opening Excel 2016 solves it. Try to copy and paste to a new book-- that didn't work. Power Query, today, solved it. If none of those work, then have $39.00-- Stellar Repair for Excel will be able to recover that data from your workbook. Check the link down there in the YouTube description. 

Episode recap: If you have a corrupt Excel file, the best thing to do is try and get to a newer version of Excel, open the workbook there, see if you can save it-- removes the corruption. If that's not going to work, see if you can copy the data from Excel; paste to a new workbook; be sure to save that new workbook before you come back and close the old one. Lots of times, it's when you Close that the file will crash. Third method (from this episode)-- using the Data tab, look for Get & Transform Data, and then Get Data. It uses Power Query. If you're in 2010 or 2013, you can download Power Query for free from Microsoft. If you're on a Mac-- sorry, no Power Query. And then step number four: If nothing else works, then Stellar software here-- this URL down in the YouTube description, about $39 bucks-- usually will be able to help get at least most of the data back.

Well, hey, I want to thank you for stopping by. Good luck with your file. We’ll see you next time for another netcast from MrExcel.

Option 3:

If All Else Fails, You Can Examine the Underlying XML for Your Spreadsheet

Most Excel files are stored with an XLSX file type. Here at MrExcel, we only use XLSM file types. There is a slim chance that you are using XLS or XLSB, in which case you are out of luck. But if you are using XLSX or XLSM, there is a crazy trick to see inside the file.

This is not for the faint of heart! It is crazy-techie stuff. Don't try this on a "good" Excel file or you can very easily corrupt things.

Say that you have this file in Excel:

  1. Find the file in Windows Explorer

  2. If you can not see the file extension, go to Tools, Folder Options. On the View tab, find the setting for Hide Extensions for Known File Types and uncheck it.

  3. Rename the workbook from FileName.XLSX to FileName.xlsx.zip
  4. Windows will warn you that you are about to make the file unusable. Say that you are okay with that.

  5. Open the Zip file and you will see folders and items. You want to double-click on the XL folder.

  6. Inside the XL folder, open the SharedStrings.xml using NotePad. You will see all of the unique text in the workbook.

It is going to be a nightmare to convert that XML back to the original workbook. Our friends over at ProfessorExcel have a document detailing where to find items inside the zip file.

But let me reiterate. This is not going to be easy. Go to Step 3 only if Steps 1 & 2 fail.

Title Photo: falcon4 / pixabay