Power Query Problems

How to Use Excel PivotTable Column Headers as Row Fields

Adam DavisAdam Davis Oct 22, 2026 873 views

Question details

The user needs to display original dataset column headers as row fields with totals in a PivotTable.

How to Use Excel PivotTable Column Headers as Row Fields
Product
Excel
Device & OS
not provided
Scenario
Creating a PivotTable where the existing source data structure prevents placing categorical column headers into row fields directly.
Observed behavior
The source data format contains categories and status values as distinct columns, which prevents the PivotTable from grouping them properly as row items with calculated subtotals.
Before you start

Ensure your source data is formatted as an Excel Table by selecting your data range and pressing Ctrl+T before importing it into Power Query.

Solution 1Recommended

Unpivot Data Using Power Query

Transform your source data by unpivoting the columns so that the headers become a single attribute column, which can then be easily used as a row field in your PivotTable.

Standard PivotTables treat column headers as distinct fields. To use these headers as row items with subtotals, the data must be reshaped so that the headers are stored as values within a single column. Power Query provides a simple 'Unpivot' feature to accomplish this without complex formulas.

1
Load Data into Power Query

Select any cell within your data table. Navigate to the Data tab on the Excel ribbon and click 'From Table/Range' to open the Power Query Editor.

2
Unpivot the Target Columns

In the Power Query Editor, hold down the Ctrl key and click the headers of the columns you want to transform. Right-click one of the selected headers and choose 'Unpivot Columns' (or select the identifying columns, right-click, and choose 'Unpivot Other Columns').

3
Rename the New Columns

Power Query will generate two new columns typically named 'Attribute' and 'Value'. Double-click these headers to rename them to something meaningful for your dataset, such as 'Category' and 'Status'.

4
Load the Transformed Data to a PivotTable

Click the 'Close & Load' dropdown arrow on the Home tab, then select 'Close & Load To...'. In the Import Data dialog box, choose 'PivotTable Report' and select where you want to place it.

5
Build the PivotTable

In the PivotTable Fields pane, drag your newly renamed attribute field (formerly the column headers) into the 'Rows' area. Drag the status field into 'Rows' or 'Columns' as needed, and place your target measure into the 'Values' area to display the totals.

Unpivot Data Using Power Query
Automatic Updates: By using Power Query, your data transformation is saved as a repeatable process. If you add new data to your source table, simply right-click your PivotTable and select 'Refresh' to update the unpivoted results instantly.
Free Microsoft Office alternative

Experience Hassle-Free Data Management with WPS Office

If complex Power Query transformations and PivotTable limitations in Microsoft Excel are slowing down your workflow, try WPS Office. It is a highly efficient, free alternative that offers seamless Microsoft Office format compatibility, intuitive data tools, and a user-friendly interface for all your spreadsheet tasks.

  1. 1. Download and Install: Visit the official WPS website and download the free WPS Office installation package for your operating system.
  2. 2. Open Your Excel File: Launch WPS Spreadsheet and easily open your existing .xlsx workbooks without losing any formatting or data.
  3. 3. Analyze Your Data: Select your data range and navigate to the Insert tab to instantly generate a PivotTable using a familiar, easy-to-use interface.
Free and lightweight alternative to Microsoft Excel100% compatible with Microsoft Office formats (.xlsx, .csv)Intuitive user interface for creating and managing PivotTablesSeamless data migration with zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just drag column headers into the Row fields area?

Standard PivotTables treat column headers as separate, independent fields. To display them as row items in a single list, the data must be reshaped (unpivoted) so that those distinct headers are grouped as individual values within a single attribute column.

What is Power Query and where can I find it in Excel?

Power Query is a built-in data preparation tool in Excel used to connect, combine, and refine data across various sources. You can access it by navigating to the Data tab on the ribbon and clicking 'From Table/Range' or by using the 'Get Data' dropdown menu.

Can I unpivot data without using Power Query?

Yes, you can manually restructure your data using Copy and 'Paste Special > Transpose', or by writing complex array formulas. However, these methods are either static or computationally heavy, whereas Power Query offers an automated, one-click refresh solution.

How do I refresh the PivotTable after adding new columns to the source data?

If you used the 'Unpivot Other Columns' option in Power Query, new columns added to your source table will automatically be included in the transformation. Simply right-click anywhere inside your PivotTable and select 'Refresh' to pull in the new data.