Pivot Table Issues

How to Create a Pivot Table with Static Rating Row Labels

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to generate a Pivot Table that summarizes a dataset containing multiple rating columns by displaying the ratings as static row labels, column headings across the top, and counts of job titles.

Product: Spreadsheet
Device & OS: not provided
Scenario: Restructuring survey or assessment data that currently has ratings spread across multiple columns, in order to perform an accurate Pivot Table count analysis by job title.
Observed behavior: Because the ratings are spread horizontally across four columns, standard Pivot Table creation fails to group them as a single static row label axis.

Before you start

Ensure your dataset is formatted as a continuous table without empty rows or blank column headers before beginning the data transformation process.

Solution 1Recommended

Use Power Query to Unpivot Rating Columns

To use multiple rating columns as a single set of row labels, you must first 'flatten' or 'unpivot' the data using Power Query before inserting the Pivot Table.

When data is recorded with categorical values (like Limited, Moderate, Considerable, Extreme) in separate columns, a Pivot Table cannot intuitively group them into a single column of row labels. By unpivoting the non-essential columns, you consolidate the ratings into a single column, which easily translates into static row labels.

Load data into Power Query

Select any cell in your dataset, go to the 'Data' tab, and choose 'From Table/Range' to open the Power Query Editor.

Select columns to keep static

In the Power Query Editor, hold down the 'Ctrl' key and click on the column headers of the first two columns (e.g., Job Title and any other identifier) that you do not want to flatten.

Unpivot other columns

Right-click one of the selected column headers and choose 'Unpivot Other Columns' from the context menu. This will collapse your four rating columns into 'Attribute' and 'Value' columns.

Load the transformed data

Click 'Close & Load' on the Home tab to output the newly structured data into a new worksheet.

Create the Pivot Table

Select the new data, go to 'Insert' > 'PivotTable'. In the PivotTable Fields pane, drag the unpivoted 'Value' (ratings) field to the 'Rows' area, drag your other category to 'Columns', and drag the 'Job Title' field to the 'Values' area to display the count.

Automatic Updates: If you add new rows to your original dataset, you can simply click 'Refresh All' on the Data tab. The Power Query connection will automatically unpivot the new data and update your Pivot Table.

Easily Summarize Restructured Data with WPS Spreadsheet

Once your data is in the correct tabular format, WPS Spreadsheet offers a powerful and highly intuitive Pivot Table feature to help you categorize ratings and count job titles in seconds.

1. Insert a Pivot Table: Open your formatted dataset in WPS Spreadsheet, go to the 'Insert' tab, and click the 'PivotTable' icon.
2. Configure Row Labels: In the PivotTable fields pane on the right side of the screen, click and drag your consolidated Ratings column into the 'Rows' box.
3. Count the Job Titles: Drag the 'Job Title' column into the 'Values' box. WPS Spreadsheet will automatically default to 'Count' since the data consists of text.
4. Format Column Headings: Drag your remaining category data field into the 'Columns' box to complete the cross-tabulated view.

100% format compatibility with Microsoft Excel (.xlsx) filesFamiliar drag-and-drop Pivot Table field list interfaceLightweight application that handles large datasets quickly and without laggingAdvanced value field settings to easily switch between counts, sums, and averages

microsoft office alternative - wps office

Frequently Asked Questions

Why are my rating columns appearing as separate fields instead of row labels?

This happens because the raw data is formatted in a 'wide' structure (cross-tab) rather than a 'long' structure (tabular). Pivot Tables require all items meant to share a single row label axis to be contained within a single column. You must unpivot the data first.

Can I count text values like job titles in a Pivot Table?

Yes. When you drag a column containing text values into the 'Values' area of the Pivot Table field list, the software automatically applies the 'Count' function to summarize how many times each text entry occurs.

How do I change a Pivot Table calculation from Sum to Count?

Right-click on any calculated number within the Pivot Table, select 'Value Field Settings' (or 'Summarize Values By'), and choose 'Count' from the list of available functions.

Is WPS Spreadsheet compatible with Pivot Tables created in Microsoft Excel?

Yes, WPS Spreadsheet is fully compatible with Excel (.xlsx) files. Pivot Tables created in Excel will display and function normally in WPS Spreadsheet, allowing you to manipulate fields and refresh data seamlessly.

Excel & Data Problems