Chapter 3: Understanding Excel Files

In This Chapter

• Starting Excel

• Opening and saving different types of files in Excel

• Understanding the Excel file formats

• 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 double-click an icon on the desktop, click an icon in the taskbar, 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 2013 starts, it performs many actions, including the following:

• 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 if it exists.

• It opens the AutoCorrect list (and *.ACL file) if it exists.

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

• It opens any workbooks in the XLStart directory.

• It opens the Personal Macro Workbook (personal.xlsb) if it exists.

• It opens any workbooks that are in the alternate start-up directory (specified on 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 an 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 Files (x86)Microsoft OfficeOffice15EXCEL.EXE

or

C:Program FilesMicrosoft OfficeOffice15EXCEL.EXE

To determine where Excel is installed, execute this VBA statement:

MsgBox Application.Path

Unlike previous versions, Excel 2013 has a single-document interface. In other words, every workbook has its own window and its own Ribbon and is treated as a separate task. In previous versions, multiple workbooks opened in a single Excel window (a multidocument interface).

For VBA developers, the single document interface affects modeless UserForms and custom shortcut menus. These topics are covered later in this book.

If you press the Ctrl key when you start Excel, the program opens in safe mode. This mode is primarily used for troubleshooting when Excel crashes when it's started.

File Types

Although the Excel 2013 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 2013 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 and Excel 2013. 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 3-1 lists the Excel file types that Excel 2013 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.

Microsoft Office XP and Office 2003 users can install the Microsoft Office Compatibility Pack, which allows them to open and save documents in Office 2007 (and later) 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 3-2 lists the text file types supported by Excel 2013. All text file formats are limited to a single worksheet.

Database file formats

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

Other file formats

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

Excel File Compatibility

It's important to understand the limitations regarding version compatibility. Even though a colleague may be able to open your file, there is no guarantee that everything will function correctly or look the same.

If you save your workbook to an older file format (such as XLS, for versions prior to Excel 2007), Excel automatically runs Compatibility Checker. The Compatibility Checker identifies the elements of your workbook that will result in loss of functionality or fidelity (cosmetics).

Figure 3-1 shows the Compatibility Checker dialog box. Use the Select Versions to Show button to limit the compatibility checking to a specific version of Excel.

The bottom part of Compatibility Checker lists potential compatibility problems. To display the results in a more readable format, click the Copy to New Sheet button.

Keep in mind that compatibility problems can occur with Excel 2007 and Excel 2010 even though these versions use the same file format as Excel 2013. You can't expect features that are new to Excel 2013 to work in earlier versions. For example, if you add Slicers (a new feature in Excel 2013) to a table and send it to a colleague who uses Excel 2010, Slicers won't be displayed. In addition, formulas that use any of the new worksheet functions will return an error. Compatibility Checker identifies these types of problems.

Figure 3-1: The Compatibility Checker is a useful tool for those who share workbooks with others.

Protected View

Excel 2010 introduced a security feature known as Protected view. Although it might seem as though Excel is trying to keep you from opening your own files, Protected view is all about protecting you from malware. Malware refers to something that can harm your system. Hackers have figured out how to manipulate Excel files such that harmful code can be executed. Protected view essentially prevents these types of attacks by opening a file in a protected environment (a “sandbox”).

If you open an Excel workbook that you downloaded from the web, you'll see a colorful message above the formula bar. In addition, the Excel title bar displays [Protected View]. Choose File⇒Info to find out why Excel opened the file in Protected view.

If you are certain that the file is safe, click Enable Editing. If you don't enable editing, you will be able to view the contents of the workbook, but you won't be able to make any changes to it.

If the workbook contains macros, you'll see another message after you enable editing: Security Warning. Macros have been disabled. If you are sure that the macros are harmless, click Enable Content.

By default, Protected view is enabled for the following:

• Files downloaded from the Internet

• Attachments opened from Outlook

• Files opened from potentially unsafe locations, such as your Temporary Internet Files folder

• File that are blocked by File Block Policy (a Windows feature that allows administrators to define potentially dangerous files)

• Files that were digitally signed, but the signature has expired

In some situations, you don't care about working with the document. You just want to print it. In that case, choose File⇒Print, and then click the Enable Printing button.

Also, note that you can copy a range of cells from a workbook in Protected view, and paste it into a different workbook.

You have some control over the types of files that trigger Protected View. To change the settings, choose File⇒Options, and click Trust Center. Then click the Trust Center Settings button and click the Protected View tab in the Trust Center dialog box.

Using AutoRecover

If you've used computers for any length of time, you've probably lost some work. You forgot to save a file, or maybe the power went out and your unsaved work was lost. Or maybe you were working on something and didn't think it was important, so you closed it without saving — and later realized that it was important. A feature introduced in Excel 2010 called AutoRecover might make these types of “D'oh!” moments less frequent.

As you work in Excel, your work is periodically saved, automatically, in the background — you don't even know that it's happening. Excel even saves workbooks that you never explicitly saved.

The AutoRecover feature consists of two components:

• Versions of a workbook are saved automatically, and you can view them.

• Workbooks that you closed without saving are saved as draft versions.

Recovering versions of the current workbook

To see whether any previous versions of the active workbook are available, choose File⇒Info. The Versions section lists the available old versions (if any) of the current workbook. In some cases, more than one autosaved version will be listed. In other cases, no autosaved versions will be available.

You can open an autosaved version by clicking its name. Remember that opening an autosaved version won't automatically replace the current version of your workbook. Therefore, you can decide whether the autosaved version is preferable to the current version. Or you can just copy some information that may have been accidentally deleted, and paste it to your current workbook.

When you close the workbook, the autosaved versions are deleted.

Recovering unsaved work

When you close a workbook without saving your changes, Excel asks whether you're sure. If that unsaved workbook has an autosaved version, the “Are you sure?” dialog box informs you of that fact.

To recover a workbook that you closed without saving, choose File⇒Info⇒Manage Versions⇒Recover Unsaved Workbooks. You'll see a list of all draft versions of your workbooks. You can open them and (if you're lucky) recover something that you needed. Note that the unsaved workbooks are stored in the XLSB file format.

