Microsoft KB Archive/104035: Difference between revisions

From BetaArchive Wiki
m (Text replacement - """ to """)
m (Text replacement - "<" to "<")
Line 291: Line 291:
  ==============================================
  ==============================================


  The status bar is displaying the message "Circular:&lt;Cell Reference&gt;,"
  The status bar is displaying the message "Circular:<Cell Reference&gt;,"
  (where &lt;Cell Reference&gt; is an actual cell reference such as A1). What
  (where <Cell Reference&gt; is an actual cell reference such as A1). What
  does this mean and how can I correct it?
  does this mean and how can I correct it?


Line 950: Line 950:
  exists. If there is not an exact match, the function will return the
  exists. If there is not an exact match, the function will return the
  largest value that is less than or equal to the value you've specified
  largest value that is less than or equal to the value you've specified
  for the &lt;lookup_value&gt; argument. In addition, the first column in your
  for the <lookup_value&gt; argument. In addition, the first column in your
  lookup table must be sorted in ascending order. To ensure that a match
  lookup table must be sorted in ascending order. To ensure that a match
  is exact for a given &lt;lookup_value&gt; and/or to find it in an unsorted
  is exact for a given <lookup_value&gt; and/or to find it in an unsorted
  table, use a combination of the INDEX() and MATCH() functions as
  table, use a combination of the INDEX() and MATCH() functions as
  follows
  follows
Line 959: Line 959:
     lookup_array,match_type),col_index_num)
     lookup_array,match_type),col_index_num)


  where &lt;table_array&gt; is the entire lookup table, &lt;lookup_value&gt; is the
  where <table_array&gt; is the entire lookup table, <lookup_value&gt; is the
  value used to find the value wanted in the table, &lt;lookup_array&gt; is
  value used to find the value wanted in the table, <lookup_array&gt; is
  the range of cells containing possible lookup values, &lt;match_type&gt; is
  the range of cells containing possible lookup values, <match_type&gt; is
  a number (-1, 0, or 1) specifying how you want your &lt;lookup_value&gt; to be
  a number (-1, 0, or 1) specifying how you want your <lookup_value&gt; to be
  matched, and &lt;col_index_num&gt; is the column number in the &lt;lookup_array&gt;
  matched, and <col_index_num&gt; is the column number in the <lookup_array&gt;
  for which the matching value should be returned.
  for which the matching value should be returned.


         NOTE: If the &lt;match_type&gt; argument is 0, MATCH() finds the
         NOTE: If the <match_type&gt; argument is 0, MATCH() finds the
         first value that is exactly equal to the &lt;lookup_value&gt; and the
         first value that is exactly equal to the <lookup_value&gt; and the
         &lt;lookup_array&gt; does not need to be sorted.
         <lookup_array&gt; does not need to be sorted.


  If none of the cells in the &lt;lookup_array&gt; argument match those in the
  If none of the cells in the <lookup_array&gt; argument match those in the
  &lt;lookup_value&gt; argument, this formula will return the #N/A error
  <lookup_value&gt; argument, this formula will return the #N/A error
  value.
  value.


Line 1,055: Line 1,055:
   =LINEST(known_y's,known_x's,const,stats)
   =LINEST(known_y's,known_x's,const,stats)


  By setting the &lt;stats&gt; argument to TRUE, you instruct Microsoft Excel
  By setting the <stats&gt; argument to TRUE, you instruct Microsoft Excel
  to return the additional statistics. If &lt;stats&gt; is set to FALSE, the
  to return the additional statistics. If <stats&gt; is set to FALSE, the
  LINEST() function will return only the slope and the y intercept.
  LINEST() function will return only the slope and the y intercept.
  (See pages 254-258 of the "Function Reference" for a complete
  (See pages 254-258 of the "Function Reference" for a complete
  discussion of the &lt;known_y's&gt;, &lt;known_x's&gt;,and &lt;const&gt; arguments.)
  discussion of the <known_y's&gt;, <known_x's&gt;,and <const&gt; arguments.)


  To create a LINEST() formula for the following table:
  To create a LINEST() formula for the following table:
