Blank Cells, Text, and Error Values Affect Crosstab Table

ID: Q89519

The information in this article applies to:

Microsoft Excel for Windows, version 4.0
Microsoft Excel for the Macintosh, version 4.0

SUMMARY

In Microsoft Excel, the Crosstab ReportWizard may not produce the results you expect if your database contains blank cells, cells containing error values (for example, #N/A or #VALUE!), or inappropriate text values. Inappropriate text values are text or character entries in columns that otherwise contain numeric data, and that you select for row headings, column headings, or value fields.

MORE INFORMATION

When you create a crosstab table from a database that contains blank cells, or cells containing text or error values mixed in with your numeric values, the resulting table may not contain the information you expect. If your database contains blank cells, error values or inappropriate text in every row, the Crosstab ReportWizard may not be able to create a crosstab table at all.

The following information summarizes the effects of blank cells, or cells containing inappropriate text or error values, on tables created with the Crosstab ReportWizard.

Blank Cells in Column Categories, Row Categories or Value Fields

If any field you select for your Row or Column Categories, or value fields contains blank cells, the values in that entire row will be ignored in the crosstab table.

Example

Create a crosstab with the data below, using Region for Row Categories, Name for Column Categories, and Sales and Units for Value Fields.


A1: Region   B1: Name  C1: Sales   D1: Units
A2: West     B2: Bob   C2: 1000    D2: 500
A3: East     B3:       C3: 800     D3: 450
A4:          B4: Sue   C4: 1600    D4: 800
A5: West     B5: Sue   C5: 1200    D5:

In this case, the values in the last three rows will be ignored in the resulting crosstab table. Your crosstab table will only show Bob's sales as the total sales for the West region.

Text and Error Values in Row or Column Categories

Cells containing text or error values in fields that you have selected as Row or Column categories are treated as labels. For example, if you select Region (using the previous example) for Row categories, and you have a cell containing #N/A in the Region column, then the resulting crosstab contains the Row category labels: West, East and #N/A. The same holds true for Column categories.

Text and Error Values in Value Fields

Cells containing inappropriate text or error values in the fields you select for Value Fields are treated like blank cells. If a cell in the value field for a record contains an error value, that entire record (row) is ignored in the crosstab table. Likewise, if a cell in an otherwise numeric Value Field contains text, that entire record is ignored.

One exception is when you have a text value in the first cell (below the field name) of a column that otherwise contains numeric data. The Crosstab ReportWizard uses the first value in the column to determine the type of data and default operation (sum or count) for that column. As a result, a text value in the first cell causes the Crosstab ReportWizard to count the number of entries for that column, rather than summing the values.

Note: In some cases you will intentionally have Value Fields that contain text entries which you want to count in the crosstab table, rather than values you want summed. In these cases, your crosstab will be correct. It's only when you have text entries mixed in with numeric values in a Value Field column that problems occur.

Blank Cells, Text or Error Values in Every Row

If you have one or more blank cells or cells containing inappropriate text or error values in every row of your database within the fields you have selected for your crosstab, then Microsoft Excel won't be able to create the crosstab table and you would receive the alert:

The crosstab can't be created

-OR-

Crosstab cannot be created

This is because no data would be available to create the crosstab table with. That is, since there is a blank cell, inappropriate text or error value in every row within the fields selected for Row and Column Categories, and Value Fields, all the records (rows) are ignored, leaving nothing to create the crosstab with.

Note: If cell C2 contained a text value, your crosstab table would contain a column titled "Count of Sales" with a value of 1.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 346-356

Additional query words: crosstab fail


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 26, 1999