Excel Settings in the Registry

The Excel Options dialog box has dozens of user -specified options. Excel uses the Windows Registry to store these settings and retrieve them when Excel is started. In this section, I provide some background information about the Windows Registry and discuss how Excel uses the Registry to store its settings.

About the Registry

The Windows Registry is essentially a central hierarchical database that is used by the operating system and by application software. The Registry first appeared in Windows 95 and replaces the old INI files that stored Windows and application settings.

CROSS-REFERENCE  

Your VBA macros can also read and write information to the Registry. Refer to Chapter 11 for details.

You can use the Registry Editor program to browse the Registry - and even edit its contents if you know what you're doing. The Registry Editor is named regedit.exe . Before beginning your explorations, take a minute to read the upcoming sidebar (titled "Before You Edit the Registry "). Figure 4-9 shows what the Registry Editor looks like.

Figure 4-9: The Registry Editor lets you browse and make changes to the Registry.

The Registry consists of keys and values, arranged in a hierarchy. The top-level keys are:

• HKEY_CLASSES_ROOT

• HKEY_CURRENT_USER

• HKEY_LOCAL_MACHINE

• HKEY_USERS

• HKEY_CURRENT_CONFIG

• HKEY_DYN_DATA

Excel's settings

Information used by Excel 2007 is stored in this Registry section:

 HKEY_CURRENT_USER\Software\Microsoft\Office.0\Excel 

Before You Edit the Registry

You can use the regedit.exe program to change anything in the Registry, including information that is critical to your system's operation. In other words, if you change the wrong piece of information, Windows may no longer work properly.

Get into the habit of choosing the File Export command in Regedit. This command enables you to save an ASCII version of the entire Registry or just a specific branch of the Registry. If you find that you messed up something, you can always import the ASCII file to restore the Registry to its previous condition (choose the Registry Import Registry File command). Refer to the Help file for Regedit for details.

 

In this section of the Registry, you'll find a number of keys that contain specific values that determine how Excel operates.

The Registry settings are updated automatically by Excel when Excel closes .

Note  

It's important to understand that Excel reads the Windows Registry only once - when it starts up. In addition, Excel updates the Registry settings only when Excel closes normally. If Excel crashes ( unfortunately , not an uncommon occurrence), the Registry information is not updated. For example, if you change one of Excel's settings, such as the visibility of the Formula bar, this setting is not written to the Registry until Excel closes by normal means.

Table 4-6 lists the Registry sections that are relevant to Excel 2007. You might not find all these sections in your Registry database.

Table 4-6: EXCEL CONFIGURATION INFORMATION IN THE REGISTRY
Open table as spreadsheet

Section	Description
Add-in Manager	Lists add-ins that appear in the Add-Ins dialog box. Add-ins that are included with Excel do not appear in this list. If you have an add-in entry in this list box that you no longer use, you can remove it by using the Registry Editor.
Converters	Lists additional (external) file converters that are not built into Excel.
Error Checking	Holds the settings for formula error checking.
File MRU	Holds information about the most recently used files (which appears in the Recent Documents list when you click the Office button).
Options	A catch-all section; holds a wide variety of settings.
Recent Templates	Stores the names of templates you've used recently.
Resiliency	Information used for recovering documents.
Security	Specifies the security options for opening files that contain macros.
Spell Checker	Stores information about your spelling checker options.
StatusBar	Stores the user choices for what appears in the status bar.
UserInfo	Stores information about the user.

Although you can change most of the settings via the Excel Options dialog box, a few settings cannot be changed directly from Excel (but you can use the Registry Editor to make changes). For example, when you select a range of cells, you may prefer that the selected cells appear in high contrast white-on-black. There is no way to specify this in Excel, but you can add a new Registry key like this:

1. Open the Registry Editor and locate this section:

    HKEY_CURRENT_USER\Software\Microsoft\Office.0\Excel\Options 

2. Right-click and select New DWORD Value.

3. Name this value Options6 .

4. Right-click the Options6 key and select Modify.

5. In the Edit DWORD Value dialog box, click the Decimal option and enter 16 (see Figure 4-10).

Figure 4-10: Setting a value for a Registry setting.

When you restart Excel, range selections will appear with a black background rather than gray. If you don't like this look, just delete the Options6 Registry entry.

Tip  

If you have trouble starting Excel, it's possible that the Registry keys have become corrupt. You can try using the Registry Editor to delete the entire Excel section:

  HKEY_CURRENT_USER\Software\Microsoft\Office.0\Excel  

The next time Excel is started, it will rebuild the Registry keys. You will, however, lose all of the customization information that was stored there.