How to Create Frequency Percentage PivotTables for Survey Data
Question details
The user needs to convert a dataset with multiple Likert-scale question columns into a frequency percentage PivotTable for survey analysis.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing survey results where each participant occupies one row and multiple questions span across columns, making it difficult to analyze frequencies directly in a standard PivotTable.
- Observed behavior
- The survey data must be unpivoted to create a structured question-and-response format suitable for PivotTable frequency and percentage calculations.
Ensure your survey data is organized in a clear tabular format without merged cells, and that demographic columns (like Participant ID, Gender, and City) are clearly separated from the survey question columns.
Unpivot Survey Data Using Power Query and Create a PivotTable
By unpivoting the question columns in Power Query, you can transform the data into a flat structure that works perfectly for PivotTable frequency analysis.
Survey data typically places each question in its own column, which PivotTables cannot easily group by frequency. Power Query solves this by converting the multiple question columns into just two columns: one for the question itself and one for the response.
Select your survey data range, navigate to the Data tab on the ribbon, and choose 'From Table/Range' to open the Power Query Editor.
Select the demographic columns (e.g., Participant, Gender, City) by holding down the Ctrl key. Right-click any of the selected headers and choose 'Unpivot Other Columns'. This transforms all the question columns into 'Attribute' (Questions) and 'Value' (Responses) columns.
Click 'Close & Load' drop-down on the Home tab, select 'Close & Load To...', and choose 'PivotTable Report' to output the transformed data directly into a new worksheet.
In the PivotTable Fields pane, drag the 'Attribute' field to the Rows area and the 'Value' field to the Columns area. Drag the 'Participant' field to the Values area to count the responses.
Click the drop-down arrow on the 'Count of Participant' field in the Values area, select 'Value Field Settings', go to the 'Show Values As' tab, and select '% of Row Total' to display frequency percentages instead of raw counts.
Go to the PivotTable Analyze tab, click 'Insert Slicer', and select demographic fields like Gender and City. This allows you to filter your frequency percentages by specific participant groups dynamically.
Analyze Survey Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides powerful PivotTable functionalities and data transformation tools to help you process large-scale survey datasets and generate insightful frequency reports with ease.
- 1. Open your survey data: Launch WPS Spreadsheet and open your structured survey dataset file.
- 2. Insert a PivotTable: Select your unpivoted data range, navigate to the Insert tab, and click on 'PivotTable'.
- 3. Configure the fields: Drag your questions to the Rows area and responses to the Columns area. Add participant IDs to the Values area.
- 4. Display as percentages: Right-click the calculated values in the PivotTable, choose 'Show Values As', and select '% of Row Total' for clear frequency analysis.
Frequently Asked Questions
Why do I need to unpivot survey data before using a PivotTable?
Survey data usually has one row per participant and multiple columns for questions. Unpivoting condenses all questions into a single 'Attribute' column and answers into a 'Value' column. This flat data structure is required for a PivotTable to properly group and count responses across all questions at once.
How do I handle missing or blank survey responses in the PivotTable?
While in the Power Query Editor, you can click the drop-down arrow on the 'Value' column header and uncheck 'null' or blank values. This filters out missing data before it loads into your PivotTable, ensuring your frequency percentages are calculated based only on actual responses.
Can I apply conditional formatting to the frequency percentages?
Yes. Once your PivotTable is generated and displaying percentages, you can select the percentage value cells, navigate to the Home tab, and apply Conditional Formatting (such as Color Scales or Data Bars) to visually highlight the most common survey responses at a glance.