Draft versions are deleted after four days or until you edit the file, whichever comes first.

Configuring AutoRecover

Normally, AutoRecover files are saved every 10 minutes. You can specify a save interval between 1 and 120 minutes in the Save tab of the Excel Options dialog box.

If you work with sensitive documents, you might prefer that previous version don't get saved automatically on your computer. The Save tab of the Excel Options dialog box lets you disable this feature completely or only for a specific workbook.

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 simply plug in the values.

Viewing templates

Excel gives you access to many templates. To explore the Excel templates, choose File⇒New, and then enter a search term.

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. Custom workbook templates 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 worksheets have gridlines, text appears in Calibri 11-point font, and columns are 8.43 units wide. 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 and 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, such as changing the column widths, named styles, page setup options, and many of the settings 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 to the standard default workbook, just delete or rename 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, this command sequence 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 and row height. 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 your changes, such as changing the column widths, named styles, page setup options, and many of the settings 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 right-click a filename and choose Open a Copy. This step creates a new workbook from an existing one, but gives a different name to ensure that the old file is not overwritten.

To create a workbook based on a template you created, choose File⇒New, and click Personal (located below the search box).

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 2007 and later versions uses an XML format for 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 of a typical Excel XLSM (macro-enabled) workbook file. The workbook, named sample.xlsm, is shown in Figure 3-2. 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.

The sample.xlsm workbook is available at this book's website.

Figure 3-2: A simple workbook.

To view the innards of an Excel XLSX (or XLSM) 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 7.

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.

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

Figure 3-3: The directory structure of the workbook file.

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 3-4 shows one of these files viewed in the Chrome 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 _rels folders contain XML files that define the relationships to other parts within the package.

Figure 3-4: Viewing an XML file in a web browser.

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.

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 was 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 3-5. Excel was able to tell that the file was damaged by comparing the information in the *._rels 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 reinserted, but it was empty.

Figure 3-5: 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 20 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

Whenever a change is made to the Quick Access toolbar or to the Ribbon, the XML file is updated immediately, not when Excel is closed. The 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 3-6 shows an imported Excel.officeUI file (the file is displayed as a table). In this case, five commands are enabled on the Quick Access toolbar (indicated as TRUE in column B), and I added a new group to the View tab, with five commands (rows 16 through 20 in the table).

You can 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 your colleagues are impressed, just give them a copy of your Excel.officeUI file and tell them where to put it. Keep in mind that replacing an existing Excel.officeUIfile will overwrite any changes your colleagues have made. There is no way to merge multiple Excel.officeUI files.

Figure 3-6: Viewing an Excel.officeUI data file in Excel.

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 startup, you can just delete the file, and Excel will create a new one. Better yet, keep a backup copy of the original.

The XLB File

Excel stores customized toolbar and menu bar configurations in an XLB file. Even though Excel 2013 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 Excel15.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⇒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 2007 (and later) 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 19 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 9 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 3-7 shows what the Registry Editor looks like.

Figure 3-7: 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 2013 is stored in this Registry section:

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

Table 3-5: 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.
Place MRU	Holds information about the mostly used places (directories and other storage locations).
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.

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_USERSoftwareMicrosoftOffice15.0Excel

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