Line 1,127: Line 1,127:
  LINEST() function to calculate the additional regression statistics
  LINEST() function to calculate the additional regression statistics
  including the correlation coefficient. In Microsoft Excel 4.0, the
  including the correlation coefficient. In Microsoft Excel 4.0, the
  RSQ() function uses the &lt;known_y's&gt; and &lt;known_x's&gt; arguments to
  RSQ() function uses the <known_y's&gt; and <known_x's&gt; arguments to
  perform this calculation automatically. It returns a single value
  perform this calculation automatically. It returns a single value
  representing your correlation coefficient (that is, the value
  representing your correlation coefficient (that is, the value

Revision as of 09:16, 20 July 2020

Excel 4.x AppNote: Most Frequently Asked Questions Part 1 of 2



The information in this article applies to:


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





SUMMARY

The Application Note "Most Frequently Asked Questions" (XE0800) is now available. This Application Note provides detailed responses to some of the most frequently asked questions about Microsoft Excel version 4.0. The questions, which were derived from polling members of the Microsoft Excel technical support staff, are broken into eight functional categories. These categories include, tips and tricks, printing, working with text files, formulas and functions, macros, workbooks, and working with other applications.

You can download a Microsoft Word formatted version of this document. The following file is available for download from the Microsoft Download Center. Click the file name below to download the file:


Xe0800.exe

For more information about how to download files from the Microsoft Download Center, please visit the Download Center at the following Web address

http://www.microsoft.com/downloads/search.asp

and then click How to use the Microsoft Download Center.



MORE INFORMATION

The following is part 1 of 2 of the complete text of XE0800.

For additional information, please click the article number below to view the article in the Microsoft Knowledge Base:

Q104283 "Excel AppNote: Most Frequently Asked Questions Part 2 of 2"

The Text of XE0800

 ======================================================================
   Microsoft(R) Product Support Services Application Note (Text File)
                 XE0800: MOST FREQUENTLY ASKED QUESTIONS
 ======================================================================
                                                    Revision Date: 7/93
                                                      16 Pages, No Disk

     ---------------------------------------------------------------------
    | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY     |
    | ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
    | Note) IS PROVIDED "AS IS" 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 (C) 1993 Microsoft Corporation. All Rights Reserved       |
    |                                  .                                  |
    | Microsoft, Microsoft Press, and MS-DOS are registered trademarks    |
    | and Windows is a trademark of Microsoft Corporation.               |
    | Apple, Macintosh, and TrueType are registered trademarks and Geneva |
    | is a trademark of Apple Computer, Inc.                              |
    | dBASE is a registered trademark of Borland International, Inc.      |
    | DEC is a registered trademark of Digital Equipment Corporation.     |
    | OS/2 is a registered trademark of International Business Machines   |
    | Corporation.                                                        |
    | Helvetica and Times are registered trademarks of Lynotype AG and    |
    | its subsidiaries.                                                   |
    | 1-2-3 and Lotus are registered trademarks of Lotus Development      |
    | Corporation.                                                        |
    | Arial and Times New Roman are registered trademarks of The Monotype |
    | Corporation PLC.                                                    |
    | ORACLE is a registered trademark of Oracle Corporation.             |
    | Q+E is a registered trademark of Pioneer Software Systems           |
    | Corporation.                                                        |
      --------------------------------------------------------------------

 The following information applies to Microsoft Excel, version 4.0.


                                OVERVIEW
                                ========

 This Application Note provides detailed responses to some of the most
 frequently asked questions about Microsoft Excel version 4.0. The
 questions, which were derived from polling members of the Microsoft
 Excel technical support staff, are broken into eight functional
 categories. These categories include, tips and tricks, printing,
 working with text files, formulas and functions, macros, workbooks,
 and working with other applications.

 NOTE: Page number references are accurate ONLY in the printed
 application note; use them in this Knowledge Base article strictly as
 comparative reference points.

 Tips and Tricks                                                      2
    Editing Templates and Add-in Macros                               2
    Displaying Leading Zeros                                          2
    Omitting the Last Three Digits in a Large Value                   2
    Cell Protection                                                   3
    Working with and Resolving Circular References                    3
       Finding a Circular Reference with the Worksheet Auditor        3
    Entering Non-Keyboard Characters                                  4
       Microsoft Excel for Windows                                    4
       Microsoft Excel for the Macintosh                              5
       Creating a Custom Number Format with an Extended Character     5
    Using the Keyboard to Move Around and Select Cells                6

 Printing                                                             7
    Different Output/Different Printer                                7
       Microsoft Excel for Windows                                    7
       Microsoft Excel for the Macintosh                              7
    Disappearing Page Breaks                                          8

 Working with Text Files                                              8
    Importing Text Files                                              8
    Exporting Text Files                                              9
       Tab- and Comma-Delimited Files                                 9
      Space-Delimited Files (Flat Files)                            10
   Printing to a File                                               10

 Formulas and Functions                                              10
    Exact Match in a Lookup Table                                    10
    Combining Cell Contents                                          11
    LINEST() Function                                                11
    Correlation Coefficient                                          12

 Macros                                                              12
    Debugging Macros                                                 12
       Using the Step and Evaluate Commands                          12
       Using Key Commands to Evaluate Portions of a Macro Statement  12
       Using Key Commands to View Values Returned by Macro Statements12
       Running a Portion of the Macro                                12
    Learning More About Macros                                       13

 NOTE: The following three sections are available in part 2 of the
 Knowledge Base version of this Application Note.

 Workbooks                                                           13
    Displaying Workbook Files                                        13
    Copying Workbook Files                                           13

 Charting                                                            13
    Adding New Series Information                                    13
    Changing the Plotting Order                                      14
    Adding a Second Y-Axis                                           14

 Using Microsoft Excel with Other Applications                       15
    Microsoft Word                                                   15
       Microsoft Word Version 2.0 for Windows                        15
       Microsoft Word Versions 4.0, 5.0, and 5.1 for the Macintosh   15
    Database Applications                                            16
 ----------------------------------------------------------------------


                             TIPS AND TRICKS
                            ================


 EDITING TEMPLATES AND ADD-IN MACROS
 ===================================

 How can I open a template file or an add-in macro sheet so that I can
 edit it?

 To open a template file or an add-in macro sheet so that you can edit
 it, press SHIFT when you open the document:

 1. From the File menu, choose Open.

 2. In the Open dialog box, select the file you want to open and then
    press and hold down the SHIFT key and choose OK.


 DISPLAYING LEADING ZEROS
 ========================

 How can I display leading zeros in a value such as a ZIP Code?

 By default, Microsoft Excel drops leading zeros. To display leading
 zeros in a value, create a custom number format using zeros as the
 format symbols. When you use a zero as a digit placeholder, Microsoft
 Excel displays the extra zero even when the number of significant
 digits is fewer than the number of placeholders in the number format.
 For example, to display leading zeros in a five-digit number:

 1. Select the range of cells you want to format.

 2. From the Format menu, choose Number.

 3. In the Code box, type "00000" (without the quotation marks).

 4. Choose OK.

 This custom number format will force all entries in the selected range
 to have five digits. For example, 1204 will be displayed as 01204, and
 1 will be displayed as 00001.


 OMITTING THE LAST THREE DIGITS IN A LARGE VALUE
 ================================================

 How can I display a value without its last three, six, or nine digits?

 In Microsoft Excel, you can display numbers with the last three, six,
 or nine digits omitted by creating a custom number format. The number
 will retain its value--it will just be displayed differently.
 Displaying a number this way is useful when you have a worksheet
 containing numbers that are very large and would therefore be more
 readable if they were expressed in some number of units, such as
 millions of dollars or thousands of dollars.

 To display values so that a limited number of digits appear:

 1. From the Format menu, choose Number.

 2. In the Code box, type

       0,,

    where each comma represents three zeros.

 3. Choose OK.

 With this number format applied, if you enter 60,893,232 in a cell
 formatted with this custom number format, it will be displayed as 61
 (the millions are rounded up). However, calculations referencing that
 cell will use 60,893,232 as the value. If you enter a format of 0 in
 the Code box, 60,893,232 is displayed as 60893. If you want 60893 to
 be displayed with a comma, you can use the custom format #,###, (note
 the comma on the end). In this case, the number 60,893,232 will be
 displayed as 60,893.


 CELL PROTECTION
 ===============

 How can I lock or protect a specific area of cells on my worksheet?

 To format a cell with a cell protection status of locked or hidden or
 both, use the Cell Protection command on the Format menu. To enable
 the cell protection status, choose Protect Document from the Options
 menu.

 The default cell protection status for Microsoft Excel worksheets and
 macro sheets is Locked. This means that when you choose Protect
 Document from the Options menu, all the cells on your worksheet are
 locked and cannot be modified or formatted.

 To lock a specific range of cells but leave other cells unlocked, you
 must first select your entire worksheet and disable the locked
 protection status. To format a specific range to be locked:

 1. Select the entire worksheet by choosing the Select All button to
    the left of the column headings.

 2. From the Format menu, choose Cell Protection.

 3. Clear the Locked check box and choose OK.

 4. Select the range of cells you want to protect.

 5. From the Format menu, choose Cell Protection.

 6. Turn on the Locked option. Choose OK.

 7. To enable document protection, choose Protect Document from the
    Options menu. Select the appropriate settings and choose OK.

 You cannot enter data in the locked cells until you choose the
 Unprotect Document command from the Options menu.

   NOTE: While you can enter data in the cells that are not locked, you
   will not be able to use any formatting options once document
   protection is enabled. To turn off document protection, choose
   Unprotect Document from the Options menu.


 WORKING WITH AND RESOLVING CIRCULAR REFERENCES
 ==============================================

 The status bar is displaying the message "Circular:<Cell Reference>,"
 (where <Cell Reference> is an actual cell reference such as A1). What
 does this mean and how can I correct it?

 In Microsoft Excel, you can intentionally create formulas that depend
 on each other for their results. For example, if cell A1 contains the
 formula =B1 and cell B1 contains the formula =A1, a circular reference
 has been created because both formulas depend on each other for their
 answer. To calculate a circular reference, use the Iteration options
 in the Calculation Options dialog box (from the Options menu, choose
 Calculation).

 More commonly, circular references are not intentional and result from
 incorrect cell references in formulas or defined names. Microsoft
 Excel displays the message, "Circular:Cell Reference" in the status
 bar when an unresolved circular reference is on the active worksheet.
 If the message displays the word "Circular" by itself, an unresolved
 circular reference is on a worksheet that is currently open in
 Microsoft Excel. In this case, activate each open worksheet until the
 message displays a specific cell reference.

 Finding a Circular Reference with the Worksheet Auditor
 -------------------------------------------------------

 When the worksheet that contains the circular reference is active, you
 can use the Worksheet Auditor add-in macro to isolate the cause:

 1. From the File menu, choose Open. To load the Worksheet Auditor add-
    in macro in Microsoft Excel for Windows, change to the LIBRARY
    subdirectory (this directory should be located in the same
    directory where you installed Microsoft Excel); in Microsoft Excel
    for the Macintosh(R), change to the Macro Library folder (this
    folder should be located in the same folder where you installed
    Microsoft Excel).

 2. In Microsoft Excel for Windows, select AUDIT.XLA. In Microsoft
    Excel for the Macintosh, select Worksheet Auditor. Choose OK.

 3. From the Formula menu, choose Worksheet Auditor.

 4. In the Worksheet Auditor dialog box, select Generate Audit Report
    and choose OK.

 5. In the Audit Report dialog box, clear all the check boxes except
    Circular References. Choose OK.

 The time it takes to generate your Audit Report may vary depending on
 the number and complexity of the formulas in your worksheet. Once the
 Audit Report is complete, you'll have a complete list of all the
 flagged cells on your worksheet that are involved in the circular
 reference. You can use this list to sequentially check all the
 formulas and defined name references to locate the circular reference.


 ENTERING NON-KEYBOARD CHARACTERS
 ================================

 How do I enter special characters, such as a trademark or degree
 symbol, in a cell?

 Many characters not available on your keyboard (extended characters)
 can be used in a Microsoft Excel worksheet. For example, you may be
 able to add a degree or trademark symbol or an exponent or other
 mathematical expression to your sheet. The characters that are
 available depend on the font you use to format a cell(s).You add
 extended characters to your sheet using key combinations.

   NOTE: If you are using Microsoft Windows 3.1, you can also copy
   extended characters from Character Map; if you are using Microsoft
   Excel for the Macintosh, you can copy them from the Key Caps desk
   accessory.

 Microsoft Excel does not support individual character formatting such
 as multiple fonts within the same cell. Your cell must be formatted
 with the same font that contains the extended character you want to
 use. For example, if your cell is formatted with the Arial(R) font (if
 you are using the Windows operating system) or with the Geneva(TM)
 font (if you are using a Macintosh computer), the extended characters
 you can enter in the cell must be part of the Arial or Geneva
 character set, respectively.

 To add special characters to your worksheet, follow the appropriate
 procedure below.


 Microsoft Excel for Windows
 ---------------------------

 If you are using Microsoft Windows version 3.1, you can determine what
 characters are contained within a font character set by opening
 Character Map, which is located in the Accessories group in Windows
 Program Manager, and selecting the appropriate font from the Font
 list. When you select a character, its code is displayed in the lower-
 right corner of the Character Map dialog box. This code will consist
 of the ALT key plus a 4-digit number beginning with zero (0).

 To enter a character in a worksheet, use one of the following two
 methods:

  - Use the character code in the lower-right corner of the Character
    Map dialog box. For example, if you are using the Arial font, the
    code for the degree symbol is ALT+0176. To enter the degree symbol
    in a cell on your worksheet, hold down the ALT key and type 0176 on
    the numeric keypad.

    If you are combining an extended character with other text or
    numbers, for example 10 degrees Celsius, type the number 10 and
    then hold down the ALT key and type 0176 on the numeric keypad, (if
    you have only a 3-digit code, you need to include the zero [0] at
    the beginning of the number.) Release the ALT key and type the
    letter C.

     IMPORTANT: For these procedures to work correctly, you must use
     the numbers on the numeric keypad.

     -or-

  - Copy the character from Character Map and paste it into a cell in
    your worksheet. To copy the character, select it in Character Map
    and then choose the Select button. This will place the character in
    the Characters To Copy box. Choose the Copy button. Next, activate
   your Microsoft Excel worksheet and select the cell into which you
   want to paste the character and choose Paste from the Edit menu or
   press CTRL+V. If you're combining the character with other text or
   numbers, type your text, press CTRL+V and then complete your text
   and press ENTER.

  NOTE: Character Map is not available in Microsoft Windows version
  3.0. You can, however, use character codes in this version of
  Microsoft Windows. For a complete list of available characters and
  their codes, see page 568 of the "Microsoft Windows User's Guide"
  version 3.0.

 Microsoft Excel for the Macintosh
 ---------------------------------

 On the Macintosh, to determine what characters are contained within a
 particular font set, open the Key Caps desk accessory (located on the
 Apple(R) menu). Key Caps contains an on-screen keyboard and a box that
 will display whatever keys you select from the keyboard. You can
 change the Key Caps font by choosing a new font from the Key Caps
 menu. Initially, the keyboard displays all your normal keys. By
 pressing and holding down the CONTROL, COMMAND, OPTION, and SHIFT keys
 individually and in combination, the on-screen keyboard will display
 the extended characters that are part of the particular font set.

 To enter an extended character in your worksheet, use one of the
 following two methods:

  - In your worksheet, use the key combination that displays the
    character on the Key Caps keyboard. For example, if you are using
    the Geneva font, a degree symbol is displayed on the 8 key when you
    hold down the OPTION and SHIFT keys while in Key Caps. To enter the
    degree symbol in your worksheet, use the following key combination
    in the cell where you want the symbol to appear:

       OPTION+SHIFT+8

    If you are combining an extended character with other text or
    numbers, for example 10 degrees Celsius, type the number 10 and
    then hold down the OPTION and SHIFT keys simultaneously and press
    8. Release the OPTION, SHIFT, and 8 keys and type the letter C.

     -or-

  - To copy and paste the character into a cell, do the following:

     1. In Key Caps, type the key combination sequence required to
        display the character on the Key Caps keyboard. This will
        display the character on the display line.

     2. From the Edit menu, choose Copy or press COMMAND+C.

    3. Switch to Microsoft Excel, select the cell into which you want
       to paste the extended character in and choose Paste from the
      Edit menu or press COMMAND+V. Or, if you're combining the
       character with other text or numbers, type your text, press
       COMMAND+V and then complete your text and press RETURN.

 Creating a Custom Number Format with an Extended Character
 ----------------------------------------------------------

 When you enter an extended character directly in a cell with or
 without other numbers or text, the entry will always be entered as
 text. If you want the entry to be treated as a value (for example, to
 add or average a series of temperatures), create a custom number
 format:

 1. Select the range of cells you want to format.

 2. From the Format menu, choose Number.

 3. Follow the appropriate procedure for your version of Microsoft
    Excel:

    - If you are using Microsoft Excel for Windows, type 0 (zero) in
      the Code box, and then hold down the ALT key and type 0176 on the
      numeric keypad. Release the ALT key and type the letter C.

    - If you are using Microsoft Excel for the Macintosh, type 0 (zero)
      in the Code box, and then hold down the OPTION and SHIFT keys
      while typing 8. Release the OPTION and SHIFT keys and type the
      letter C.

 When you enter a value in one of the selected cells, it will be
 followed by a degree symbol and the letter c. Note that if you are
 using a custom number format for an exponent such as 2 squared, the
 exponent portion is treated as text and thereby ignored in any
 calculation. For example, 2 squared plus 2 squared will calculate to 4
 squared. Only the 2's are added and the cell containing the sum takes
 its number format from the cells being added, resulting in the
 exponent of 2 being appended to the sum.


 USING THE KEYBOARD TO MOVE AROUND AND SELECT CELLS
 ===================================================

 What are some keyboard shortcuts for moving and selecting areas on my
 worksheet?

 The following table lists some common keyboard shortcuts for moving
 and selecting areas on a worksheet. Keys that must be pressed
 sequentially are separated by a comma; keys that must be pressed at
 the same time are separated by a plus sign. In a few cases, there are
 two key combinations that perform the same action.

  NOTE: If the Alternate Navigation Keys option is turned on, some of
   these keyboard shortcuts will behave differently. To turn off
   Alternate Navigation Keys, choose Workspace from the Options menu
   and clear the Alternate Navigation Keys check box.


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

    Move to the beginning of  CTRL+HOME             COMMAND+HOME
    the sheet

    Move to the last cell in  CTRL+END              COMMAND+END
    the sheet

    Move to the edge of the   CTRL+arrow key        COMMAND+arrow key
    data block in the         END, arrow key        END, arrow key
    direction of the arrow
    key

    Select the entire row     SHIFT+SPACEBAR        SHIFT+SPACEBAR

    Select the entire column  CTRL+SPACEBAR         CTRL+SPACEBAR

    Select the entire         CTRL+SHIFT+SPACEBAR   COMMAND+SHIFT+SPACEBAR
    worksheet if a cell is                          COMMAND+A
    selected; if an object
    is selected, select the
    whole object

    Extend the selection to   CTRL+SHIFT+arrow key  COMMAND+SHIFT+arrow key
    the edge of the data      END, SHIFT+arrow key  END, SHIFT+arrow key
    block in the direction
    of the arrow key

   Extend the selection up   SHIFT+PAGE UP        SHIFT+PAGE UP
   one window

   Extend the selection      SHIFT+PAGE DOWN      SHIFT+PAGE DOWN
   down one window

   Extend the selection      CTRL+SHIFT+PAGE UP   COMMAND+SHIFT+PAGE UP
   left one window

   Extend the selection      CTRL+SHIFT+PAGE DOWN COMMAND+SHIFT+PAGE DOWN
   right one window

   Select the current data   CTRL+SHIFT+8         COMMAND+* (on numeric
   block                                          keypad)

   Switch to the next open   CTRL+F6              COMMAND+M
   document                                       COMMAND+F6 (extended
                                                  keyboard)

   Switch to the previous    CTRL+SHIFT+F6        COMMAND+SHIFT+M
   open document                                  COMMAND+SHIFT+F6
                                                  (extended keyboard)

   Switch to the next        ALT+PAGE DOWN        COMMAND+PAGE DOWN
   workbook document

   Switch to the previous    ALT+PAGE UP          COMMAND+PAGE UP
   workbook document

   Go to a specific cell     F5                   COMMAND+G or F5
   or range                                       (extended keyboard)


   Turn Extend mode on or    F8                   F8 (extended keyboard)
   off (used for extending
   a selection)

   Turn Add mode on/off      SHIFT+F8             SHIFT+F8 (extended)
   (used to add a
   nonadjacent selection)

   To extend a selection     SHIFT+click the      SHIFT+click the last
   from the active cell      last cell in the     cell in the selection
   (like Extend mode)        selection

   To add a nonadjacent      CTRL+click           CTRL+click
   selection (like Add mode)


                                PRINTING
                                ========

 DIFFERENT OUTPUT/DIFFERENT PRINTER
 ==================================

 I changed printers and now my worksheet is not fitting on one page.
 What has happened and how can I correct this?

 The most likely cause is that your screen fonts are mapping to a
 different set of printer fonts on the new printer. For more specific
 information about this problem and how to correct it, see the
 appropriate section below.

 Microsoft Excel for Windows
 ---------------------------

 When you change printers, in most cases you are using a different
 printer driver. This new driver may support a different set of fonts
 than the printer driver you were using. As a result, different printer
 fonts may be substituted, causing your printer output to appear
 different or to no longer fit on one page.

 The row heights and column widths on your worksheet are sized
 according to your Normal style font, the default font in Microsoft
 Excel. If the Normal style font is a screen font such as MS Sans Serif
 (the default), when you print, this font is matched, or mapped, to the
 closest matching font available on your printer. If the matching
 printer font is slightly larger or if it is a monospace font (such as
 Courier) rather than a proportional space font, your column widths,
 row heights, and text size may change in such a way that the text is
 truncated or fewer or more columns or rows fit onto a page.

 How to Correct the Problem

 If your data is not fitting as it once did but the text otherwise
 looks the same, you can use Microsoft Excel's scaling features to fit
 your document to the original number of pages. To do this, choose Page
 Setup from the File menu. Under Scaling, set the number of pages wide
 and tall you want your document to fit on.

 If your fonts look different, use a TrueType(R) font (Microsoft
 Windows 3.1) or a printer font (Microsoft Windows 3.0) for your Normal
 style font as well as when you apply font styles and sizes to
 individual cells. If you reformat your worksheet with a TrueType font
 such as Arial or Times New Roman(R), you'll be able to switch printers
 without any perceptible change (as long as TrueType fonts are
supported by your new printer driver). TrueType fonts function as both
 screen and printer fonts and are supported by nearly all printer
 drivers available in the Windows 3.1 operating environment. If you are
 using Windows 3.0, you'll get the best results if you format your
 sheet using a printer font and, when you change printers, you reformat
 your sheet with a font that is available on the new printer.

    NOTE: To distinguish among TrueType, screen, and printer fonts,
    from the Format menu, choose Font. In the Font list box, you'll see
    a list of available fonts. TrueType fonts will be preceded by a
    double T, printer fonts will be preceded by a printer symbol, and
    screen fonts will not be preceded any symbol.

 To change the Normal style font and the font applied to individual
 cells:

 1. From the Format menu, choose Style.

 2. Verify that Normal is selected in the Style Name box and choose
    Define.

 3. Under Style Includes, clear all the check boxes except Font.

 4. Under Change, choose the Font button.

 5. Select a font from the Font list.

     NOTE: If you are using Windows 3.1, select a TrueType font and if
     you are using Windows 3.0, select a printer font.

 6. Make other changes as needed in the Font dialog box and choose OK
    twice to return to your sheet.

 7. If you have formatted individual cells with a font size or style
   other than the Normal style font, select those cells and choose
   Font from the Format menu. If the font is not already a printer or
   a TrueType font, select one that is or select the same font you
   chose for your Normal style font. Clear the Font Style and the Size
   boxes; by doing this, you only change the font, not the style or
   size you previously selected. Choose OK.

 Microsoft Excel for the Macintosh
 ---------------------------------

 While some printers use the fonts that are displayed on your screen,
 other printers have built-in fonts that may be substituted. In this
 case, when you change printers, a different printer font may be
 substituted--this font substitution may cause your output to appear
 different or to no longer fit on one page.

 The row heights and column widths on your worksheet are sized
 according to your Normal style font, the default font in Microsoft
 Excel. If your Normal style font is a screen font such as Geneva (the
 default), when you print, this font is either substituted with the
 closest matching font available on your printer or your screen font is
 used. To determine if printer fonts are being substituted or not, from
 the File menu, choose Page Setup. If the option for Font Substitution
 is selected, printer fonts are being substituted for your screen
 fonts. In this case, since the Normal style font is a screen font and
 since this font determines your column widths and row heights, if the
 substituted printer font is slightly larger or if it is a monospace
 rather than a proportional space font, your column widths and row
 heights may change in such a way that text is truncated or fewer or
 more columns or rows fit onto a page.

 How to Correct the Problem

 When your data is not fitting as it once did but the text otherwise
 looks the same, you can use Microsoft Excel's scaling features to fit
 your document to the original number of pages: from the File menu,
 choose Page Setup, and under Scaling, set the number of pages wide and
 tall that you want your document to fit on.

 If your fonts look different, do one of the following:

  - To quickly correct the problem, turn off the Font Substitution
    option in the Page Setup dialog box. The quality of your printed
    output, may be decreased.

     -or-

  - To get the best results, format your sheet using a printer font for
    your Normal style font (follow the steps  above in this article)
    and leave the Font Substitution option enabled. Then, if you change
    printers, the previous printer font will be substituted with the
    closest matching font on the new printer. For a list of printer
   fonts, consult your printer documentation. The most common printer
    fonts are Times(R), Helvetica(R), and Courier.


 DISAPPEARING PAGE BREAKS
 ========================

 My manual page breaks are not displayed on my screen and they don't
 seem to be working when I print my worksheet. Why?

 This is most likely occurring because, in the Page Setup dialog box,
 you enabled the Fit To X Pages Wide By X Pages Tall option under
 Scaling to specify a certain scale. When you use this scaling option,
 manual page breaks do not appear on the worksheet and are ignored in
 print preview and the printed output.

 To emulate scaling a document to fit a certain number pages and have
 page breaks at specific rows and/or columns, use the Reduce/Enlarge
 Scaling option. To determine the reduction percentage required to fit
 a document to a certain number of pages, follow these steps:

 1. From the File menu, choose Page Setup.

 2. In the scaling section, select the Fit To option and indicate the
    desired number of pages wide and tall. Choose OK.

 3. From the File menu, choose Print Preview to force the document to
    be scaled according to the Fit To setting. Choose Close.

 4. Choose Page Setup from the File menu again. This time, select the
    Reduce/Enlarge option in the Scaling section. The percentage the
    document was scaled to while in print preview appears when this
    option is selected. Choose OK.

 If you now set manual page breaks, they are displayed correctly, your
 document breaks where specified, and it appears in the appropriate
 scale.


                         WORKING WITH TEXT FILES
                        =======================

 IMPORTING TEXT FILES
 ====================

 How can I import text files to Microsoft Excel from a third-party
 application? For example, I have some text files that I have
 downloaded from my company's mainframe system and I would like to
 bring these into Microsoft Excel 4.0. How can I do this?

 When you open a text file in Microsoft Excel, the way the text is
 broken into columns depends on the character you choose to delimit
 your fields or columns of text. You can also choose no delimiter and
 parse your text after the file is opened. The most common column
 delimiters are commas, tabs, and spaces.

 When you open a text file, Microsoft Excel checks the document for the
 column delimiter you selected in the Text File Options dialog box
 (from the File menu, choose Open and then choose the Text button). In
 this dialog box, the default Column Delimiter option is Tab; you can
 also select the Comma, Space, Semicolon, None, or Custom option (when
 you select Custom, you can specify any keyboard character). Once you
 choose a column delimiter, Microsoft Excel places the text following
 each instance of the specified delimiter in a separate cell. For
 example, if your text is delimited by spaces and if you select the
 Space delimiter, Microsoft Excel places the text following each space
 in a separate cell. This process is called parsing and occurs
 automatically when you specify a delimiter for your text file.

 If you do not select the Space delimiter before you open a space-
 delimited file, or if you select the None (no delimiter) option, the
 text in your file will not be parsed; that is, each line of text will
 appear in a separate row in column A only. In this case, after you
 open the file in Microsoft Excel, you can break the text into separate
 columns using one of the parsing commands.

    NOTE: If you open a text file with no delimiter specified, you are
    limited to 255 characters per row. All characters beyond 255 will
    not be imported for any given row.

 The parsing commands, located on the Data menu, are:

  - Parse - This command parses text based on a fixed length or number
    of characters per column, rather than parsing your text based on a
    specified character.

    For more information on opening and using the Parse command, see
    pages 150-151 in "User's Guide 2" or the "Parsing" topic in
    Microsoft Excel Help.

  - Smart Parse - This command is added to the Data menu when you load
    the Flat File add-in macro. Smart Parse gives you the option of
    parsing your data based on a specified delimiter: This is similar
    to specifying a delimiter in the Text File Options dialog box, with
    the exception that the parsing is done after you've opened the text
    file and that there is not an option to parse based on a tab
    delimiter.

    For more information on opening and using the Flat File add-in
    macro, see pages 151-152 in "User's Guide 2" or the "Flat File
    Macro" topic in Microsoft Excel Help.

     NOTE (Windows only): When you open a file with a .CSV extension,
     Microsoft Excel for Windows will always assume that this file is a
     comma-separated values (CSV) file regardless of the selected
     column delimiter. As a result, it will be automatically parsed
     using a comma delimiter; if there are no commas in the file, the
     text will not be parsed. For files with any other filename
     extension, .TXT for example, Microsoft Excel will use the
     delimiter you specify in the Text File Options dialog box.

 When your text is parsed and placed into separate columns in Microsoft
 Excel, you may find that some of the text entries in your file do not
 appear as expected. These unexpected results may occur because of the
 way Microsoft Excel automatically applies formatting to numbers and
 alphanumeric characters. If you have a text file in which this
 formatting is altering the values of your entries, there is an
 Application Note available, "Opening and Saving Text Files" (WE0801
 for Microsoft Excel for the Macintosh or ME0802 for Microsoft Excel
 for Windows), which includes a Text Reader add-in macro. When you use
