How to Unpivot Multiple-Answer Survey Fields for Excel PivotTables
Question details
The user needs to transform multiple-choice survey data from separate columns into a normalized format suitable for PivotTable counting without manually rearranging every response.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Preparing raw survey data where each multiple-choice option is stored in a separate column (containing a 1 or blank) so it can be summarized using a PivotTable.
- Observed behavior
- The survey data is spread across multiple columns, treating each answer as a separate field, which prevents accurate aggregation and counting in a standard PivotTable.
Ensure your survey data table contains a unique identifier column for each respondent (such as a Respondent ID, Email, or Name) before importing the data into Power Query.
Use Power Query to Unpivot Survey Columns
Use the Unpivot Columns feature in Power Query to convert wide survey data into a tall dataset, normalizing the data for PivotTable analysis.
Power Query provides a robust and automated way to reshape survey data. By unpivoting the answer columns, you convert the wide format into a normalized row-based structure. This combines all selected options into a single measurable attribute field, which is the ideal structure for PivotTable summaries.
Select your survey data table in Excel, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Locate the Respondent ID or Person column. Right-click the column header and ensure you keep this as your anchor column for the transformation.
Select all the columns containing the multiple-choice answers. Right-click the header of one of the selected columns and choose 'Unpivot Columns' (or select the Respondent column and choose 'Unpivot Other Columns').
If you need full text descriptions instead of column headers, click 'Merge Queries' on the Home tab. Join your unpivoted attribute values with a separate lookup query that contains the detailed option descriptions, then remove the original attribute column.
Click the 'Close & Load' dropdown arrow, select 'Close & Load To...', choose 'PivotTable Report' or add it to the Data Model, and click OK to start building your PivotTable.
Analyze Survey Data Easily with WPS Office
If you are working with complex survey data or Excel PivotTables, WPS Office is an excellent, free alternative to Microsoft Office. It seamlessly handles complex datasets, supports standard spreadsheet data tools, and offers a lightweight interface for all your data analysis needs without expensive subscriptions.
Frequently Asked Questions
Why can't I just create a PivotTable from the original wide survey columns?
When each survey answer is in its own column, a PivotTable treats them as completely separate fields. This prevents you from dropping them into a single row or column label to get a consolidated total count. Unpivoting merges them into a single measurable field.
What happens to the blank cells when I unpivot the columns in Power Query?
By default, Power Query ignores null or blank values when unpivoting. It will only create rows for the columns that actually contained a value (such as a '1'), which perfectly filters out the survey options that the user did not select.
Can I refresh the Power Query transformation if new survey responses are added?
Yes. Once you have configured the Unpivot steps in the Power Query Editor, you only need to right-click your resulting PivotTable or data table in Excel and select 'Refresh'. Power Query will automatically apply all transformation steps to the newly added rows.
How do I fix the merge error when joining the option descriptions in Power Query?
Merge errors usually occur if the data types of the matching columns are different (e.g., one is formatted as Text and the other as a Whole Number) or if there are invisible leading/trailing spaces. Ensure both matching columns are explicitly set to the 'Text' data type and apply the 'Trim' transformation to both before attempting the merge.
