Excel AppNote: Most Frequently Asked Questions Part 2 of 2

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
• Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

The Application Note "Most Frequently Asked Questions" (XE0800) is now available from Microsoft Technical Support. 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 programs.

You can obtain this Application Note from the following sources:

• Microsoft's World Wide Web Site on the Internet
• The Internet (Microsoft anonymous ftp server)
• Microsoft Download Service (MSDL)
• Microsoft FastTips Technical Library
• Microsoft Technical Support

The following is part 2 of 2 of this Application Note.

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q104235
   TITLE     : "Excel AppNote: Most Frequently Asked Questions Part 1 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.                                                       |
     --------------------------------------------------------------------


                               WORKBOOKS
                               =========

DISPLAYING WORKBOOK FILES

How can I see all the files I have in my workbook?

To arrange your workbook documents so you can see more than one document on your screen at a time, open each document in a window separate from the Workbook Contents screen:

1. Switch to the Workbook Contents window by clicking the Workbook Contents icon in the lower-right corner of the document.

2. To open a document in a separate window, hold down the CTRL key if you are using Microsoft Excel for Windows or the COMMAND key if you are using Microsoft Excel for the Macintosh, and double-click the document in the Workbook Contents window. Repeat this step for each document you want to view.

3. From the Window menu, choose Arrange. Enable the Windows Of Active Workbook option and choose OK.

COPYING WORKBOOK FILES

How can I make a copy of a file in my workbook?

You can copy or move a workbook document from one workbook to another using the drag and drop method or the Copy and Paste commands. To copy a workbook document into a sheet that is not part of a workbook:

1. From the Workbook Contents window, double-click the document name to activate the document you want to copy.

2. Choose the Select All button to the left of the column headings.

3. From the Edit menu, choose Copy.

4. From the File menu, choose New. Select Worksheet and choose OK.

5. From the Edit menu, choose Paste. You will now have a separate copy of the workbook document, which you can save.

                               CHARTING
                               ========

ADDING NEW SERIES INFORMATION

When I add additional data to my spreadsheet, can I update my chart without re-creating it?

To update a chart when you add additional data to an existing series or when you want to add a new series, use the ChartWizard. For example, if you present the following data in a chart,

       |   A         |      B    |    C
   ----|-------------|-----------|---------
   1   |             |     1990  |   1991
   ----|-------------|-----------|---------
   2   | Productx    |    $100   |  $350
   ----|-------------|-----------|---------
   3   | Producty    |    $200   |  $500
   ----|-------------|-----------|---------
   4   | Productz    |    $300   |  $700

1. If your chart is a chart object, select the chart by clicking it once. If your chart is a separate chart window, activate it.

2. Choose the ChartWizard button on the Chart toolbar.

3. In the ChartWizard Step 1 Of 2 dialog box, change the Range to include the data you added. Using the sample data, if you add data to the existing series (1990 and 1991), you would enlarge the range from $A$1:$C$4 to $A$1:$C$5 to include the new row. If you add a new series, enlarge the range from $A$1:$C$4 to $A$1:$D$4, so that it includes the new column.

         NOTE: When your new data is entered in cells that are not adjacent
         to the range of cells your chart is linked to, separate the data
         ranges with a comma when you update the chart. For example, if you
         want to add a new series to your chart and you've entered the data
         in cells E1:E4, in the Range box in the ChartWizard dialog box,
         change your reference to $A$1:$C$4,$E$1:$E$4.
   

  TIP: You can use defined names in place of actual cell ranges. This
  option enables you to update the chart by redefining the named range
  to include the new data.

CHANGING THE PLOTTING ORDER

Microsoft Excel plotted my chart backwards. The information in the y- axis should be in the x-axis and vice versa. How can I change this?

Microsoft Excel uses the shape of the worksheet selection to determine whether rows or columns form the data series. Microsoft Excel assumes you want fewer series than categories. So, if the worksheet selection has more rows than columns, Microsoft Excel plots each column as a separate data series and plots each row as a separate category. Similarly, if the worksheet selection has more columns than rows, Microsoft Excel plots each row as a separate data series and each column as a separate category.

To reverse Microsoft Excel's default plotting assumptions, use the ChartWizard to create the chart, or, if you have already created the chart, use the ChartWizard to change the defaults:

1. If you have not yet created your chart, select the data on the worksheet that you want your chart to be based on.

   -or-

   If your chart is already created and your chart is a chart object, select the chart by clicking it once. If your chart is a separate chart window, activate it.

2. Select the ChartWizard tool.

3. When you reach step 4 of 5 (new chart) or step 2 of 2 (edited chart), select either columns or rows. On the left, a sample of what your chart will look like is displayed. If you want the axes reversed, select the alternate option under Data Series In. For example, if Microsoft Excel has defaulted to rows, select columns. Verify that your chart is displayed correctly and choose the OK button.

ADDING A SECOND Y-AXIS

The information my data series is based on refers to two different kinds of numbers. As a result, my columns are large for one data series and very small for the other data series. Is it possible to have a second y-axis with a different scale?

In Microsoft Excel, you can use an overlay chart to plot different types of data on the same chart. An overlay chart is a second chart plotted on top of the main chart in the same chart window. The overlay can be a different chart type or have a different scale or both. For example, if you have one data series that consists of numbers of units sold and a second data series that consists of revenues, the values representing units sold and those representing revenues are different types of information. When you plot these two types of data on the same chart, if you use only one y-axis, the data markers may be skewed if the revenues are in a significantly higher range of values than the units sold. Instead, to meaningfully represent the different types of information, add an overlay chart with a second y-axis.

