Undeleting a Worksheet

Terry has a workbook that has dozens of worksheets within it. He deleted one of the worksheets by mistake. (He had intended to delete a different one.) Terry wonders if it is possible to undo the deletion and recover the information in the worksheet.

The answer is that it may be possible, with the primary determining factor being what you have done since the deletion occurred. If you deleted the worksheet and immediately realized your mistake, then the best bet is to close the workbook without saving it. Open it back up again, and the worksheet will be there. Of course, any changes you may have made between your last save and the point when you closed the workbook will also be lost, but I'm betting that the possible loss of the worksheet data is the bigger issue.

One variation on this "don't save" approach is that you could, right after the worksheet deletion, use Save As to save the workbook. This leaves the original workbook, with the now-missing worksheet, intact on your system. Open that workbook and copy or move the missing worksheet to the worksheet on which you used Save As. The only thing that won't be rescued in this approach is any cross-worksheet formulas that involved the deleted worksheet. If you have those in your workbook, then you are better to skip Save As and go with the first approach mentioned earlier.

What if, after deleting the worksheet accidentally, you saved your workbook? Then things get a lot more iffy. You obviously can't use either of the techniques already discussed because the original, worksheet-not-deleted version of the workbook is no longer immediately available.

The first thing you can do is to check to see if there is an AutoRecover version of your workbook still available. Do this by clicking the File tab of the ribbon to display Excel's back office. (See Figure 1.)

Figure 1. The back-office area for Excel.

Note that next to the Manage Workbook icon there is a list of a few AutoRecover versions of the workbook. You can click on one of these to recover it, or you can click on Manage Workbook to track down more versions.

If you do this and you don't see any AutoRecover versions available, it may mean that you don't have AutoRecover enabled on your system. With the back-office area still visible, click the Options link at the left side of the screen. Excel displays the Excel Options dialog box, and you should click the Save option at the left side. (See Figure 2.)

Figure 2. The Save settings of the Excel Options dialog box.

When it comes to AutoRecover, there are two settings you need to pay attention to. The first is the Save AutoRecover Information Every option, which turns AutoRecover on and off and allows you to specify how often the information should be generated. Excel will only generate AutoRecover files if you have this option turned on. The second option is the Disable AutoRecover for this Workbook Only check box, near the bottom of the available options. If this check box is selected, then Excel won't generate AutoRecover files for this specific document.

In looking at the back-office area of Excel, you may have noticed that there is an option just above the Manage Workbook icon called Versions. This provides another way for Excel to maintain past versions of your workbooks using OneDrive. If you have this versioning capability turned on, then you can recover your workbook pre-worksheet-deletion by simply selecting an appropriate version and restoring it.

If none of these options provides access to the worksheet, you can try to use the File History feature of Windows. Get out of Excel entirely (exit the program) and then press Win+E to open a Windows Explorer window. Navigate to the folder that contains the workbook in which you deleted the worksheet. Click once on the workbook, and then click the History tool near the upper-right corner of the Explorer window. Provided you have File History enabled on your system, Windows will show you past versions of the workbook. If you don't have File History enabled, then you'll be out of luck. You might consider turning it on, though, for possible future needs. You can find more information about File History at this WindowsTips page:

https://tips.net/T775

If you do have it turned on and you see a past version of the workbook you want to use, then you can recover the workbook from the backups automatically maintained by File History. Click the Previous button to locate and choose the version to recover. Then click the green Restore button to get access to the backup.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8209) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.