Microsoft KB Archive/107722

From BetaArchive Wiki

Microsoft Knowledge Base

Excel AppNote: Creating a Crosstab with Crosstab ReportWizard

Last reviewed: April 3, 1997
Article ID: Q107722

The information in this article applies to:

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

The Application Note "Creating a Crosstab Table with the Crosstab ReportWizard" (XE0872) is now available from Microsoft Product Support Services. This Application Note demonstrates how to use the Crosstab ReportWizard to create a crosstab table that resembles the one pictured on page 349 of "User's Guide 1," version 4.0.

    You can obtain this Application Note from the following sources:

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)
  • The Microsoft Network (MSN)
  • Microsoft Download Service (MSDL)
  • Microsoft FastTips Technical Library
  • Microsoft Product Support Services

    For complete information, see the "To Obtain This Application Note" section at the end of this article.

THE TEXT OF XE0872

  Microsoft(R) Product Support Services Application Note (Text File)
   XE0872: CREATING A CROSSTAB TABLE WITH THE CROSSTAB REPORTWIZARD
                                                  Revision Date: 11/93
                                                      No Disk Included

The following information applies to Microsoft Excel version 4.0.

| 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  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 and MS-DOS are registered trademarks of Microsoft         |
| Corporation.                                                        |
|---------------------------------------------------------------------|

Overview

This Application Note demonstrates how to use the Crosstab ReportWizard to create a crosstab table that resembles the one pictured on page 349 of "User's Guide 1," version 4.0.

Setting Up Your Sample Database

When you create a crosstab report, you must first set up a database. When you set up this database, use the following guidelines:

  • Field names should be in the first row and each subsequent row beneath should represent a record.
  • Field names cannot start with a number.
  • Do not leave blank cells in the database (to avoid blank cells, you can replace them with either a zero or the =NA() formula).
  • If you want to generate a crosstab report based on a set of criteria, you must make sure that the criteria is such that the records that are extracted by the Crosstab command meet the criteria.

For more information about creating a database, see Chapter 9 of "User's Guide 1," version 4.0.

To create the sample database below:

  1. From the File menu, choose New. Select the Worksheet option and choose OK.
  2. In the new worksheet, type the data exactly as it appears in the following table:

       |    A        |    B    |  C    |  D     |    E      |   F
    1  | Product     | Region  | Month | Actual | Projected | Salesperson
2  | Automobiles   East      Jan     $1.15    $1.19       Fred
3  | Automobiles   West      Jan     $2.65    $1.90       Tom
4  | Trucks        East      Jan     $1.50    $0.95       Fred
5  | Trucks        West      Jan     $1.04    $0.95       Tom
6  | Trucks        Central   Jan     $1.15    $0.95       Sue
7  | Automobiles   East      Feb     $1.80    $1.36       Sue
8  | Automobiles   West      Feb     $1.56    $0.97       Fred
9  | Trucks        East      Feb     $1.68    $1.21       Fred
10 | Trucks        West      Feb     $1.14    $0.73       Sue
11 | Trucks        Central   Feb     $1.38    $0.97       Sue
12 | Automobiles   East      Mar     $2.60    $1.75       Tom
13 | Automobiles   West      Mar     $2.47    $1.50       Sue
14 | Trucks        East      Mar     $1.95    $1.50       Fred
15 | Trucks        West      Mar     $1.30    $0.90       Tom
16 | Trucks        Central   Mar     $1.69    $1.25       Sue

Once you have entered the data, select the range of cells A1:F16 and choose the Set Database command from the Data menu. When you do this, you will see the word Database in the box to the left of the formula bar.

Creating a Crosstab Report

