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

Document Shows as 'In Use' by Another User

Word tries to constantly track who is using various documents, in order to prevent two users from clashing in their edits ...

Discover More

Reversing Print Order

When you print a document, does it come out of the printer in the order you need? Here's how to reverse the print order ...

Discover More

Writing On Top of Locked Graphics

Getting graphics to appear right where you want them in relation to the text in your document can be a challenge. One ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Condensing Multiple Worksheets Into One

Want a quick way to combine your worksheets? This tip features a simple macro to do the task for you.

Discover More

Freezing Top Rows and Bottom Rows

Freezing the top rows in a worksheet so that they are always visible is easy to do. Freezing the bottom rows is not so ...

Discover More

Disabling Moving Between Worksheets

If you want someone to not be able to move from one worksheet to another in a workbook, you've got your work cut out for ...

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 seven more than 6?

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.