Microsoft Excel contains a feature that allows you to recover data from corrupted workbooks. This article describes how to use the registry to override the default behavior when Excel tries to recover data.

Back to the top

When you open a workbook in Excel, Excel checks for problems in the workbook. If Excel detects a problem in a workbook, the data recovery process begins automatically.

Note You can also manually start the data recovery process. To do this, click Open on the File menu, click the arrow on the Open button, and then click Open and Repair.

All the registry values that affect data recovery are in the following registry subkeys, depending on which version of Excel you are running:

Microsoft Excel 2002

Microsoft Office Excel 2003

Microsoft Office Excel 2007

These subkeys contains three DWORD registry values that you can modify as follows.

Note In these subkeys, 2 is always the default value, and 1 is the only other valid value.

Back to the top

ExtractDataMode

If ExtractDataMode equals 2 (or a value that is not valid), Excel assumes that the structure of the workbook is not corrupted. Therefore, Excel tries to recover the whole workbook, including formulas, formatting, and Microsoft Visual Basic for Applications (VBA) projects.

If ExtractDataMode equals 1, Excel assumes that the structure of the workbook is corrupted. Therefore, Excel does not try to recover anything other than the data in the workbook.

To change the value from 2 to 1, follow these steps.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

1.	Start Registry Editor.
2.	Locate the following registry subkey, as appropriate for the version of Excel that you are running: Excel 2002 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\10.0\Excel\Options Excel 2003 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Options Excel 2007 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Options
3.	In the list of registry entries, double-click ExtractDataMode.
4.	Type 1 in the Value data field, and then click OK.

Back to the top

ExtractDataFormulas

If ExtractDataFormulas equals 2 (or a value that is not valid), Excel tries to recover formulas and converts a formula to a value if recovery does not succeed.

If ExtractDataFormulas equals 1, Excel recovers as much of the formula as possible, and substitutes #REF if recovery does not succeed. This value also affects the default option when you are prompted to recover formulas during the Open and Repair process.

To change the value from 2 to 1, follow these steps:

1.	Start Registry Editor.
2.	Locate the following registry subkey, as appropriate for the version of Excel that you are running: Excel 2002 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\10.0\Excel\Options Excel 2003 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Options Excel 2007 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Options
3.	In the list of registry entries, double-click ExtractDataFormulas.
4.	Type 1 in the Value data field, and then click OK.

Back to the top

ExtractDataDisableUI

If ExtractDataDisableUI equals 2 (or a value that is not valid), Excel prompts you with two messages during the Open and Repair process. The first message prompts you to choose either to repair or to extract data. The second message prompts you to choose either to convert to values or to recover formulas.

If ExtractDataDisableUI equals 1, Excel does not offer you any options during the Open and Repair process. Excel opens the file by using the Safe Load process. Data extraction is still enabled through the object model and through automatic data recovery.

To change the value from 2 to 1, follow these steps:

1.	Start Registry Editor.
2.	Locate the following registry subkey, as appropriate for the version of Excel that you are running: Excel 2002 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\10.0\Excel\Options Excel 2003 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Options Excel 2007 HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Options
3.	In the list of registry entries, double-click ExtractDataDisableUI.
4.	Type 1 in the Value data field, and then click OK.

Back to the top