http://www.microsoft.com/TechNet/desk/office/reskit/orkf/ch03f.htm (PC Press Internet CD, 03/1996)
Updated : January 4, 1996
CD article first appeared on:October 1995
This chapter describes the architecture of Microsoft Excel in terms of its two primary elements: the application and the workbook. A section of the chapter lists Microsoft Excel components and where they are stored within Microsoft Excel, another section describes the architecture of the Microsoft Excel mapping feature, and the chapter concludes with an explanation of how Microsoft Excel resolves conflicting settings.
The architecture of Microsoft Excel consists of the Microsoft Excel application and the workbook file. The Microsoft Excel application controls the standard menus, toolbars, and default settings for how workbook files are created, opened, and stored. The workbook file is a collection of sheets: worksheets, chart sheets, and macro sheets. The workbook file also stores workbook-level components, such as passwords, styles, and scenarios. To standardize workbooks and the Microsoft Excel application across your organization, you can save workbooks in formats other than a workbook file. You can save workbooks as templates and as add-ins.
Microsoft Excel default settings and paths that are established at setup. These features can be customized after installation for a single user, a workgroup, or across an entire organization.
There are several ways to customize the Microsoft Excel application:
In addition to these methods of customizing Microsoft Excel, you can also customize specific features for your users. Some of these customizations are stored in a separate file on the user's system, as explained in the following list of features:
You can distribute a common set of custom lists for AutoFill and sorting, or provide custom toolbars for particular tasks at your site. To do so, create the custom lists and custom toolbars in a workbook. Save the workbook, and place the resulting .xlb file on a network share, where users can merge the customizations into their own workbooks by opening the .xlb file with the Open command on the File menu.
Note When custom toolbars are attached to a module in a workbook, they are not stored in the user's \Windows\Username.xlb file. Instead, they are stored with the workbook that contains the module.
If users want to start Microsoft Excel with workbooks and templates stored somewhere other than Xlstart (such as a network share), they can specify an alternate startup folder. Users specify an alternate startup folder on the General tab in the Options dialog box. This setting is stored in the Windows Registry.
You might want to create an alternate startup folder for your users if they have Microsoft Excel installed on individual workstations, each with individual Xlstart folders. By specifying a single alternate startup folder on a network share, you would have a single folder to maintain for any workbooks, charts, or workspace files you want your users to open when they start Microsoft Excel.
You can specify the default font, formatting, and other options for new workbooks for your users by creating a custom autotemplate. For example, you can create a workbook autotemplate that includes customized headers and footers and your company name or any text, formatting, formulas, and macros that your users want as defaults when creating new workbooks.
To create a workbook autotemplate, create or open the workbook you want to use as the autotemplate. Click Save As on the File menu, and save the workbook in the template file format, with the name Book.xlt. Distribute this file to your users' Xlstart or alternate startup folder.
When users click the New button on the Standard toolbar, Microsoft Excel creates a new workbook, based on the autotemplate. The autotemplate also shows up as the Workbook icon on the General tab in the New dialog box when users click New on the File menu or when they click the Start a New Document button on the Microsoft Office Shortcut Bar.
To create a workspace file for your users, open all the workbooks you want to include in the workspace. Size and position them as you want them to appear when the users open the file. Then, click Save Workspace on the File menu. The default name Microsoft Excel gives to the workspace file is Resume.xlw. You may rename the file if you want.
Distribute this file to your users. If you want the workspace file to open automatically each time users start Microsoft Excel, copy the file to the users' Xlstart or alternate startup folder.
Note The workbook names and folder locations are saved with the workspace file. If the files are moved to another location, Microsoft Excel will not be able to find them.
The workbook in Microsoft Excel is analogous to the document in Word, or the presentation in PowerPoint. The workbook is where the data is stored, in the Microsoft Excel file format. Microsoft Excel workbook files have the extension .xls.
Microsoft Excel stores workbook files in the binary interchange file format (BIFF). Workbooks are compound files: files that contain a hierarchical system of storages and streams. A storage is analogous to a folder, and a stream is analogous to a file in a folder. This file format is the OLE implementation of the Structured Storage Model standard.
Fore more information about the Structured Storage Model, see the OLE 2 Programmer's Reference, Volume One, and Inside OLE. For detailed information about the BIFF file format, see the Microsoft Excel Developer's Kit. All of these books are published by Microsoft Press and are available at your local bookstore.
A template is a special workbook used as a pattern to create other workbooks of the same type. Rather than an .xls file extension, templates have the file extension .xlt.
You can create templates for workgroups to maintain consistency. For example, you can create a sales report workbook, save it as a template, and distribute it to a workgroup. Users in the workgroup then create weekly sales reports based on the template or insert sheets from template into their workbook.
Settings saved in a template determine the following characteristics of new workbooks based on that template:
When users open a template, Microsoft Excel opens an untitled, unsaved copy of the template that contains all data, formatting, formulas, macros, styles, scenarios, and so forth that is contained in the template. The original template file remains unchanged.
To create a template, first create the workbook that includes any text, formats, and formulas your users want, and then click Save As on the File menu. Enter a filename and select the folder, such as a network share, to store the template. In the Save As Type box, select Template. Storing workbook templates in a user's Xlstart or alternate startup folder automatically makes the template available when the user clicks New on the File menu.
Although templates in a user's startup folder are automatically available for creating new workbooks, only the template with the reserved name Book.xlt stored in the startup folder is an autotemplate. When users start Microsoft Excel, click the New button on the Standard toolbar, or click the Start a New Document button on the Microsoft Office Shortcut Bar, the new workbook is based on the autotemplate.
In addition to saving workbooks as .xls and .xlt files, users can also save workbooks as add-ins. Add-Ins compiled from Microsoft Excel workbooks have the file extension .xla.
You can create custom add-ins to assemble and distribute custom features that, from the user's point of view, act as if they are built into Microsoft Excel itself. For information about creating, maintaining, and distributing add-ins, see Chapter 12 of the Microsoft Excel/Visual Basic for Windows 95 Programmer's Guide, published by Microsoft Press and available from your local bookstore.
Add-Ins can also be written in C. When compiled, these add-ins have the file extension .xll (similar to .wll files in Word).
Several add-ins are included with Microsoft Excel, although whether or not they are installed on a user's system depends on what kind of installation was chosen during setup, and on which add-ins were selected during a Custom installation. For information about the components installed for each type of installation, see Appendix C, "List of Installed Components."
Users can install additional add-ins by clicking Add-Ins on the Tools menu.
The following table describes the add-ins included with Microsoft Excel. Their default installation folder is \Excel\Library and its subfolders. Some add-ins require a dynamic link library or compiled C add-in in addition to the .xla file, as indicated in the table.
Add-In |
Filename |
Description |
|---|---|---|
Analysis ToolPak |
Funcres.xla, Procdb.xla, Analys32.xll |
Adds financial and engineering functions, and provides tools for performing statistical and engineering analysis. |
Analysis ToolPak - VBA |
Atpvbaen.xla |
Adds Visual Basic functions for Analysis ToolPak. |
Microsoft Query 1 |
Xlquery.xla |
Retrieves data from external database files and tables using Microsoft Query. |
ODBC |
Xlodbc.xla, Xlodbc32.dll |
Adds worksheet and macro functions for retrieving data from external sources with Microsoft Open Database Connectivity (ODBC). |
Report Manager |
Reports.xla |
Prints reports that consist of views and scenarios. |
Solver |
Solver.xla |
Calculates solutions to what-if scenarios based on adjustable cells, constraint cells, and optionally, cells that must be maximized or minimized. |
Template Utilities |
Tmpltnum.xla |
Adds utilities used by Microsoft Excel templates. |
Template Wizard with Data Tracking |
Wztemplt.xla |
Creates a template to export worksheet data to a database. |
Update Links |
Updtlink.xla |
Updates links to Microsoft Excel 4.0 add-ins to directly access the new built-in functionality. |
View Manager |
Views.xla |
Saves the current window display as a view and lets users apply their own saved views to see their worksheet in different formats. |
AutoSave |
Autosave.xla |
Saves workbook files automatically. |
1 To use Microsoft Query with Microsoft Excel, users must install the Microsoft Query application, the Microsoft Query add-in, the drivers for the types of data they want to retrieve, and the necessary ODBC files. Users can install these files by clicking Add/Remove Office Programs and selecting the Data Access option. For information about Data Access components, see Appendix C, "List of Installed Components."
Document ContentsWorkbook components are stored on sheets of the workbook. These components include all the contents of all data cells (constant values and formulas), cell formatting, charts, macros, add-ins, and security settings. Security settings can be stored for an entire workbook, as well as for individual sheets.
This section lists the various components of a workbook and explains how each is stored within the workbook.
Constant values stored in cells can be numeric values, including date, time, currency, percentage, or scientific notation, or it can be text. The way Microsoft Excel displays numbers in a cell depends upon the number format for the cell. This value may differ from the actual value Microsoft Excel stores, which is with 15 digits of accuracy. By default, Microsoft Excel makes calculations based on the stored value (full precision), but users can have Microsoft Excel calculate based on displayed values by selecting Precision As Displayed on the Calculations Tab in the Options dialog box.
If users are getting results from their formulas that appear to be wrong, it may be due to the difference in precision between displayed and stored values. For example, if two cells each contain the value 1.007, and a formula adds them in a third cell, the result is 2.014. If all three cells are formatted to display only two decimal places, the displayed calculation, 1.01+1.01=2.01, appears to be wrong. Similarly, calculating on displayed values, 1.01+1.01=2.02 appears to be correct, but this results in a value that is not as precise as it would be by calculating on stored values.
Once users switch to calculating on displayed values, Microsoft Excel stores all constant values as their displayed values, and full precision values cannot be restored.
Calculating with precision as displayed:
&boxf; Affects all worksheets in the active workbook.
&boxf; Does not affect numbers in the General format, which are always calculated with full precision.
&boxf; Slows calculation because Microsoft Excel must round the numbers as it calculates.
Formulas are a sequence of values, cell references, names, functions or operators that produce a new value from existing values. They are part of the data that is stored with the workbook file. Cell references in a function can be relative, absolute, or mixed references in the A1 style, or row-and-column (R1C1) style; or they can be name references.
Users who use the A1, or R1C1, reference style may experience difficulty with formulas if they reposition or delete cells, as these references refer to data by position. A way to avoid this problem is to reference cells by name.
Users can use names as references to a cell, a group of cells, a value, or a formula. Name references can be accessible to an entire workbook or restricted to a sheet. When restricted to a sheet, the same name, such as "Profit" can be used to define related cells on different sheets in the same workbook. Book-level names, on the other hand, can refer to cells on one worksheet and be used throughout the workbook, eliminating the need to re-create names for each new worksheet or to type worksheet references in formulas.
When users create book-level names, they simply type the name they are defining in the Name box on the formula bar. When they create sheet-level names, however, they must include the name of the sheet, such as "Sheet1!Profit" in the Name box.
Sheet-level names override book-level names when used on the sheet where they are defined. For example, in a workbook where Profit is defined both as a book-level name and a sheet-level name for Sheet1, if a user enters a formula that uses the name Profit on Sheet1, the formula uses the sheet-level name rather than the book level name.
Scenarios are the Microsoft Excel method of allowing users to pose what-if models in their data. To create a what-if model, users specify a set of changing cells, or cells that store hypothetical data under a name. This name is stored with the workbook file.
Scenarios can be sheet-level or book-level. Users can merge scenarios among different sheets in the same workbook and among multiple workbooks. To merge scenarios among workbooks, all the workbooks must be open.
When a user merges scenarios, there may be some duplicate names. "Best Case" and "Worst Case" are common examples. In such instances, Microsoft Excel appends additional information to the duplicate scenario names, such as creation date, creator name, or an ordinal number.
Users can protect scenarios by selecting the Prevent Changes and Hide options in the Add Scenario dialog box. For the protection option to take effect, however, the user must also activate protection for the sheet.
Cell formatting is stored separately from the cell data, as a collection of format settings that are saved together as a style. Styles can be copied between cells, changed, or deleted, without affecting the data in the cell.
The following table shows the individual format settings determined by styles.
Setting |
Determines |
|---|---|
Number |
Decimal places, separator, inclusion of dollar sign, style for displaying negative number and other options for formatting different kinds of numbers, such as currency, dates, fractions, and so on. |
Alignment |
Horizontal, vertical, text orientation, and whether or not text wraps in the cell. |
Font |
Typeface, style, size, special effects, and color of the text in the cell. |
Border |
Placement and style of the border of the cell. |
Pattern |
Shading and color of the cell. |
Protection |
Whether data in the cell is locked or the formula is hidden. This option does not take effect until the worksheet is protected, which the user can do by clicking Protection on the Tools menu. |
Styles are saved with the workbook. Users can copy styles from one workbook to another by clicking Merge in the Style dialog box and then selecting the workbook they want to merge styles from. Both the source and destination workbooks must be open. All styles from the source workbook are merged into the destination workbook. If styles in the destination document have names that match styles being merged, the user is prompted to choose whether or not to overwrite existing styles in the destination workbook.
All cells in all of the sheets of a new workbook are initially formatted with the Normal style. Users can change and store the settings for the Normal style, but the change does not affect any other workbooks unless they merge the new Normal style into another workbook or save the workbook with the new Normal style to a template. If the Normal style has been modified in a user's autotemplate, the default formatting for cells in all new workbooks is the modified Normal style.
The default font that the Normal style uses is specified by the Standard Font setting on the General tab in the Options dialog box. This setting is stored in the Registry key Hkey_Current_User\Software\Microsoft\Excel\7.0\Microsoft Excel, as the string value for Font.
Users create charts based on a range of selected cells on a worksheet. Depending upon which option the user specifies, a chart is stored either on the same worksheet as the cells it is linked to, or it exists as an entire sheet within the workbook. Charts are linked dynamically to data on a worksheet. This means that changes to the data are updated in the chart, and changes to a data marker on the chart are reflected in the linked data cells.
Data markers are the chart symbols (dot, bar, area, slice, and so on) that represent a single data point or value originating from a worksheet cell. Users can modify data markers on the following types of charts only: bar, column, line, stacked, pie, doughnut, and xy (scatter) charts. Data markers on 3-D charts cannot be modified.
Text on the chart is also linked to text in worksheet cells. This text appears as data labels, legend entries, and labels for axis tick-marks. Editing text in the worksheet cells affects text in the charts that are linked to the cells. Users can also edit text in charts, but this breaks the link to the cells on the worksheet.
Microsoft Excel stores charts in the workbook file. Charts sheets can be copied into other workbooks, and charts can be copied and pasted into other workbooks and into other Office applications, such as Word documents and PowerPoint presentations.
Instead of formatting chart items individually, users can quickly change the look of a chart using AutoFormat. Microsoft Excel stores AutoFormats as charts on separate chart sheets in a workbook with the reserved filename Xl5galry.xls.
AutoFormats, like cell styles, are a collection of characteristics. Each AutoFormat is based on one of the 14 predefined chart types, and can include a chart subtype, legend, gridline options, data labels, color settings, patterns, and layout. Microsoft Excel includes built-in AutoFormats and also allows users to create custom AutoFormats by selecting User-Defined and clicking Customize in the AutoFormat dialog box.
To create a unified look, you can build a library of custom AutoFormats for workgroups at your site. For example, you can create a series of AutoFormats with a consistent layout and color scheme, designed to be integrated into a PowerPoint presentation. To do this, you create custom AutoFormats in a workbook, and then save the workbook. When you save this file, your custom AutoFormats are stored in Xl5galry.xls.
To make the custom AutoFormats available to your users, do one of the following:
Macros store Visual Basic code in workbooks, allowing users to automate repetitive or complex tasks. You can create macros for your users, and distribute them in the following ways:
You and your users can secure specific sheets or entire workbooks, according to how you set Protection options in the Protect Sheet or Protect Workbook dialog boxes. Security settings are stored with the workbook file. The only way to change them is to open the workbook file, modify the protection options, then save the file.
You can also assign passwords at the sheet level or workbook level. Passwords enable users to bypass sheet or workbook protections.
At the sheet level, the following items can be protected:
At the workbook level, the following items can be protected:
The Microsoft Excel Data Map supplies six map formats for analyzing geographical data: Value Shading, Category Shading, Dot Density, Graduated Symbols, Pie Charts, and Column Charts. These formats and their uses are described in the Data Map Help (available only when the Data Map server is active). The map formats allow users to spot geographical patterns in their data, and to correlate one data item, such as sales in a city, to another, such as a city's population.
Data Map consists of an OLE mini-server (meaning that it can only be invoked from within an OLE application) and a set of maps for use with the Data Map server. Users can insert a Data Map object in any OLE application. In OLE container applications, such as the Office applications, users can edit the object in place using OLE visual editing. Users can also move or copy Data Map objects between OLE applications using OLE drag and drop.
Regardless of the location of a Data Map object, data for the map must come from either a Microsoft Excel worksheet or a Microsoft Access database. A user can create a map in Microsoft Excel and then drag it to a Word document or PowerPoint presentation, but doing so breaks the link to the data. To update the map in Word or PowerPoint, the user must replace it with an updated copy of the map. Creating a map in Microsoft Excel and embedding the Microsoft Excel file in an Office Binder can also break the link to the data.
Term |
Refers to |
Example |
|---|---|---|
Map Template |
A collection of features, such as political boundaries, cities, airports, and highways. Some map templates contain a feature that consists of points for single data items, such as postal codes. These single-point feature sets are also called centroid maps. |
US States map template consists of the following features: U. S. state political boundaries U. S. major cities U. S. minor cities U. S. airports U. S. interstate highways Centroid map for five-digit ZIP Codes |
Features |
A set of political boundaries or physical entities, such as cities, which are in a template. |
U. S. state political boundaries. |
Data Set |
Cell data that is mapped to a map template. |
Sales by major U. S. cities in the US States map template. |
Geographic data |
Cell data that corresponds to names of boundaries and other features, such as cities, in a map template. |
The cell data "AL" and "AK" corresponds to the boundary names "Alabama" and "Alaska," respectively, in the US States map template. |
All Data Map map files are installed in the \Program Files\Common Files\Microsoft Shared\Datamap\Data folder. Data Map keeps a data dictionary of all installed maps in the file Geodict.dct. The location of the geodictionary is set and maintained in the Registry in the key Hkey_local_machine\Software\Microsoft \DataMap\Directories, as the text value for MapData.
For Microsoft Excel to have access to a map, the map must be registered in the geodictionary. Therefore, if a user removes a map file from the disk, the geodictionary will still refer to the file, and the user will get error messages when Microsoft Excel attempts to access the file. Similarly, users cannot add new maps simply by adding map files to their disk. Copying map files to the disk does not register the files in the geodictionary.
To add or remove map files, users must use the Data Installer (Datainst.exe), a utility installed with Data Map, usually in the \Program Files\Common Files\Microsoft Shared\Datamap folder. The Data Installer provides an easy, graphical way to add and remove map files to and from the geodictionary. The geodictionary cannot be edited directly by a user, and must be edited using the Data Installer.
Removing a map from the geodictionary does not delete the files from the user's disk; you must delete the files manually.
Data Map map files use the same format as MapInfo 3.0 files. Each map consists of four to five files. For example, the World Map consists of the following files:
File |
Description |
|---|---|
World.dat |
Binary file. Stores names for all boundaries or points in the map. |
World.id |
Binary file. Stores information about relationships with other maps. |
World.ind |
Binary file. Index used for speeding access to the .map file. Only used in large maps. |
World.map |
Binary file. Stores latitude and longitude for all points which make up all boundaries in the map. |
World.tab |
Map descriptor file, used for storing version information, a friendly name for the map, and description of the map feature structure. |
There are three ways to create a Data Map object in Microsoft Excel:
When the Data Map object is created, Microsoft Excel checks for a source data range, which is the user's selection. If Microsoft Excel does not find a source data range, it creates a blank Data Map object. If Microsoft Excel finds a source data range, it sends the range to Data Map. Data Map takes a sample from each column of the source data range and attempts to match the sample against all installed map templates.
Each primary map in a template contains a set of labels in the .dat file. These labels correspond to a set of boundaries (such as "Washington," "Colorado," and so on). In order for the user's data to be displayed on a map, Data Map must be able to match geographic data in the user's selection to labels stored in the .dat file.
Data Map attempts to match over 80 percent of the geographic data against an installed map. If this level is attained, Data Map opens the matched map and begins to plot the data.
In some cases, Data Map can match more than one map. For example, "Germany" matches both the World by Country and the Europe by Country maps. In such cases, users are prompted to choose which of the matched maps they wish to use.
If no maps are matched, Data Map disregards the data and prompts the user to choose a map to display.
If the matching process takes more than five seconds, Data Map aborts auto-matching and prompts the user for a map to use.
Once a map is matched, Data Map displays the map and binds the user's data to it. It is possible that Data Map will not recognize some geographic data, either because the data is misspelled or because the user used an unfamiliar variation of a region's name, such as "Mainland China" instead of "People's Republic of China." Data Map displays all unknown labels one by one to help users find an acceptable alternative. The file Mapstats.xls, stored in the \Program Files\Common Files\Microsoft Shared\Datamap\Data folder, contains lists of proper labels for all installed maps, as well as demographic data to compare with your own data.
By default, Data Map plots values in the first column to the right of the geographic data as a Value Shaded format on the map. If Data Map finds multiple records that pertain to the same region, the values are aggregated. These values are usually added, but it is also possible to count and average them.
Data Map can map data on only one template and one map at a time. If a user's selection has labels for regions in more than one map, Data Map matches the greatest amount of geographic data to one map, and reads geographic data that does not match that map as a mismatch. For example, suppose a user's selection includes several U. S. states and the country Japan. Data Map matches the geographic data to the US by State map, but comes up with a mismatch for the label Japan.
Once users have created a Data Map based on a template, they can import data to other maps in the template. For example, suppose the active template is North America. This template contains three maps: US States, Canadian Provinces, and States in Mexico. The user can select geographical data for one of these maps (US States, for example) to create the North America Data Map object, but then the user must import data separately for the Canadian Provinces map and for the States in Mexico map. Data Map cannot map the user's original selection with data for multiple maps; it must map them one at a time.
Document ContentsConflicts can arise in Microsoft Excel when files of the same name reside in a user's startup and alternate startup folders, or when the user interface has been customized through macros or Visual Basic code.
Microsoft Excel opens files in a user's Xlstart folder before opening files in the alternate startup folder. If there is a file in the alternate startup folder with a name that matches a file in the user's Xlstart folder, the file in the alternate startup folder is ignored. For example, if Xl5galry.xls exists in both the Xlstart and the alternate startup folder, only the custom AutoFormats defined in \Xlstart\Xl5galry.xls are available to the user.
There are two ways to create customized toolbars in Microsoft Excel.
When a user opens a workbook that has a toolbar attached, the attached Toolbar definitions are saved to the user's .xlb file. These definitions will exist on the user's computer after the user closes or even deletes the workbook. If custom macros are attached to the toolbar and the user tries to implement the toolbar after closing or deleting the original workbook, the user will get an error message stating that Microsoft Excel cannot find the macro. This is because the macro resides with the workbook where the toolbar originated.
If users edit the toolbar, their changes do not affect the original toolbar stored with the workbook. When the user reopens the original workbook, Microsoft Excel uses the copy of the toolbar stored in the user's .xlb file rather than reloading the toolbar stored with the workbook. To generate the original version of the toolbar, users can delete the edited copy by selecting it and clicking Delete in the Toolbars dialog box.
For information about modifying Microsoft Excel toolbars with Visual Basic, see Chapter 9 of the Microsoft Excel/Visual Basic for Windows 95 Programmer's Guide.
If an add-in's source workbook contains a reference to another add-in -- for instance, a reference to Xlodbc.xla -- make sure that when you distribute the add-ins, the referenced add-in is stored somewhere on the user's computer where the calling add-in can find it. You should always store a referenced add-in in the folder that contains the calling add-in. When you compile a source workbook to an add-in, Microsoft Excel stores a hard-coded path to the referenced add-in in the calling add-in. If you move the calling add-in to another computer (for example, when you distribute the add-in to your users) and Microsoft Excel cannot find the file in the hard-coded location, it searches in the following locations, in this order:
For information about creating, maintaining, and distributing add-ins, see Chapter 12 of the Microsoft Excel/Visual Basic for Windows 95 Programmer's Guide.
Send us your comments
Go To TechNet Home Page 
Go To Microsoft Home Page