Microsoft KB Archive/95952

{|
 * width="100%"|

Excel AppNote: Using Microsoft Excel Workbooks (XE0696)
'Article ID: Q95952

Creation Date: 02-MAR-1993

Revision Date: 12-SEP-1996' The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0
 * Microsoft Excel for the Macintosh, version 4.0

The Application Note &quot;Using Microsoft Excel Workbooks&quot; (XE0696) is now available from Product Support Services (PSS). This application note describes how to use Microsoft Excel version 4.0 workbooks (documents that you can use to store other Microsoft Excel documents or references to them). Workbooks replace Microsoft Excel version 3.0 workspaces.

You can obtain this Application Note from the following sources:


 * Microsoft Download Service (MSDL)
 * The Internet (Microsoft anonymous ftp server)
 * Microsoft FastTips Technical Library
 * Microsoft Product Support Services

For complete information, see the &quot;To Obtain This Application Note&quot; section at the end of this article. THE TEXT OF XE0696

Microsoft(R) Product Support Services Application Note (Text File) XE0696: USING MICROSOFT EXCEL WORKBOOKS Revision Date: 2/93 No disk included The information in this Application Note applies to: Microsoft Excel version 4.0 for Windows(TM) Microsoft Excel version 4.0 for the Macintosh(R)

WORKBOOKS OVERVIEW
 * INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY   |
 * ACCOMPANY THIS DOCUMENT (collectively referred to as an           |
 * Application Note) IS PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY     |
 * KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO   |
 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A    |
 * PARTICULAR PURPOSE. The user assumes the entire risk as to the    |
 * accuracy and the use of this Application Note. This Application   |
 * Note may be copied and distributed subject to the following       |
 * conditions: 1) All text must be copied without modification and   |
 * all pages must be included; 2) If software is included, all files |
 * on the disk(s) must be copied without modification [the MS-DOS(R) |
 * utility DISKCOPY is appropriate for this purpose]; 3) All         |
 * components of this Application Note must be distributed together; |
 * and 4) This Application Note may not be distributed for profit.   |
 * Copyright 1992-1993 Microsoft Corporation. All                    |
 * Rights Reserved.                                                  |
 * Microsoft and MS-DOS are registered trademarks and Windows        |
 * is a trademark of Microsoft Corporation.                          |
 * Macintosh is a registered trademark of Apple Computer, Inc.       |
 * 1-2-3 and Lotus are registered trademarks of Lotus Development    |
 * Corporation.                                                      |
 * Corporation.                                                      |

WHAT WORKBOOKS DO FOR YOU

A &quot;workbook&quot; is a Microsoft Excel document in which you store other Microsoft Excel documents or references to them. Workbooks replace Microsoft Excel version 3.0 workspaces. Like workspaces, you can use workbooks to:


 * Group related Microsoft Excel documents together.
 * Open multiple documents by opening a single file.
 * Control document window size, placement, and display options.
 * Refer to the same document from more than one workbook.

Workbooks also provide several features not available in workspaces. With workbooks, you can also:
 * Store multiple Microsoft Excel documents under one workbook filename.
 * Give extended names of up to 31 characters to documents stored in a workbook.
 * Easily navigate through the documents stored in a workbook.
 * Obtain full file compatibility with Lotus(R) 1-2-3(R) .WK3 (multi- ply or 3-D) worksheets.

HOW WORKBOOKS WORK

You can access most workbook features from the Workbook Contents window. The workbook is a new feature in Microsoft Excel and requires several new terms. For definitions of common workbook terms, see the &quot;Workbook Glossary&quot; at the end of this Application Note.

HOW WORKBOOKS ARE SIMILAR TO MICROSOFT EXCEL VERSION 3.0 WORKSPACES

Microsoft Excel version 4.0 workbooks replace Microsoft Excel version 3.0 workspaces. When you open a workspace, Microsoft Excel converts it to a workbook with unbound documents. You cannot save a workspace in Microsoft Excel version 4.0.

All the functionality of workspaces is available within workbooks. Table 1 below shows the similarities and differences between the way the settings are saved in workspaces and workbooks.

Saved in a  Saved in Setting                                        workspace? a workbook? ---                                       --   ---

Position and size of the document window          Yes          Yes Options in the Workspace Options dialog box       Yes          Yes (Display Scroll Bars, for example) Options in the Display Options dialog box         Yes          Yes (Cell Gridlines, for example) Your preferred chart type                         Yes          Yes A document's opened or closed state               No           Yes A document's hidden or displayed state            Yes          Yes Info window settings                              Yes          Yes Pointers to external Microsoft Excel files        Yes          Yes Whole Microsoft Excel files                       No           Yes