Once you've created your database, do the following to create your crosstab report:

  1. From the Data menu, choose Crosstab to activate the Crosstab

        ReportWizard and choose Create A New Crosstab.
        If the Crosstab command does not appear on the Data menu, run the
        Microsoft Excel Setup program to install the Crosstab add-in
        macro. For more information about adding or removing add-in
        macros, see "Managing Add-in Commands and Functions" in Chapter 4
        of "User's Guide 2," version 4.0.
  2. In the Row Categories dialog box, under Fields In Database, select

        the fields you want to appear as your row categories. To make a
        selection, select the appropriate field name and choose Add. This
        will cause the selected field to appear in the Include As Row
        Categories box. For this example, select the fields Product and
        Region. If you make a mistake and add the wrong field, select it
        in the Include As Row Categories box and choose the Remove button.
        The selections that you make in this dialog box will appear along
        the side of your crosstab report.
                               [Graphic deleted]
              The Crosstab ReportWizard Row Categories Dialog Box
    
  3. Once you've added the Product and Region fields to the Include As

        Row Categories box, select Product from the Include As Row
        Categories box and choose the Options button to move to the Row
        Category Options dialog box. This dialog box allows you to control
        the way the Crosstab ReportWizard aggregates your data.
                               [Graphic deleted]
           The Crosstab ReportWizard Row Category Options Dialog Box
    
  4. In the Row Category Options dialog box, under Insert Subtotal Rows

        For, select the Custom option and select the Sum check box. This
        will cause the Crosstab ReportWizard to generate sum totals for
        each product and region. Choose OK to return to the previous
        dialog box.
  5. Repeat steps 3 and 4 for the Region row category and then choose

        the Next button.
          NOTE: When you create your own reports, you may want to select
          one of the other Insert Subtotal Rows For options, such as
          Average or Count.
  6. In the Column Categories dialog box, under Fields In Database,

        select Month and choose the Add button. Choose the Next button to
        move to the next step.
          NOTE: Choosing the Options button in this dialog box gives you
          the same calculation options available in the Row Categories
          dialog box. In this example, you can accept the default (Sum), so
          you don't need to choose this button.
                               [Graphic deleted]
              Crosstab ReportWizard Column Categories Dialog Box
    
  7. In the Value Fields dialog box the fields that you select for your

        values are the fields that will be aggregated in the final
        crosstab report. In this dialog box, select the Actual and
        Projected fields. To do this, select Actual in the Fields In
        Database box, and choose the Add button. Repeat this procedure for
        the Projected field and then choose the Next button.
          NOTE: When the final crosstab is created, the Crosstab
          ReportWizard will break out the monthly actual and monthly
          projected values and sum them by product and region.
                               [Graphic deleted]
               The Crosstab ReportWizard Value Fields Dialog Box
    
  8. In the Multiple Value Field Layout dialog box, because you

        selected two value fields to aggregate (Actual and Projected), you
        need to specify how they should appear in the report. Select the
        Inner Columns option (the default). This option will place the
        actual and projected figures side by side, broken down by month.
        If you don't want to intersperse the actual and projected figures,
        use the Outer Columns option instead. This places all of the
        monthly actual figures together in one group and all of the
        monthly projected figures in another group. (To see an example of
        this type of crosstab table, see page 352 in "User's Guide 1,"
        version 4.0.)
          NOTE: The Inner Rows and Outer Rows formats are identical to the
          above descriptions, except that value fields are broken down by
          rows instead of columns.
                               [Graphic deleted]
         Crosstab ReportWizard Multiple Value Fields Layout Dialog Box
    
          NOTE: This dialog box will not be available if you select only
          one value field.
    
  9. To move to the Final dialog box, choose Next.
  10. In the Final dialog box, choose the Set Table Creation Options

        button, select No under Create An Outline In Excel For The
        Crosstab, and choose OK.
  11. Choose the Create It button.

The Crosstab ReportWizard opens a new worksheet and generates the crosstab report according to your specifications.

[Chart deleted]

  NOTE: The crosstab table is not automatically formatted. To format
  the table, you can apply an AutoFormat (from the Format menu, choose
  AutoFormat) or you can format it manually by choosing the
  appropriate commands from the Format menu.

Common Crosstab Error Messages

The following table lists some common error messages you may receive when you create a crosstab and outlines possible solutions and offers explanations for these problems.

If you get this   Do the following          Explanation

error message

No Data Records   From the Formula menu,    This error can occur
Retrieved.        choose Define name. In    if a criteria range is
                  the Define Name dialog    active on the
                  box, select Criteria      worksheet and it
                  from the list of defined  excludes the records
                  names, and choose the     that Crosstab is
                  Delete button. Press OK   attempting to
                  to return to the          aggregate.
                  spreadsheet.

                  -or-

                  Make your criteria less
                  restrictive.

Invalid Value in Use one of the Microsoft This error is caused

Group Field.      Excel built-in date       when you attempt to
                  formats.                  group by the date when
                                            you are using a custom
                                            date format.

Bad File          Make sure that you have   Crosstab requires disk
Operation. Try    write privileges to the   space to write
Freeing           directory from which you  temporary files to. In
Additional Disk   are running Microsoft     version 4.0, these
Space and         Excel and verify that     temporary files are
Repeating         there is space on this    written to the
Crosstab.         directory.                directory from which
                                            you are running
                                            Microsoft Excel.

Database Cannot   Verify that field names   Crosstab fails if the
Contain Blank     are not blank and do not  field names in the
Field Names.      begin with a number.      database are blank or
                                            begin with a numeric
                                            character. This
                                            behavior will occur
                                            even if the data type
                                            is text.

TO OBTAIN THIS APPLICATION NOTE

The following file(s) are available for download from the Microsoft Software Library:

 ~ XE0872.EXE (size: 17070 bytes) 

For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:

   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 (206) 635-7070. 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

Additional query words: 4.00

Keywords : kbappnote kbfile kbusage
Version : 4.00 4.00a | 4.00
Platform : MACINTOSH WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" 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.

Last reviewed: April 3, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.