Undeleting a Worksheet

Written by Allen Wyatt (last updated March 25, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


1

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Counting Shaded Cells

Ever want to know how many cells in a worksheet (or a selection) are shaded in some way? You can create a handy little ...

Discover More

Using Header Information as the Filename

Save a document for the first time, and Word helpfully suggests a filename you can use or change. If you want this ...

Discover More

Converting a PDF File to a Word Document

PDF files seem to be everywhere; they are a standard way of exchanging documents with others. At some point you may want ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Comparing Formulas on Two Worksheets

As you develop worksheets, it is not unusual to end up with two that are essentially the same. At some point you may want ...

Discover More

Jumping to Alphabetic Worksheets

Got a workbook with a lot of worksheets in it? Here are some handy ways to jump to the worksheet you want, alphabetically.

Discover More

Shifting Objects Off a Sheet

One day you are just editing your worksheet like you normally do, then you see an error that says "Cannot shift object ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 0?

2023-03-25 11:59:18

Don Small

If you have OneDrive enabled and your workbook is saved in a OneDrive folder, it will automatically save previous versions of your Office document that can easily be restored (much like SharePoint does if you use that in enterprise work). Click the filename located in the header bar center, click Version History and select a previous version. This can be a lifesaver!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.