Chapter 4: Understanding Excel Files

IN THIS CHAPTER

• Starting Excel

• Opening and saving different types of files in Excel

• Introducing the XML file format in Excel 2007

• Figuring out how Excel uses the Windows Registry

Starting Excel

You can start Excel in various ways, depending on how it's installed. You can click an icon on the desktop, use the Windows Start button, or double-click a file associated with the Excel application. All methods ultimately launch the excel.exe executable file.

When Excel 2010 starts, it performs the following actions:

• It reads its settings stored in the Windows Registry.

• It reads and applies any Quick Access toolbar or Ribbon customizations defined in the Excel.officeUI file.

• It opens the *.xlb menu/toolbar customization file.

• It opens all add-ins that are installed (that is, those that are checked in the Add-Ins dialog box).

• It opens any workbooks that are in the XLStart directory.

• It opens any workbooks that are in the alternate start-up directory (specified in the Advanced tab of the Excel Options dialog box).

• It determines whether Excel ended with a crash the last time it was used. If so, it displays a list of autorecovered workbooks.

• It displays an empty workbook — unless the user specified a workbook to open or one or more files were found in the XLStart or alternate start-up directory.

You can install Excel in any location. But in most cases, the Excel executable file is located in the default installation directory:

C:Program FilesMicrosoft OfficeOffice14EXCEL.EXE

You can create one or more shortcuts to this executable file and customize those shortcuts' various parameters, or command line switches. Table 4-1 lists these command line switches.

Table 4-1: Excel Command Line Switches

Switch	What It Does
filename	Opens the specified file. The filename is a parameter and does not require a switch.
/r filename	Opens the specified file in read-only mode.
/t filename	Opens the specified file as a template.
/n filename	Opens the specified file as a template (same as /t).
/e	Starts Excel without creating a new workbook and without displaying its splash screen.
/p directory	Sets the active path to a directory other than the default directory.
/s	Starts Excel in Safe mode and does not load any add-ins or files in the XLStart or alternate start-up file directories.
/m	Forces Excel to create a new workbook that contains a single Microsoft Excel 4.0 macro sheet (obsolete).

You can experiment with these command line switches by using the Windows Start⇒Run command (or use the Search box to start the Windows Run program). Put the path to Excel in quotes, followed by a space and the command line switch. Figure 4-1 shows an example.

One way to specify any of these switches is to edit the properties of the shortcut that starts Excel. For example, if there are times when you'd like Excel to start and use a folder named c:xlfiles as its default folder, you can customize a Windows shortcut. In this case, you need to use the /p switch and specify the folder.

FIGURE 4-1: Starting Excel from the Windows Run dialog box.

The instructions that follow are for Windows Vista.

Start with an icon that launches Excel. Right-click the icon and choose Properties. In the Properties dialog box, click the Shortcut tab and enter the following in the Target field (see Figure 4-2):

FIGURE 4-2: Customizing a shortcut to launch Excel.

“C:Program FilesMicrosoft OfficeOffice14EXCEL.EXE” /p c:xlfiles

Keep in mind that the path to excel.exe can vary for different installations and for different versions.

You can also assign a shortcut key to launch Excel, which can be useful. If Excel is already running, pressing the shortcut key activates Excel.

You can run multiple instances of Excel on a single system. Each instance is treated as a separate task. Most people have pretty good success running multiple versions of Excel on a single system. For best results, install the versions in the order of their release dates (earliest to newest).

File Types

Although the Excel 2010 default file format is an XLSX workbook file, the program can also open and save a wide variety of other file formats. This section provides an overview of the file types that Excel 2010 can handle.

Beginning with Excel 2007, Microsoft removed support for Lotus and Quattro Pro spreadsheet file formats.

Excel file formats

Excel 2007 introduced a new default file format, and that format is also used in Excel 2010. However, these recent versions can still read and write older Excel file formats.