Table 1--Saving: Workspace vs. Workbook --- Two additional important differences between workspaces and workbooks are:
 * A workbook has a unique window (the Workbook Contents window) through which you can use many workbook features. This window is shown in the previous illustration. Workspaces are never directly visible in a Microsoft Excel version 3.0 screen.
 * A workbook can contain bound documents that do not exist outside the workbook file and can point to external, unbound documents that do exist as separate files. A workspace points only to external files.

HOW WORKBOOKS ARE SIMILAR TO LOTUS .WK3 FILES

Microsoft Excel workbooks offer most of the functionality of Lotus 1-2-3 .WK3 3-D files, as well as some additional functionality not available in 3-D files.

Both allow you to:


 * Store multiple worksheets in one file.
 * Perform group editing and formatting.
 * Write cross-sheet formulas that do not require external references.
 * Include worksheets, macro sheets, and charts in a single file.

Workbooks also allow you to:
 * View more than three worksheets simultaneously.
 * Include pointers to external (unbound) Microsoft Excel documents.
 * Give bound documents extended names of up to 31 characters.

HOW WORKBOOKS AFFECT MEMORY RESOURCES

Microsoft Excel treats each bound document in a workbook as a separate window to manage. This means that opening a workbook that contains 25 bound worksheets, regardless of individual worksheet size, requires about the same memory resources as opening 25 separate worksheets in Microsoft Excel.

If you open a Lotus 1-2-3 3-D file in Microsoft Excel, Microsoft Excel converts it into a workbook with bound worksheets. In Microsoft Excel for Windows, you may encounter performance problems when opening and converting large 3-D files (those with more than about 25 plies). Before you open a large 3-D file, try first closing other applications and Microsoft Excel documents. When the 3-D file opens and converts to a workbook, unbind as many documents as possible, then close the unbound documents you do not need at the moment. Microsoft Excel will open these as you need them.

CREATING WORKBOOK REFERENCE FORMULAS

A workbook reference formula acts upon the same cell in contiguous worksheets bound in a workbook. This provides functionality similar to data consolidation within a workbook. You can use internal workbook references only with bound documents.

For example, the workbook SALES.XLW contains four bound worksheets: QUARTER1 through QUARTER4. All four worksheets have an identical layout, with the quarterly sales total in cell F10 of each worksheet. The fifth worksheet is the summary. It may be bound or unbound. Its annual sales total cell contains the following formula:

=SUM([SALES.XLW]Quarter1:Quarter4!F10)

Note that the worksheets referenced in the formula above have names that follow the MS-DOS file-naming conventions, even though they re bound. If you had given the first referenced worksheet the extended name &quot;First Quarter,&quot; the SUM formula would look like the following:

=SUM('[SALES.XLW]First Quarter:Quarter4'!F10)

Microsoft Excel encloses the workbook and worksheet reference in single quotation marks. When you enter a workbook reference formula for a range of bound worksheets with extended names, you must include the single quotation marks if either the first or last referenced worksheet has an extended name.

REFERENCING WORKSHEETS BOUND IN A WORKBOOK

Because a bound worksheet does not exist as a separate file outside the workbook file, you must include the workbook filename in your reference. For example, the workbook SALES.XLW includes the bound document QTR 3 SALES. A reference to cell D5 would look like this in Microsoft Excel for Windows:

='[SALES.XLW]QTR 3 SALES'!$D$5

Note that the bound document QTR 3 SALES does not follow the MS-DOS file-naming conventions. The workbook file SALES.XLW does, however.

VIEWING WORKBOOK DOCUMENTS IN MULTIPLE WINDOWS

By default, Microsoft Excel displays all documents in a workbook in the same window. You simply page through the workbook documents sequentially, like turning pages in a book. However, you can display workbook documents in separate windows. You can also simultaneously display the same workbook document in multiple windows.

To open a new window of a workbook document:


 * 1) Select the document in the Workbook Contents window or page to it within the workbook.
 * 2) From the Window menu, choose New Window.

You can also hold down CTRL in Microsoft Excel for Windows or OPTION in Microsoft Excel for the Macintosh and double-click the document in the Workbook Contents window. Once you've opened a new window of a workbook document, Microsoft Excel may temporarily close other windows as you page through the workbook.

PRINTING ALL DOCUMENTS IN A WORKBOOK

Microsoft Excel prints the workbook documents in the order they are listed in the Workbook Contents window.

To print all documents in a workbook

 Make any document in the workbook the active document. From the Options menu, choose Group Edit. In the Select Group box, Microsoft Excel displays a list of all open, nonhidden worksheets and macro sheets. The workbook documents are selected for you. From the File menu, choose Print. In the Print dialog box, choose the print options you want.

