Analyze & Visualize Survey/Feedback Reports with Power Query
Step 1: Prepare Raw Data in Excel
- Structure
your data with the following layout:
Respondent ID | Q1 | Q2 | Q3 | Q4
--------------|------|------|------|------
R1 | Yes | No |
Yes | No
R2 | No | Yes
| No | Yes
...
- Ensure
each row represents an individual respondent and each column after the
first represents a question or feedback point.
Step 2: Convert Data into a Table
- Select
the entire dataset.
- Go to
the Insert tab → Click on Table.
- Ensure
"My table has headers" is checked.
- Give
your table a name (e.g., SurveyData) for easier reference.
Step 3: Load Data into Power Query Editor
- With
the table selected, go to the Data tab → Click From Table/Range.
- This
action opens the Power Query Editor.
Step 4: Unpivot the Questions
- In
Power Query Editor:
- Select
all question columns (e.g., Q1 to Q4) by clicking the first question
column, then Shift+Click the last.
- Go
to the Transform tab.
- Click
Unpivot Columns.
- The
result will convert columns into two: Attribute (question name) and Value
(response).
Step 5: Rename Columns for Clarity (Optional)
- Rename
the columns:
- Attribute
→ Question
- Value
→ Response
- Resulting
table:
python-repl
CopyEdit
Respondent ID | Question | Response
--------------|----------|---------
R1 |
Q1 | Yes
R1 |
Q2 | No
...
Step 6: Close and Load Data
- Go to
the Home tab → Click Close & Load.
- Data
loads into a new sheet or table in Excel.
Step 7: Create a Pivot Table on Loaded Data
- Select
the newly loaded table.
- Go to
the Insert tab → Click Pivot Table.
- Place
the Pivot Table in a new sheet.
- Suggested
pivot layout:
- Rows:
Question
- Columns:
Response (e.g., Yes, No)
- Values:
Respondent ID (change aggregation to Count)
Step 8: Show Data as Row Percentage
- In the
Pivot Table:
- Right-click
any value cell → Show Values As → Select % of Row Total.
This allows you to easily compare the proportion of
responses per question.
Step 9: Apply Conditional Formatting for Visualization
- Select
the value area in the Pivot Table.
- Go to
the Home tab → Click Conditional Formatting → Choose:
- Data
Bars
- Color
Scales
- Icon
Sets
- This
visually highlights differences in feedback or trends across questions.
✅ Outcome
You now have a dynamic, cleanly structured, and visually
rich report showing percentage-based feedback across all questions. It is easy
to update—just refresh the Power Query and Pivot Table if new data is added.
Would you like a downloadable sample Excel file or a visual
flowchart of this process?