Pivot Table Issues

How to Create a Cross-Tab PivotTable with Multiple Row Labels in Excel

Partner Editor Sep 25, 2026 870 views

Question details

The user needs to create a cross-tab report in Excel using multiple specific fields as row labels and sum the numeric values at their intersections.

How to Create a Cross-Tab PivotTable with Multiple Row Labels in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Structuring source data and building a PivotTable report that correctly displays main categories and subcategories in separate row fields.
Observed behavior: The user wants to arrange specific fields (Title, Group, Location) as row labels and sum intersecting numeric measures, which may require transforming wide source data into a flat format.

Before you start

Ensure your source data has clear column headers without any blank rows or columns. If your raw data is already grouped across multiple columns horizontally, you will need to flatten it before inserting a PivotTable.

Solution 1Recommended

Set Up Fields in the PivotTable Rows Area

The most direct method to create a cross-tab is by dragging the required fields sequentially into the Rows and Values areas of the PivotTable task pane.

By placing multiple fields into the Rows area, Excel creates a hierarchy. You can then adjust the report layout to show these fields in distinct columns.

Insert the PivotTable

Select any cell within your source data range, navigate to the Insert tab on the ribbon, and click PivotTable. Choose to place it on a New Worksheet or Existing Worksheet.

Add Row Labels

In the PivotTable Fields pane on the right, drag the 'Title' field into the 'Rows' area. Then drag 'Group' and 'Location' below 'Title' in the same 'Rows' box to create the hierarchy.

Add Values for Intersections

Drag your numeric measure field into the 'Values' area. By default, Excel will sum numeric data, displaying the totals at the intersections of your row labels.

Change to Tabular Layout

To view each row field in a separate column (true cross-tab style), click anywhere inside the PivotTable. Go to the PivotTable Design tab, click 'Report Layout', and select 'Show in Tabular Form'.

Set Up Fields in the PivotTable Rows Area

Layout Tip: To make the cross-tab cleaner, you can also go to 'Report Layout' and select 'Repeat All Item Labels' so that blank spaces under main categories are filled in.

Solution 2

Unpivot Source Data Using Power Query

Use this method if your source data is not in a flat, tabular format and instead has separate columns for categories that need to act as row labels.

Create PivotTables Easily with WPS

Use WPS Spreadsheet for Advanced PivotTable Reporting

WPS Spreadsheet provides robust, easy-to-use PivotTable tools to create cross-tabs, manage multiple row labels, and summarize large datasets efficiently without complex configurations.

1. Open Your Data: Launch WPS Spreadsheet, open your workbook, and select the data range you want to analyze.
2. Insert PivotTable: Navigate to the Insert tab on the top ribbon and click the PivotTable icon.
3. Organize Row Fields: In the side pane, drag your categorical fields (like Title, Group, Location) directly into the 'Row' area.
4. Calculate Totals: Drag your measurable numerical data into the 'Values' area to generate cross-tab calculations automatically.

Fully compatible with Microsoft Excel (.xlsx) formats.Intuitive drag-and-drop interface for structuring PivotTable fields.Fast processing of complex cross-tabs and large datasets.Free to use with a lightweight installation.

microsoft office alternative - wps office

Frequently Asked Questions

Why are all my multiple row labels showing up in a single column?

By default, Excel displays new PivotTables in 'Compact Form', which nests all row fields into one column to save space. To separate them into distinct columns, click the PivotTable, go to the Design tab, click 'Report Layout', and select 'Show in Tabular Form'.

How do I remove automatic subtotals from my row fields?

When you add multiple fields to the Rows area, Excel adds subtotals for the parent categories. To remove them, click anywhere inside the PivotTable, go to the Design tab, click 'Subtotals' in the Layout group, and choose 'Do Not Show Subtotals'.

What does it mean to unpivot data for a PivotTable?

Unpivoting transforms data from a wide format (where attributes like months or locations are separate columns) into a long, flat format (where those attributes are stacked in a single column). PivotTables require this long, flat format to group and summarize data accurately.

Can I change the PivotTable calculation from Sum to Count or Average?

Yes. In the PivotTable Fields pane, click the drop-down arrow next to the field in the 'Values' box, select 'Value Field Settings', and choose your preferred calculation method, such as Count, Average, Min, or Max.

Excel & Data Problems