How to Use Excel PivotTable Column Headers as Row Fields
Question details
The user needs to display original dataset column headers as row fields with totals in a PivotTable.

- 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.
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.
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.
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.
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').
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'.
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.
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.

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. Download and Install: Visit the official WPS website and download the free WPS Office installation package for your operating system.
- 2. Open Your Excel File: Launch WPS Spreadsheet and easily open your existing .xlsx workbooks without losing any formatting or data.
- 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.

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.




