How to Backup and Recover Your Spreadsheets in Microsoft Excel 2016

During this Microsoft Excel 2016 training tutorial video, we will discuss the different ways in which you can backup and recover your Excel spreadsheets. We will show you how you can autosave your work, recover unsaved workbooks, autorecover a file location and create a backup copy of a file.

Like what you see? Get our complete Microsoft Excel 2016 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Video Transcript

There are various aspects of Backup and Recovery and the first one I want to look at is the question of losing your workbooks. If you, for example, work on a laptop and the only copy you have of your workbooks is on your laptop and you’re away from home and your laptop is stolen or you lose your laptop and the only copy you have of those workbooks is on the laptop then you’re going to be in big trouble. If you store your workbooks in the Cloud then to a large extent this is no longer a problem, although there are intrinsic dangers there as well which I’ll refer back to later. But if you save your workbooks on the device and that’s the only copy you have then you really could finish up in quite a bit of trouble.

Now you have a number of options but basically you need to keep a backup copy of each of the workbooks that has any value to you on a different device to the one that you work on them with. Now this might be an external hard drive, perhaps just USB drive that plugs into the device but is then kept somewhere separately or if you have access to a computer network you may transfer it onto another device on the network. But it is very important that that backup copy of each of your workbooks is on a different device so if the original version is lost or stolen you still have a backup copy available.

Now I’m not going to go into how to setup a sort of backup regime on this course but you should get into a routine process for backing up your workbooks. Perhaps you just take a backup of each workbook at the end of each working day or even after you’ve worked on a workbook you put a copy of it on a different device. The most important thing is that you make sure that if the worst did happen, so if your laptop was stolen, for example, that you have a recent enough copy that you wouldn’t face a huge problem restoring the content of the stolen workbook.

Want More? Get Started With a Free Excel 2016 Course! Click Here

Now the next situation to consider is one where Excel or your device fail. If this happens you may have been working on a workbook for a period of time, made a lot of changes, enter some data, the device fails. Perhaps the electricity fails or Excel itself crashes. Potentially you’ve lost all of that work. As I mentioned earlier on in the course you should normally have AutoSave enabled. You do that from Backstage View in the Excel Options. On the Save Page one of the options there, Save AutoRecover information, in my case every 10 minutes. That’s the default. Ten minutes is the figure that I normally stick with. It means that at worst case I’m going to lose 9 minutes and 59 seconds worth of work. If you find that the AutoSave process, perhaps if you’re working on a very big and complex workbook, is actually slowing things down too much for the time that it takes to happen you may want to increase that figure. If you do of course, if you do go up to I think the maximum is 120 minutes then potentially you could lose that much work. So only really increase that number if it’s giving you some problems.

There’s an option below that, Keep the last AutoSave version if I close without saving. That’s a very interesting option because most of us at some stage when we’ve closed a workbook or perhaps a document in another program such as Word when it says, “Do you want to Save changes?” most of us at some stage have said No accidentally and gone, “Oh no I made all those changes and then when I closed it I said No to saving the changes.” Well nowadays if I have made some changes say to one of my workbooks and I accidentally close without saving changes, let me just cancel that. When you come to do an Open at the bottom of the page here you’ve got this option, Recover unsaved workbooks. And you see the Tool Tip there, Open a recent workbook that was closed without saving. That gives you the option of getting at those versions, the ones where you’ve lost work because you closed without saving.

So that little group of options are very important. Let me just go in there for one other thing. Back to the Save Page again. AutoRecover file location. The AutoRecovery files are saved in this location by default, my username TobyA. Here your username will be in your version of that. If you’d like to save those somewhere else then you can change that location.

Now there’s one other aspect of Backup which is something that I used to do. I don’t any longer but I used to. I know some people have done it in the past, some of them still do. And that is that there is a facility in Excel to always save a backup copy of a document when you save the document. But this copy is saved in the same location so it doesn’t fulfill the same purpose as having, for instance, an offsite backup or a backup on a different device. So this approach just saves a copy with a particular name in the same folder as the workbook itself. Some people quite like this approach. And all you have to do with this is when you do a Save, let’s suppose I take my Business Expenses spreadsheet here and I do a Save As but with this, with my Save As if I go to the Tools dropdown to the left of the Save button and into General Options one of the options is Always create backup. Click on OK, click on Save, I’m only replacing the existing version. Now what you’ll notice is I have a new file there called Backup of Business Expenses.xlk. So that is a backup version of my workbook. So I’ve got a local backup copy that I could go back to if I need to.

Want More? Get Started With a Free Excel 2016 Course! Click Here

The last thing I’d like to mention here is in relation to Cloud storage, and I did say I’d come back to this point. Cloud storage is fine. You need to make sure that your Cloud storage provider takes backups of your work so that you know that you have backup copies. It’s a good idea to know how frequently those backup copies are made as well. The other thing to bear in mind, particularly say if you had a failure and you needed to go back to a backup copy and you are relying on Cloud storage is whether or not you always have your Cloud storage available. So if you sometimes or frequently work offline, so you have no internet access, just think what would happen if you needed to get to a backup copy of a file and you didn’t have internet access. Now for many of you you may say well I’d just wait until I got internet access. There’s not really a problem. But it’s just worth thinking through the consequences of that and whether that could create you a problem in some situations.

Okay so that’s it on Backup and Recovery. I’ll see you in the next section.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!