Learn how to recover "lost" Excel workbooks in addition to methods to recover data from damaged presentations including: reverting the workbook to the last saved version, setting the calculation option to manual and using external references to link to the corrupted workbook.
- [Instructor] If you happen to lose your Excel file, the steps to locate this missing file are going to be very similar to the steps that we've already explored in some of the other Office applications. As always, let's go ahead and start off with File Explorer. And I like to start off with a narrow search of where I think that file might be. In our example, I'm going to narrow down the search to Documents. Then I'm going to use the asterisk which means search for everything with the extension of xlsx which is the Excel 2000 file extension.
And as you can see here, we have a list of all of our Excel documents. If you cannot find your file using this method, another option is to actually check the auto recover folder. But before you do that, you need to know where that auto recover folder is located and the easiest way to figure that out is to open Excel, open a new blank workbook and go ahead and click File, Options, and then Save. And here we have our information about the default saving of our workbooks.
As you can see, we have an auto recovery of every 10 minutes. I'm going to drop that down to five. My personal favorite, keep the last auto recovered version even if I close without saving, and finally the location for our auto recover files. You may have noticed that the browse button is not here as it is in Word. In this case to open the folder, you can either manually browse to the folder or you can copy the address as I'm going to do here, click your Windows key, type Run, select Run, and then paste the location and what this will do is open up that location for you and then you'll see your previous copies.
Okay, that takes care of your lost files, but what if a document has been damaged and you need to repair it? The easiest way to do so again is to open up Excel, click on File, Open, and the trick here is you must click on Browse. I'm going to go ahead, select a file, and then instead of clicking on Open, I'm going to click on the down arrow and then select Open and Repair. As we can see here, we popped up with a little box letting us know that we can repair as much of that workbook as possible or we can just go ahead and extract the data.
In our example, we're going to go ahead and click Repair and our workbook opens for us. You can also revert to the last workbook that you saved as long as you have not saved the current version as of yet. And I'm just going to go ahead and enter a line in here. I'm not going to format it or anything. I'm just going to add it in. Now, at this point, I could go ahead and close my workbook, but the trick here is don't save your changes and then the London change will not be saved.
You would normally only use this method if the workbook that you're currently working on becomes corrupted while you're in it. I'm going to go ahead and click Cancel on that. Did you know that every time you open a workbook, all the calculations in that workbook are performed at the time of opening? If you can't open a specific workbook, you can try to recover the data by setting the calculation option in Excel to manual. And by setting this calculation option to manual, the workbook may open because those calculations are not being run.
Let's go ahead and show you how to do this. I'm going to do it from an open workbook, but you could do it from a blank one. I'm going to click on File, Options, and then Formulas and you'll notice here that we have workbook calculation turned to automatic. If I set it to manual and now click OK, the next time I go to open up a workbook, those calculations will not be performed. So let's go ahead and open up that workbook that we just had open.
If there were calculations in here, if they would not have been run, we could pull the data and then we could try manually running the calculations. Finally, to recover damaged data, you can use external references to link to the damaged workbook. Using this method will pull only the data, but not the formulas. To use this method, you'll need to start off with a blank workbook. We'll use book one and here is the trick. In cell 1A, we are going to reference the damaged workbook.
To do so, use the equal sign, enter in the name of the damaged workbook, in our example it will missing items, the exclamation mark, and then A1. Go ahead and click Enter. You'll notice here that we have the word month pop up and that's because it's pulling the contents from A1 from our workbook missing items. So I'm going to go ahead and expand the number of columns, pull down for our number of rows, and you'll notice that all the data is now in our new workbook.
This is a great way to recover data from a damaged workbook. For more tips, tricks, and troubleshooting techniques using Excel, please refer to the Excel Support Center.
- Troubleshooting printing and startup issues
- Recovering lost and damaged files
- Fixing compatibility issues
- Troubleshooting Skype audio and video issues
- Accessing SharePoint sites
- Syncing files
- Connecting to email