To change the default file save setting, choose File⇒Options and click the Save tab in the Excel Options dialog box. You'll find a drop-down list that lets you select the default file format.

Table 4-2 lists the Excel file types that Excel 2010 supports. Keep in mind that an Excel workbook or add-in file can have any extension that you like. In other words, these files don't need to be stored with the standard extensions shown in the table. However, Excel may display a warning if you try to open a file in which the content does not match the extension.

Table 4-2: Excel File Types

File Type	Extension	Read/Write	Notes
Excel Workbook	xlsx	Yes/Yes	The default Excel 2010 file format. It can't store VBA or XLM macro code.
Excel Macro-Enabled Workbook	xlsm	Yes/Yes	The Excel 2010 file format for workbooks that contain macros.
Excel Binary Workbook	xlsb	Yes/Yes	The Excel 2010 binary file format. It's an updated version of the previous XLS format.
Template	xltx	Yes/Yes	The Excel 2010 file format for a template. It can't store VBA or XLM macro code.
Macro-Enabled Template	xltm	Yes/Yes	The Excel 2010 file format for a template that contains macros.
Excel Add-In	xlam	Yes/Yes	The Excel 2010 file format for an add-in. It can store VBA and XLM macros.
Excel 97–Excel 2003 Workbook	xls	Yes/Yes	The Excel binary format (BIFF8) that's compatible with Excel 97 through Excel 2003.
Excel 97–Excel 2003 Template	xlt	Yes/Yes	The Excel binary template format (BIFF8) that's compatible with Excel 97 through Excel 2003.
Excel 97–Excel 2003 Add-In	xla	Yes/Yes	The Excel binary format (BIFF8) for add-ins that's compatible with Excel 97 through Excel 2003.
Microsoft Excel 5.0/95 Workbook	xls	Yes/Yes	The Excel binary format (BIFF5) that's compatible with Excel 5.0 and Excel 95.
XML Spreadsheet 2003	xml	Yes/Yes	Microsoft's XML Spreadsheet 2003 file format (XMLSS).

Microsoft Office XP and Office 2003 users can install the Microsoft Office Compatibility Pack, which allows them to open and save documents in the Office 2010 and Office 2007 file formats. The Compatibility Pack is available at http://office.microsoft.com.

Text file formats

When you attempt to load a text file into Excel, the Text Import Wizard might kick in to help you specify how you want the file retrieved.

To bypass the Text Import Wizard, press the Shift key when you click Open in the Open dialog box.

Table 4-3 lists the text file types supported by Excel 2010. All text file formats are limited to a single worksheet.

Table 4-3: Text File Types

File Type	Extension	Read/Write	Notes
CSV (comma separated values)	csv	Yes/Yes	Columns are delimited with a comma, and rows are delimited with a carriage return. Excel supports subtypes for Macintosh and MS-DOS.
Formatted Text	prn	Yes/Yes	Columns are delimited with a space character, and rows are delimited with a carriage return.
Text	txt	Yes/Yes	Columns are delimited with a tab, and rows are delimited with a carriage return. Excel supports subtypes for Macintosh, MS-DOS, and Unicode.
Data Interchange Format (DIF)	dif	Yes/Yes	The file format originally used by VisiCalc.
Symbolic Link (SYLK)	slk	Yes/Yes	The file format originally used by Multiplan.

Database file formats

Table 4-4 lists the database file types supported by Excel 2010. All database file formats are limited to a single worksheet.

Table 4-4: Database File Types

File Type	Extension	Read/Write	Notes
Access	mdb, mde, accdb, accde	Yes/No	You can open one table from the database.
dBASE	dbf	Yes/No	The file format originally created by Ashton-Tate.
Others	Various	Yes/No	By using the commands in the Data⇒Get External Data group, you can import data from various data sources that have connections or queries defined on your system.

Other file formats

Table 4-5 lists the other file types supported by Excel 2010.