this macro to import text files, your text is parsed according to a
 delimiter you select and each entry is formatted as text--this text
 formatting prevents Microsoft Excel from applying automatic number
 formatting.

 EXPORTING TEXT FILES
 ====================

 I created a worksheet in Microsoft Excel 4.0 and now need to export
 this file from Microsoft Excel to my mainframe. How can I do this?

 Just as you can import text files to Microsoft Excel, you can also
 save and export worksheet files in text format.


 Tab- and Comma-Delimited Files
 ------------------------------

 To save your file in either a tab-delimited (Text) or comma-delimited
 (CSV) file, follow the appropriate procedure below.

 If you are using Microsoft Excel for the Macintosh:

 1. From the File menu, choose Save As and choose the Options button.

 2. From the File Format list, select either Text format for a tab-
    delimited file or CSV for a comma-delimited file. Choose OK.

 3. If you want to rename your file, type a new name in the Save
    Worksheet As box and choose OK.

 If you are using Microsoft Excel for Windows:

 1. From the File menu, choose Save As.

 2. Under Save File As Type, select Text or CSV. Note that if you
    select Text, the filename extension changes to .TXT; similarly,
    when you select CSV, the filename extension automatically changes
    to .CSV.

 3. Choose OK.


 Space-Delimited Files (Flat Files)
 ----------------------------------

 If you want to save the file as a flat file (a file that uses spaces
 between the columns to delimit the file), you can use the Flat File
 add-in macro. When you load this macro, the Export command is added to
 your Data menu. To export your data:

 1. Select the area of the worksheet that you want to save to a flat
    file format.

 2. From the Data menu, choose Export (this command will be available
    only if the Flat File add-in macro is open). The add-in macro will
    prompt you for a filename. Type in a path and filename and choose
    Export. This file can now be opened in any application that
    requires an ASCII flat file.

 For more information on saving files from Microsoft Excel, see pages
 147-150 of "User's Guide 2." For more information on opening and using
 the Flat File add-in macro, see pages 151-152 in "User's Guide 2" or
 the "Flat File Macro" topic in Microsoft Excel Help.


 PRINTING TO A FILE
 ==================

 In Lotus(R) 1-2-3(R), I have the option to print to a file rather than
 the printer. This creates a text file with a .PRN filename extension.
 How can I do this in Microsoft Excel?

 When you print to a file in Lotus 1-2-3, a space-delimited text file
 is created. In Microsoft Excel, you can create a space-delimited text
 file by using the Export command that is available with the Flat File
 add-in macro. For detailed information about using this command, see
 the "Space-Delimited Files (Flat Files)" section above.



                        FORMULAS AND FUNCTIONS
                         =======================

 EXACT MATCH IN A LOOKUP TABLE
 =============================

 I want to create a lookup formula so that it will only find an exact
 match. If it doesn't, I want it to return the #N/A error value. In
 addition, I don't want to have to sort my table so that the first
 column is in ascending order. How can I do this?

 In Microsoft Excel, the lookup functions LOOKUP(), VLOOKUP(), and
 HLOOKUP() will find an exact match in a lookup table only when one
 exists. If there is not an exact match, the function will return the
 largest value that is less than or equal to the value you've specified
 for the <lookup_value> argument. In addition, the first column in your
 lookup table must be sorted in ascending order. To ensure that a match
 is exact for a given <lookup_value> and/or to find it in an unsorted
 table, use a combination of the INDEX() and MATCH() functions as
 follows

    =INDEX(table_array,MATCH(lookup_value,
     lookup_array,match_type),col_index_num)

 where <table_array> is the entire lookup table, <lookup_value> is the
 value used to find the value wanted in the table, <lookup_array> is
 the range of cells containing possible lookup values, <match_type> is
 a number (-1, 0, or 1) specifying how you want your <lookup_value> to be
 matched, and <col_index_num> is the column number in the <lookup_array>
 for which the matching value should be returned.

        NOTE: If the <match_type> argument is 0, MATCH() finds the
        first value that is exactly equal to the <lookup_value> and the
        <lookup_array> does not need to be sorted.

 If none of the cells in the <lookup_array> argument match those in the
 <lookup_value> argument, this formula will return the #N/A error
 value.

 For example, in the following table

       A         B        C
    --------------------------

    1 Name      Dept         Age
    ---------------------------
    2 Henry        501       28
    ---------------------------
    3 Stan         201       19
    ---------------------------
    4 Mary         101       22
    ---------------------------
    5 Larry        301       29

 since the "Name" range is not sorted, using VLOOKUP() to find the age
 of Mary returns 28, the age of Henry. To find the age of Mary, use the
 formula:

    =INDEX(A2:C5,MATCH("mary",A2:A5,0),3)

 This formula will return 22.


 COMBINING CELL CONTENTS
 ========================

 How can I combine the contents of two cells into one cell? I have a
 series of last names in one column and first names in a second column
 and I'd like to combine them into one cell.

 In Microsoft Excel, to join the contents of two or more cells into a
 single cell, use the ampersand to connect, or concatenate, the values
 of the cells. For example, you can join first and last names or you
 can combine a formula with text. The following table shows some common
 uses of the concatenation operator (&):

         A        B             C            Formula Used in Column C
   -------------------------------------------------------------------
   1   John    Doe      John Doe         =A1&" "&B1
   -------------------------------------------------------------------
   2   John    Doe      Doe, John        =B1&", "&A1
   -------------------------------------------------------------------
   3   John    Doe      JohnDoe          =A1&B1
   -------------------------------------------------------------------
   4
   -------------------------------------------------------------------
   5   10      5        The cost is
                        15 dollars       ="The Cost is "&SUM(A5:A6)&"
                                         dollars"

 Use quotation marks to add any text to the concatenated string and the
 ampersand (&) to add cell references or formulas. In the name
 examples, quotation marks are used to add a space between the first
 name and the last name (cell C1) and a comma and space between the
 last and the first name (cell C2). Cell C5 contains an example of how
 to add a formula in the middle of a text string. This can be useful
