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:
- From the File menu, choose New. Select the Worksheet option and choose OK.
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:
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.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 BoxOnce 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 BoxIn 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.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.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 BoxIn 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 BoxIn 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.- To move to the Final dialog box, choose Next.
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.- 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 Last reviewed: April 3, 1997 |