DISTRIBUTING WORKBOOK DOCUMENTS ACROSS A NETWORK

Because unbound documents are not physically stored within a workbook file, you can include an unbound document in several workbooks. These workbooks may be stored on different computers or network drives. For example, you could produce and distribute one workbook that contains one bound and one unbound document. So long as all users of the workbook have access to a common network drive, you can store the unbound document on that drive. This feature is handy for ensuring that a group of users always has access to your most current macro sheet, for example.

After the first user opens the unbound document, Microsoft Excel prompts subsequent users to open it as a read-only document. This prevents several users from attempting to modify the same document at the same time.

NAVIGATING THROUGH WORKBOOKS WITH THE KEYBOARD

You can use the keyboard to page through a workbook. The following table lists the keyboard actions you can take within a workbook.

In             On the To                                Windows press   Macintosh press --                                -   ---

Select a document in the Workbook Arrow keys      Arrow keys Contents window Select the first document in the  HOME            HOME Workbook Contents window Select the last document in the   END             END Workbook Contents window Page to the next document in the  ALT+PAGE DOWN   OPTION+PAGE DOWN workbook Page to the previous document in  ALT+PAGE UP     OPTION+PAGE UP the workbook Table 2--Keyboard Shortcuts --- WORKBOOK GLOSSARY

&quot;bound document&quot;    A Microsoft Excel document that is stored within the workbook file. Because a bound document is not a distinct file, you can give it an extended name of up to 31 characters. The workbook file must still follow your operating system s file-naming conventions (8-character MS-DOS filenames with 3-character extensions, for example). &quot;extended name&quot;    A name of up to 31 characters that you can give to bound workbook documents. These appear in the Workbook Contents window and in the document title bar along with the workbook filename. Extended names are especially useful for users of Microsoft Excel for Windows, because they allow you to sidestep the MS-DOS 11-character file-naming convention. This is possible because bound workbook documents are part of the workbook file, not a separate file. &quot;paging&quot;    Sequentially displaying the next or previous workbook document in a single window. All workbook documents display paging buttons in the lower-right corner of the document window. Click these buttons to move to the next or previous document, or to the Workbook Contents window. &quot;unbound document&quot;    A Microsoft Excel file that is stored separately from a workbook file but appears in one or more Workbook Contents windows. Unbound documents must follow your operating system s file-naming conventions. &quot;Workbook Contents window&quot;    The Workbook Contents window is analogous to the contents page of a book. In the Workbook Contents window you can add, remove, rename, and reorder documents in a workbook. You can also display a document in the workbook by double- clicking its name in the Workbook Contents window. &quot;workbook documents&quot;    Microsoft Excel worksheets, charts, and macro sheets that are listed in a Workbook Contents window. Workbook documents can be bound in a single workbook or be unbound in one or more workbooks. &quot;workspace&quot;    The Microsoft Excel version 3.0 document type that workbooks replace. The documents that a workspace file points to must be stored as separate files. With workbooks, however, you can store several documents in one workbook file. TO OBTAIN THIS APPLICATION NOTE =============================== You can find by.SEA, a self-extracting file, on the following services:   Microsoft's World Wide Web Site on the Internet On the www.microsoft.com home page, click the Support icon. Click Knowledge Base, and select the product. Enter kbfile by.SEA, and click GO! Open the article, and click the button to download the file.   Internet (anonymous FTP) ftp ftp.microsoft.com Change to the Softlib/Mslfiles folder. Get by.SEA   The Microsoft Network On the Edit menu, click Go To, and then click Other Location. Type &quot;mssupport&quot; (without the quotation marks). Double-click the MS Software Library icon. Find the appropriate product area. Locate and Download by.SEA.   Microsoft Download Service (MSDL) Dial (206) 936-6735 to connect to MSDL Download by.SEA </li></ul>

For additional information about downloading, please see the following article in the Microsoft Knowledge Base: ARTICLE-ID: Q119591 TITLE    : How to Obtain Microsoft Support Files from Online Services
 * You can have this Application Note mailed or faxed to you from the automated Microsoft FastTips Technical Library, which you can call 24 hours a day, 7 days a week at (800) 936-4100. NOTE: The FastTips Technical Library is available only to customers within the U.S. and Canada.
 * If you are unable to access the source(s) listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (206) 635-7070. If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, call Microsoft International Customer Service at (206) 936-8661.
 * }

-

"THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY." '' ©1997 Microsoft Corporation. All rights reserved. Legal Notices.

''

KBCategory: kbusage kbfile

KBSubcategory:

Additional reference words: 4.00 4.00a