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.