Table 4-5: Other File Types

File Type	Extension	Read/Write	Notes
Hypertext Markup Language (HTML)	htm, html	Yes/Yes	Beginning with Excel 2007, this file format no longer supports “round-tripping.” If you save a file and then re-open it, you may lose information.
Single File Web Page	mht, mhtml	Yes/Yes	Also known as Archived Web Page. The only browsers that can display these files are Microsoft Internet Explorer and Opera.
OpenDocument Spreadsheet	ods	Yes/Yes	A file format developed by Sun Microsystems and OASIS. Readable by open source spreadsheets, such as OpenOffice.
Portable Document Format (PDF)	pdf	No/Yes	The file format originated by Adobe.
XML Paper Specification	xps	No/Yes	Microsoft's alternative to Adobe's PDF.

Working with Template Files

A template is essentially a model that serves as the basis for something else. An Excel template is a workbook that's used to create other workbooks. You can save any workbook as a template file (XLTX extension). Doing so is useful if you tend to create similar files on a regular basis. For example, you might need to generate a monthly sales report. You can save some time by creating a template that holds the necessary formulas and charts for your report. When you start new files based on the template, you need only to plug in the values.

Viewing templates

Excel gives you access to many templates. To explore the Excel templates, choose File⇒New to display the Available Templates screen.

The Office Online Templates section contains a number of categories. Click a category, and you'll see the available templates. To use a template, select it and click Download. Figure 4-3 shows some of templates available in the Invoices category.

FIGURE 4-3: Templates that you can use for invoices.

Microsoft Office Online has a wide variety of templates, and some are better than others. If you download a few duds, don't give up. Even though a template may not be perfect, you can often modify a template to meet your needs. Modifying an existing template is often easier than creating a workbook from scratch.

The location of the Templates folder varies, depending on the version of Excel. To find the location of your Templates folder, execute the following VBA statement:

MsgBox Application.TemplatesPath

Creating templates

Excel supports three types of templates:

• The default workbook template: Used as the basis for new workbooks. This file is named book.xltx.

• The default worksheet template: Used as the basis for new worksheets inserted into a workbook. This file is named sheet.xltx.

• Custom workbook templates: Usually, ready-to-run workbooks that include formulas. They can be as simple or as complex as you like. Typically, these templates are set up so that a user can simply plug in values and get immediate results.

Using the workbook template to change workbook defaults

Every new workbook that you create starts out with some default settings. For example, the workbook has three worksheets, the worksheets have gridlines, text appears in Calibri 11-point font, columns are 8.43 units wide, and so on. If you're not happy with any of the default workbook settings, you can change them.

Making changes to Excel's default workbook is fairly easy to do, and it can save you lots of time in the long run. Here's how you change Excel's workbook defaults:

1. Open a new workbook.

2. Add or delete sheets to give the workbook the number of worksheets that you want.

3. Make any other changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the two Display Options sections in the Advanced tab of the Excel Options dialog box.

To change the default formatting for cells, choose Home⇒Styles⇒Cell Styles and then modify the settings for the Normal style. For example, you can change the default font, size, or number format.

4. When your workbook is set up to your liking, choose File⇒Save As.

5. In the Save As dialog box, select Template (*.xltx) from the box labeled Save As Type.

6. Enter book.xltx for the filename.

7. Save the file in your XLStart folder (not in your Templates folder).

8. Close the file.

To determine the location of XLStart, execute this VBA statement:

MsgBox Application.StartupPath

After you perform the preceding steps, the new default workbook that appears when Excel is started is based on the book.xltx workbook template. You can also press Ctrl+N to create a workbook based on this template. If you ever want to revert back to the standard default workbook, just delete the book.xltx file.

If you choose File⇒New and select Blank Workbook, the workbook will not be based on the book.xltx template. I don't know whether that's a bug or by design. In any case, it provides a way to override the custom book.xltx template if you need to.

