Microsoft KB Archive/107722

= 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.                                                       |
 * 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:

 From the File menu, choose New. Select the Worksheet option and choose OK.</li>  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 </li></ol>

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:

<ol>  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. </li>  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 </li>  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 </li>  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. </li>  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. </li>  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 </li>  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 </li>  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. </li> To move to the Final dialog box, choose Next.</li>  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. </li> Choose the Create It button.</li></ol>

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