How to Transform Excel Survey Data from Columns to Rows Using Power Query
Question details
The user needs to normalize a wide survey dataset by converting individual question columns into dedicated rows corresponding to each respondent ID.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Restructuring and organizing large survey datasets where each question occupies a separate column, aiming to avoid error-prone manual copy-pasting.
- Observed behavior
- The data is successfully normalized into a clean, long-format structure containing distinct ID, Question, and Response columns.
Ensure your survey data has clear headers, a unique respondent ID column, and no completely blank rows or columns that might break the data range selection.
Use Power Query to Unpivot Survey Columns
This is the most efficient method to restructure wide survey data into a long, row-based format automatically.
Power Query features an 'Unpivot Columns' tool specifically designed to handle this exact scenario. It translates attribute columns into paired rows seamlessly.
Select your entire survey data range, navigate to the Insert tab, and click Table (or press Ctrl+T). Ensure 'My table has headers' is checked.
With any cell in your new table selected, go to the Data tab on the ribbon and click 'From Table/Range' in the Get & Transform Data group.
In the Power Query Editor, hold the Ctrl or Shift key to select all the survey question columns (leave the Respondent ID column unselected). Go to the Transform tab and click 'Unpivot Columns'.
The unpivot action generates two new columns named 'Attribute' and 'Value'. Double-click these headers to rename them to 'Question' and 'Response'. Ensure your columns are arranged as ID, Question, and Response.
Go to the Home tab and click 'Close & Load'. Excel will generate a new worksheet containing your transformed, easy-to-analyze survey data.
Fill Blank Cells Before Unpivoting
If your survey data contains missing responses, filling the blank cells before running Power Query prevents data alignment errors.
Analyze and Organize Spreadsheets with WPS Office
If you are working with complex data and need a powerful, cost-effective tool, WPS Office provides a highly capable alternative to Microsoft Excel. It handles large datasets, pivot tables, and complex formulas effortlessly while maintaining full format compatibility.
- 1. Download and install: Get WPS Office from the official website and complete the quick installation.
- 2. Open your survey data: Launch WPS Spreadsheet and open your existing .xlsx survey files directly with no conversion required.
- 3. Analyze your data: Utilize built-in data processing features like Pivot Tables to summarize and manage your records effortlessly.
Frequently Asked Questions
What does it mean to unpivot data in Excel?
Unpivoting is the process of transforming wide data (where attributes are spread across multiple columns) into long data (where attributes are consolidated into a single column with corresponding paired values). This format is often required for creating Pivot Tables and running database queries.
Why use Power Query instead of manual copy-pasting?
Power Query automates the data transformation process, ensuring consistency and eliminating the risk of human error associated with manual copying. Furthermore, it creates a repeatable process—if source data is updated, you can simply click 'Refresh' to apply the transformations.
Can I reverse an unpivot action in Power Query?
Yes. If you make a mistake, you can simply click the 'X' next to the 'Unpivoted Columns' step in the 'Applied Steps' pane on the right side of the Power Query Editor. If you need to physically pivot long data back into wide data, you can use the 'Pivot Column' feature on the Transform tab.
Will unpivoting delete my original data?
No, Power Query does not alter your original source data. It extracts the data, applies your transformation steps in the background, and loads the finished result into a brand new worksheet.