when you have a statement that uses the result of a formula.

   NOTE: If a referenced cell contains a formula, the returned value is
   displayed as a text value in the combined string.


 LINEST() FUNCTION
 ==================

 How can I return the additional regression statistics that are built
 into the LINEST() function?

 To retrieve the statistics that can be returned by the LINEST()
 function, you must first select an appropriately sized array on your
 worksheet. The array should be five rows high and two columns wide. If
 your original data includes more than one x variable, your array
 should include one extra column for each additional x variable. For
 example, if you have three x variables, your array will be five rows
 by four columns.

 The syntax of the LINEST() function is:

  =LINEST(known_y's,known_x's,const,stats)

 By setting the <stats> argument to TRUE, you instruct Microsoft Excel
 to return the additional statistics. If <stats> is set to FALSE, the
 LINEST() function will return only the slope and the y intercept.
 (See pages 254-258 of the "Function Reference" for a complete
 discussion of the <known_y's>, <known_x's>,and <const> arguments.)

 To create a LINEST() formula for the following table:

             A         B       C
    -------------------------------

   1   Known Y's   XVar1   XVar2
   2          200      15       76
   3          210      20       65
   4          195      23       66
   5          235      28       72
   6          250      36       80

 1. Because the data includes two x variables, select an array that is
    five rows by three columns. For example, you could select cells
    E2:G6.

 2. In the formula bar, type:

      =LINEST(A2:A6,B2:C6,,TRUE)

    NOTE: For this example, you can omit the third argument of the
    function.

    After typing the formula, you must press CTRL+SHIFT+ENTER in
    Microsoft Excel for Windows or COMMAND+RETURN in Microsoft Excel
    for the Macintosh to enter the formula as an array formula--you
    will see braces ({}) placed around the formula in the formula
    bar.

 3. Cells E3:F6 contain the additional regression statistics:

                 E           F        G
       ------------------------------------

       2     1.017709   2.22756   90.57607
       ------------------------------------
       3     1.167926  0.934885   76.37852
       ------------------------------------
       4     0.840776   13.3242    #N/A
       ------------------------------------
       5     5.280472         2    #N/A
       ------------------------------------
       6     1874.931  355.0688    #N/A

 The additional statistics are returned in the last three rows and
 first two columns of your array. If your array has more than the
 required number of columns (three in this example), #N/A errors will
 be returned in the extra columns. To determine what each returned
 value represents, refer to page 255 in the "Function Reference."

 You can also use the Regression tools to perform regression analysis,
 return the additional statistics, and automatically create best-fit-
 line plots. The Analysis ToolPak includes an add-in macro that
 provides statistical analysis tools. When you load this add-in macro,
 the Analysis Tools command is added to the Options menu. Selecting the
 Analysis Tools command will display a list of statistical tools.


