Recovering Deleted Data from Excel 2016: Quick Guide

In the realm of data management, Excel 2016 stands as a cornerstone for countless professionals, providing robust tools for organizing, analyzing, and interpreting data. However, even the most vigilant users can encounter the unfortunate mishap of accidental deletion of important data. The aim of this guide is to provide a comprehensive yet concise set of instructions for recovering deleted data from Excel 2016, ensuring you can restore your invaluable information swiftly and effectively.

Understanding AutoRecover

Before delving into the steps to recover deleted data, it’s crucial to understand Excel’s AutoRecover feature. This built-in tool is designed to automatically save your work at regular intervals, providing a safety net against data loss due to crashes or unintended closures:

• Excel saves your workbook periodically based on the interval set in the AutoRecover settings.
• The default saving interval is usually set to 10 minutes, but it can be customized.
• It does not protect against data you have saved over or deleted deliberately, but it helps recover the last unsaved changes before an unexpected event.

Steps to Enable AutoRecover

To ensure you leverage this feature effectively:

1. Open Excel and go to File > Options > Save.
2. Check the box next to “Save AutoRecover information every X minutes.”
3. Set the desired interval.
4. Ensure “Keep the last autosaved version if I close without saving” is checked.

Recovering Unsaved Workbooks

If you have lost your work due to a crash or closing the application without saving, follow these steps:

• Open Excel 2016.
• Click on File > Info.
• Under the “Manage Workbook” section, click on “Recover Unsaved Workbooks.”
• A list of unsaved files should appear; choose the file you wish to recover.

❗ Note: Unsaved files are usually stored in the XLSTART folder or “Unsaved Files” folder within your User directory.

Retrieving Data from Versions

If you’ve accidentally overwritten data, Excel’s version history can be your savior:

1. Open your workbook.
2. Go to File > Info and look under “Manage Workbook.”
3. Click on “Versions” and select the version you wish to recover from.
4. Choose the desired version to open, review the changes, and copy the necessary data back into your current workbook.

Utilizing Cell History

Here’s how you can use cell history to recover deleted entries:

1. Select the cell or range where the data was deleted.
2. Right-click and choose “Track Changes” then “Highlight Changes.”
3. Set the filter to “All Changes” and uncheck “Track changes while editing.”
4. Once the changes are highlighted, use “Undo” to restore the deleted data, or manually input the data back if it’s listed under “List Changes on a New Sheet.”

Data Recovery via Macro

In cases where the above methods fail, a VBA macro can sometimes help:

Sub RecoverLostData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘Change to the appropriate sheet name
    ws.UsedRange.Cells.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = “=IF(COUNTA(R1C1:R1C1)=0,1,”“)”
End Sub

This script fills blank cells with 1 where there was previously data, based on the cell’s history of containing formulas or values. After running the macro, you can then manually restore data from the last version where it was present.

🗒️ Note: Macros can be risky if not properly reviewed, especially from external sources. Always backup your workbook before using any macro.

To wrap up, the key to successful data recovery in Excel 2016 lies in understanding and leveraging the tools at your disposal. AutoRecover, version history, and cell tracking provide layers of protection against data loss, offering you multiple avenues to recover lost information. While these methods don't guarantee a 100% recovery rate, they significantly increase your chances of restoring what's been lost.

Can I recover a deleted Excel file from my recycle bin?

+

If you have inadvertently deleted an Excel file, check your recycle bin first. If it’s there, right-click the file and choose “Restore” to return it to its original location.

What if the Excel autosave files are not found?

+

If you can’t find the autosaved versions, your AutoRecover settings might be incorrect, or the autosave feature might have been disabled. Check and adjust your settings under File > Options > Save.

How often should I save my Excel files to prevent data loss?

+

While Excel’s AutoRecover can save automatically, it’s good practice to save manually every 5-10 minutes or when significant changes are made to ensure your latest work is secured.