Using the worksheet template to change worksheet defaults

When you insert a new worksheet into a workbook, Excel uses its built-in worksheet defaults for the worksheet. These defaults include items such as column width, row height, and so on. If you don't like the default settings for a new worksheet, you can change them by following these steps:

1. Start with a new workbook and delete all the sheets except one.

2. Make any changes that you want to make, which can include column widths, named styles, page setup options, and many of the settings that are available in the Excel Options dialog box.

3. When your workbook is set up to your liking, choose File⇒Save As.

4. In the Save As dialog box, select Template (*.xltx) from the Save As Type box.

5. Enter sheet.xltx for the filename.

6. Save the file in your XLStart folder (not in your Templates folder).

7. Close the file.

8. Close and restart Excel.

After performing this procedure, all new sheets that you insert by clicking the Insert Worksheet button (which is next to the last sheet tab) will be formatted like your sheet.xltx template. You can also press Shift+F11 to insert a new worksheet.

Creating workbook templates

The book.xltx and sheet.xltx templates discussed in the preceding section are two special types of templates that determine default settings for new workbooks and new worksheets. This section discusses other types of templates, referred to as workbook templates, which are simply workbooks that you set up as the basis for new workbooks or worksheets.

Why use a workbook template? The simple answer is that it saves you from repeating work. Assume that you create a monthly sales report that consists of your company's sales by region, plus several summary calculations and charts. You can create a template file that consists of everything except the input values. Then, when it's time to create your report, you can open a workbook based on the template, fill in the blanks, and be finished.

You could, of course, just use the previous month's workbook and save it with a different name. This approach is prone to errors, however, because you easily can forget to use the Save As command and accidentally overwrite the previous month's file. Another option is to use the New From Existing icon in the New Workbook dialog box. This step creates a new workbook from an existing one, but gives a different name to ensure that the old file is not overwritten.

When you create a workbook that is based on a template, the default workbook name is the template name with a number appended. For example, if you create a new workbook based on a template named Sales Report.xltx, the workbook's default name is Sales Report1.xlsx. The first time that you save a workbook that is created from a template, Excel displays its Save As dialog box so that you can give the template a new name if you want to.

A custom template is essentially a normal workbook, and it can use any Excel feature, such as charts, formulas, and macros. Usually, a template is set up so that the user can enter values and get immediate results. In other words, most templates include everything but the data, which is entered by the user.

If your template contains macros, it must be saved as an Excel Macro-Enabled Template, with an XLTM extension.

Inside an Excel File

Excel 2010 uses an XML format for its workbooks, templates, and add-ins. These files are actually Zip compressed files. As such, they can be “unzipped” and examined.

Versions prior to Excel 2007 used a binary file format. Although the binary file format specifications are known, working with binary files is not easy. The Excel XML file format, on the other hand, is an open format. As such, these files can be created and manipulated using other software.

Dissecting a file

In this section, I describe the various parts within a typical Excel XLSM (macro-enabled) workbook file. The workbook, named sample.xlsm, is shown in Figure 4-4. It has one worksheet, one chart sheet, and a simple VBA macro. The worksheet contains a table, a button (from the Forms controls), a SmartArt diagram, and a photo of a flower.

The sample.xlsm workbook is available on the companion CD-ROM.

To view the innards of an Excel 2010 file, you need to open an Explorer window and add a ZIP extension to the filename. So the sample.xlsm file is renamed to sample.xlsm.zip. You can then open the file by using any unzipping program. I use the Zip feature built into Windows Vista.

If your system is set up to hide file extensions, I suggest that you turn off that option. In a Windows Explorer window, choose Tools⇒Folder Options and click the View tab. In the File and Folders section, remove the check mark from Hide Extensions For Known File Types.

You may prefer to extract the zipped files into an uncompressed directory. Doing so makes it easier to view the files. In Windows, right-click the filename and choose Extract All.

