Pivot Table Issues

How to Create a Cross-Tab in Excel 365 PivotTables

Amos Gikunda Sep 25, 2026 869 views

Question details

The user needs to create a cross-tab (two-way summary table) in Excel 365 PivotTables or troubleshoot why their current cross-tab layout is displaying incorrectly.

How to Create a Cross-Tab in Excel 365 PivotTables

Product: Excel 365
Device & OS: not provided
Scenario: Setting up a two-way summary data layout using Excel PivotTables.
Observed behavior: A cross-tab requires specific fields in the Rows, Columns, and Values areas, but the layout may fail or produce unexpected results due to source data formatting or incorrect field configuration.

Before you start

Ensure your source data is organized in a strict tabular format with unique column headers and no blank rows or merged cells before inserting a PivotTable.

Solution 1Recommended

Configure PivotTable Fields for a Cross-Tab Layout

Set up the two-way summary table by dragging your categorical and numerical data fields into the correct PivotTable areas.

A cross-tab layout requires at least one field to act as the horizontal axis, one for the vertical axis, and a metric to calculate the intersection.

Insert a PivotTable

Select your source data range, go to the 'Insert' tab on the Excel ribbon, and click 'PivotTable'. Choose whether to place it on a new or existing worksheet.

Set the Rows Field

In the PivotTable Fields pane on the right, click and drag your primary category field (e.g., Region or Product) into the 'Rows' area.

Set the Columns Field

Drag your secondary category field (e.g., Months or Status) into the 'Columns' area. This creates the two-way cross-tab structure.

Define the Values

Drag a numeric or countable data field into the 'Values' area. Click the dropdown arrow on the field to access 'Value Field Settings' and ensure the calculation is set correctly (e.g., Sum or Count).

Configure PivotTable Fields for a Cross-Tab Layout

Tip for Accurate Results: If your values are calculating as 'Count' instead of 'Sum', it usually means there is a blank cell or text value within that column of your raw data.

Solution 2

Troubleshoot Issues Using a Dummy Data Sample

If the PivotTable layout remains broken, recreate the setup using a sample workbook to isolate formatting or version issues.

Powerful Spreadsheet Alternative

Create Cross-Tabs Effortlessly in WPS Spreadsheet

WPS Office features a robust Spreadsheet application that fully supports advanced PivotTable creation. You can easily build, modify, and analyze two-way summary cross-tabs using an intuitive drag-and-drop interface.

1. Open Your Data File: Launch WPS Spreadsheet and open your existing Excel data file or start a new table.
2. Insert the PivotTable: Navigate to the 'Insert' tab on the top ribbon and click 'PivotTable'.
3. Select the Data Range: Confirm the selected data range and choose where to generate the new PivotTable, then click 'OK'.
4. Build the Cross-Tab: In the PivotTable fields panel, simply drag your desired fields into the Rows, Columns, and Values boxes to immediately generate your cross-tab layout.

Fully compatible with Microsoft Excel (.xlsx) file formatsIntuitive drag-and-drop PivotTable field configurationLightweight, fast performance for large datasetsFree to use with comprehensive data analysis tools

microsoft office alternative - wps office

Frequently Asked Questions

What exactly is a cross-tab in Excel?

A cross-tab (or cross-tabulation) is a two-way summary table that displays the relationship between two or more variables. In Excel, this is typically built using a PivotTable by placing one data category in the Rows area and another in the Columns area.

Why is my PivotTable Value area showing Count instead of Sum?

Excel defaults to 'Count' if it detects text, blank cells, or non-numeric formatting in the data column you dragged into the Values area. You can fix this by right-clicking a value in the PivotTable, selecting 'Summarize Values By', and choosing 'Sum'.

Can I add multiple fields to the Rows or Columns area?

Yes. You can drag multiple fields into the Rows or Columns areas in the PivotTable Fields pane. This will create grouped sub-categories (a hierarchical layout), allowing for deeper and more specific data analysis.

Why does my cross-tab layout look completely wrong?

This usually happens if your raw source data is not formatted properly. Ensure that your raw data has clear column headers, no merged cells, no blank columns, and no pre-calculated subtotal rows mixed into the dataset.

Excel & Data Problems