To add an overlay chart, do one of the following:

• If you've just created a new chart and haven't yet customized it, use the Combination command on the Gallery menu. Select one of the combined chart options and choose OK.

      -or-
  

• If you've already customized the chart and want to preserve the formatting, use the Add Overlay command on the Chart menu.

  NOTE: If you are working with a chart object on your worksheet and
  you want to add or edit an overlay chart, you must open the chart in
  its own window by double-clicking it.

             USING MICROSOFT EXCEL WITH OTHER APPLICATIONS
            ==============================================

MICROSOFT WORD

I want to copy a chart I created in Microsoft Excel and paste it into Microsoft Word so that it will be updated when I change it in Microsoft Excel. What is the best way to do this?

You can use the Paste Link feature available both in Microsoft Excel and Microsoft Word to link a Microsoft Excel object to a Microsoft Word document. To link a Microsoft Excel object to a Microsoft Word document, use the appropriate procedure below for your version of Microsoft Word.

Microsoft Word Version 2.0 for Windows

To paste a chart in a Microsoft Word version 2.0 for Windows document so that it will be updated when you change the original chart in Microsoft Excel:

1. In Microsoft Excel, if your chart is a chart object on your worksheet, select it by clicking it once. From the Edit menu, choose Copy. (If your chart is a separate chart document, activate it. From the Chart menu, choose Select Chart and choose Copy from the Edit menu.)

2. Start Word.

3. In the Word document, position the cursor where you want to paste the chart.

4. From the Edit menu, choose Paste Special. This will display the Paste Special dialog box.

5. Under Data Type, select Picture and choose Paste Link.

When you use the Paste Link command to paste a chart as a bitmap, the text may appear distorted--particularly if you resize the image. When you use the Paste Link command to link a chart as a picture, printer and font information is pasted with it. As a result, the image will be displayed and printed as it is in Microsoft Excel.

Microsoft Word Versions 4.0, 5.0, and 5.1 for the Macintosh

You can copy a chart in Microsoft Excel for the Macintosh and link it to a Word for the Macintosh document. The method for copying the chart in Microsoft Excel is the same regardless of your version of Word or your version of the Macintosh system software.

To copy the chart in Microsoft Excel:

1. If you want the chart to be pasted in color in Word, choose Print from the File menu and select the Print Using Color and Print Preview options and choose Print. When the Print Preview window is displayed, choose Close.

2. If you're copying a chart object on your worksheet, double-click it to open it in its own window (a chart object cannot be linked when pasted in Word). From the Chart menu, choose Select Chart. (If your chart is already a separate chart document, activate it, and choose Select Chart from the Chart menu.)

3. Hold down the SHIFT key and choose the Copy Picture command from the Edit menu. Under Appearance, select the As Shown On Screen option. Under Size, select the option you want and choose OK.

4. Switch to Word and position your insertion point where you want the chart.

   For Word version    Follow this procedure
   ---------------------------------------------------------------------
   
   4.0                 From the Edit menu, choose Paste Link.
   5.0 or 5.1          From the Edit menu, choose Paste Special. Select
                       Picture and choose Paste Link.

• If you are using Macintosh system software version 6.x, the link between the chart in Word and the original chart in Microsoft Excel is not dynamic; that is, it will not be updated automatically after the chart is changed in Microsoft Excel. To manually update the chart:

     For Word version    Follow this procedure
     ----------------------------------------------------------------------
     
     4.0                 From the Edit menu, choose Update Links.
     5.0 or 5.1          From the Edit menu, choose Link Options and choose
                         Update Now.
  
  

• If you are using Macintosh system software version 7 or 7.0.1, the link between the chart picture in Word versions 5.0 or 5.1 and Microsoft Excel is dynamic; it will be updated automatically after the chart is changed in Microsoft Excel as long as Automatically is selected in the Link Options dialog box in Word. Dynamic linking is not supported in Word 4.0 using System 7.0 AppleEvents; to update your chart in Word 4.0, choose Update Links from the Edit menu.

DATABASE APPLICATIONS

I need to extract data from an external database. Can I do this with Microsoft Excel?

Microsoft Excel for Windows provides a database front-end application, Q+E(R), along with supporting add-in macros, that allows you to manipulate, update, and extract information from a variety of database systems. These include dBASE(R), Microsoft SQL Server, ORACLE(R), OS/2(R) Extended Edition Database, and DEC(R) RDB files.

For additional information on setting up and using Q+E, see the "Q+E for Microsoft Excel User's Guide."

Microsoft Excel for the Macintosh ships with an add-in macro called the Data Access macro. This macro allows Microsoft Excel to use the Data Access Language (DAL) extension created by Apple Computer, Inc. You can use this macro to access data from external databases and transfer query results to Microsoft Excel worksheets or to separate files. To use the Data Access Macro, you need the DAL system extension and the Data Access software, both available from Apple Computer, as well as network connections from your Macintosh to the external database server.

TO OBTAIN THIS APPLICATION NOTE

The following file is available for download from the Microsoft Software Library:

 ~ XE0800.EXE (size: 36445 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services

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 (425) 635-7080 (Microsoft Excel for the Macintosh) (425) 635-7070 (Microsoft Excel for Windows). If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at:

   http://www.microsoft.com/worldwide/default.htm