FIGURE 4-4: A simple workbook.

The first thing that you notice is that the file contains a directory structure. The left panel of Figure 4-5 shows the fully expanded directory structure for the workbook file. The actual directories will vary with the workbook.

With a few exceptions, all the files are text files. More specifically, they are XML files. You can view them in a text file editor, an XML editor, a Web browser, or even Excel. Figure 4-6 shows one of these files viewed in the Firefox browser. The non-XML files include graphic images and VBA projects (these are stored in binary format).

This XML file has four root-level folders, and some of these have subfolders. Many of the folders contain a _rels folder. These folders contain XML files that define the relationships to other parts within the package.

FIGURE 4-5: The directory structure of the workbook file.

Following is a list of the folders in the sample.xlsm workbook:

• _rels: Contains information about the package relationships.

• customXml: Contains information about Ribbon enhancements stored in the workbook.

• docProps: Contains XML files that describe the file properties and application settings.

• xl: Holds the meat of the file. The folder name varies with the Office document type (xl, ppt, word, and so on). You'll find several XML files that contain settings for the workbook. And if your workbook contains VBA code, it will be in a binary file with a BIN extension. The xl folder has several subfolders. (Some workbooks may have more or fewer subfolders, depending on the content.)

• charts: Contains an XML file for each chart. This file contains the chart settings.

• chartsheets: Contains an XML file with data for each chart sheet in the workbook.

• diagrams: Contains XML files that describe the diagrams (SmartArt) in the workbook.

• drawings: Contains an XML file with data for each drawing. Drawings include items such as buttons, charts, and images.

• media: Contains embedded media, such GIF and JPG files.

• tables: Contains an XML file with data for each table.

• theme: Contains an XML file with data about the workbook's theme.

• worksheets: Contains an XML file for each worksheet in the workbook.

FIGURE 4-6: Viewing an XML file in a Web browser.

If you add a ZIP extension to an Excel file, you can still open it in Excel — although you'll get a warning message first. Also, you can save a workbook with a ZIP extension. In the Save As dialog box, add a ZIP extension and then place double quotation marks around the entire filename — for example, “Myworkbook.xlsx.zip”.

Why is the file format important?

The open XML file formats introduced in Microsoft Office 2007 represent a significant step for the computing community. For the first time, it's relatively easy to read and write Excel workbooks using software other than Excel. For example, you can write a program to modify thousands of Excel workbook files without even opening Excel. Such a program could insert a new worksheet into every file. The programmer, of course, would need to have excellent knowledge of the XML file structures, but such a task is definitely doable.

Importantly, the new file formats are somewhat less prone to corruption (compared to the old binary formats). I saved a workbook file and then deleted one of the worksheet XML files. When I tried to reopen it in Excel, I got the message shown in Figure 4-7. Excel was able to tell that the file was damaged by comparing the information in the .res files with what's actually in the file. In this case, Excel was able to repair the file and open it. The deleted worksheet was re-inserted, but it was empty.

FIGURE 4-7: Excel can often repair a damaged workbook file.

In addition, the zipped XML files are usually smaller than comparable binary files. And, finally, the structured nature of the files makes extracting individual elements (for example, all graphic images) possible.

The typical Excel user won't need to examine or modify the XML components of a workbook file. But, as a developer, you may want to write code that changes Excel's Ribbon user interface. If that's the case, you will need to be at least somewhat familiar with the structure of a workbook XML file.

Refer to Chapter 22 for more information about modifying Excel's Ribbon.

The OfficeUI File

A file named Excel.officeUI stores changes made to the Quick Access toolbar and Ribbon. This XML file is located here:

C:Users<username>AppDataLocalMicrosoftOffice

This file is updated whenever a change is made to the Quick Access toolbar or to the Ribbon. It's updated immediately, not when Excel is closed. This file doesn't exist unless you've made at least one change to the user interface.