CORRELATION COEFFICIENT
 =======================

 How can I retrieve the correlation coefficient?

 In earlier versions of Microsoft Excel, it was necessary to use the
 LINEST() function to calculate the additional regression statistics
 including the correlation coefficient. In Microsoft Excel 4.0, the
 RSQ() function uses the <known_y's> and <known_x's> arguments to
 perform this calculation automatically. It returns a single value
 representing your correlation coefficient (that is, the value
 of R squared).

 For additional information on using the RSQ() function, see pages 366-
 367 of the "Function Reference."


                                 MACROS
                                 ======


 DEBUGGING MACROS
 ================

 I need to debug a macro that I've written. What is the best way to do
 this?

 Most command-equivalent macro functions return the value FALSE before
 they are run, TRUE if they are run successfully, and FALSE or an error
 value if they don't run successfully. If your macro functions are
 returning an incorrect or unexpected result or if one or more macro
 commands cause your macro to halt in error, you can use any
 combination of the following methods to isolate and resolve the
 problem.

 Using the Step and Evaluate Commands
 ------------------------------------

 The step feature allows you to step through and evaluate your macro
 commands line by line. To use the step feature, run your macro by
 choosing Run from the Macro menu. After you select your macro from the
list, choose the Step button. You can then either step through or
 evaluate the individual lines of your macro code. Step takes you to
 the next macro command. Evaluate calculates each nested function
 within a line of macro code, one argument at a time--this is a useful
 way to isolate a problem within a specific line of a macro. While you
 are in step mode, you cannot edit your macro. To exit step mode to
 edit your macro, choose the Halt button.

 Using Key Commands to Evaluate Portions of a Macro Statement
 -------------------------------------------------------------

 To evaluate a portion of a statement or an entire line of code in your
 macro in the formula bar, select the area you want to evaluate and
 press F9 or, if you are using Microsoft Excel for the Macintosh, press
 and hold down the COMMAND key and then press the EQUAL SIGN (=). This
 will immediately calculate the selected portion of your function and
 display the value in the formula bar.

   CAUTION: Be sure to press the ESC key after you view the value;
   otherwise the original formula will be replaced with the displayed
   value.

 Using Key Commands to View Values Returned by Macro Statements
 ---------------------------------------------------------------

 To view the values returned by all statements on a macro sheet, press
 and hold down the CTRL key, or the COMMAND key if you are using
 Microsoft Excel for the Macintosh, and then press the ACCENT GRAVE (`)
 key. CTRL+ACCENT GRAVE (Windows) and COMMAND+ACCENT GRAVE (Macintosh)
 toggle between the view values mode and the view formulas mode. Press
 CTRL+ACCENT GRAVE or COMMAND+ACCENT GRAVE to return the macro sheet to
 view formulas mode. An alternative way to toggle between view values
 and view formulas is to choose Display from the Options menu and
 select or clear the Formula check box (when a macro sheet is active,
 the default view is view formulas).


 Running a Portion of the Macro
 ------------------------------

 If your macro is long, you may want to test small portions of it to
 isolate problems. To divide your macro into more manageable sections,
 do the following:

 1. Activate your macro sheet. At the end of the range of macro code
    that you want to test, insert a row and type "=HALT()" (without
    the quotation marks). This will stop execution at this point
    in the macro.

 2. Activate the document from which you are running your macro and
    choose Run from the Macro menu.

 3. With the dialog box active, choose the macro sheet name from the
    Window menu and select the cell containing the first line of macro
    code that you want to test. This will place the macro cell
    reference in the Run Macro dialog box.

 4. Choose OK or Step.

 The macro will stop where it encounters the HALT() function.


 LEARNING MORE ABOUT MACROS
 ==========================

 How can I learn more about writing macros?

 In addition to reviewing Chapters 6-7 in "User's Guide 2," you can use
 the Recorder command (located on the Macro menu) as a learning tool.
 You can use the macro recorder to learn command structure, syntax, and
 which macro commands perform which actions. When you record key
 combinations, menu command selections, and mouse actions, they are
 automatically placed in a macro sheet. You can then review this sheet
 to gain a better understanding of the macro functions that perform the
 actions you recorded. If you need to use non-command-equivalent
 functions, such as information, reference, or customizing functions,
 you must manually add these to your macro. For additional information
 on what functions fall into these categories, see pages xv-xliii in
 the "Function Reference."

For more information about recording a command macro, see pages 216-
 219 of "User's Guide 2."

 For more information about writing macros, see the "Complete Guide to
 Microsoft Excel Macros" by Charles Kyd and Chris Kinata, or "Microsoft
 Excel Macros Step by Step" by Steve Wexler and Julianne Sharer. To
 order these books, call Microsoft Press(R) at (800) 677-7377
 (800-MS-PRESS).
 -------------------------------------------------------------------- 

Additional query words: 4.00a

Keywords : kbappnote kbfile
Version : MACINTOSH:4.0; WINDOWS:4.0
Platform : MACINTOSH WINDOWS
Issue type : kbinfo
Technology :


Last Reviewed: December 4, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.