You can view Excel.officeUI using an XML editor, a Web browser, or Excel. To view this file in Excel, follow these steps:

1. Make a copy of the Excel.officeUI file.

2. Add an XML extension to the copy of the file so that the name is Excel.officeUI.XML.

3. Choose File⇒Open to open the file or just drag it into Excel's window.

4. You'll see a dialog box with some options; choose As an XML Table.

Figure 4-8 shows an imported Excel.officeUI file (the file is displayed as a table). In this case, the Quick Access toolbar has two commands enabled (rows 12 and 13 in the table), and I added a new tab and group, with two commands (rows 14 and 15 in the table).

It's possible to share an Excel.officeUI file with other users. For example, you may have customized your Quick Access toolbar with some handy tools, and added a new Ribbon tab with lots of useful commands, nicely organized . If a colleague is impressed, just give him a copy of your Excel.officeUI file and tell him where to put it. Keep in mind that replacing an existing Excel.officeUIfile will overwrite any changes your colleague has made.

Don't attempt to modify the Excel.officeUI file unless you know what you're doing. But feel free to experiment. If Excel reports an error in the Excel.officeUI file at start-up, you can just delete the file, and Excel will create a new one. Better yet, keep a backup copy of the original.

FIGURE 4-8: Viewing an Excel.officeUI data file in Excel.

The XLB File

Excel stores customized toolbar and menu bar configurations in an XLB file. Even though Excel 2010 doesn't officially support custom toolbars and menus in the way that it did in previous versions, it still uses an XLB file if you use any applications that create toolbars or custom menus. If you can't find an XLB file, it means that Excel isn't storing any custom toolbar or menu configurations.

When you exit Excel, the current toolbar configuration is saved in a file named Excel12.xlb. This file is (most likely) located here:

C:Users<username>AppDataRoamingMicrosoftExcel

This binary file contains information regarding the position and visibility of all custom toolbars and custom menu bars, plus modifications that you've made to built-in toolbars or menu bars.

Add-In Files

An add-in is essentially an Excel workbook file with a few important differences:

• The workbook's IsAddin property is True — which means that it can be loaded and unloaded by using the Add-Ins dialog box.

• The workbook is hidden and cannot be unhidden by the user. Consequently, an add-in is never the active workbook.

• When using VBA, the add-in workbook is not part of the Workbooks collection.

Access the Add-Ins dialog box by choosing File⇒Excel Options. Click the Add-Ins tab, select Excel Add-Ins from the Manage list, and click Go. If you've set up Excel to display the Developer tab, you can also use Developer⇒Add-Ins⇒Addins. Or (easiest of all), just press Alt+TI, a handy key combination leftover from Excel 2003.

Many add-ins provide new features or functions to Excel. You can access these new features as if they were built into the product.

You can create your own add-ins from workbook files. In fact, creating add-ins is the preferred method of distributing some types of Excel applications. Excel 2010 add-ins have an XLAM extension by default.

Besides XLAM add-ins, Excel supports XLL add-ins and COM add-ins. These types of add-ins are created using software other than Excel. This book discusses only XLAM add-ins.

Chapter 21 covers the topic of add-ins in detail.

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.

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 (included with Windows) to browse the Registry — and even to 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 sidebar “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

Excel's settings

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

HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0Excel

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.

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 some of the Registry sections that are relevant to Excel 2010. You may not find all these sections in your Registry database, and you may find some others.

Table 4-6: Excel Configuration Information in the Registry

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 choose File⇒Recent).
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 spell 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, you can't change a few settings 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 setting in Excel, but you can add a new Registry key like this:

1. Open the Registry Editor and locate this section:

HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0ExcelOptions

2. Right-click and choose 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 the usual light blue. If you don't like this look, just delete the Options6 Registry entry.

If you have trouble starting Excel, the Registry keys may have become corrupt. You can try using the Registry Editor to delete the entire Excel section:

HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0Excel

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