How to Create a Grouped Text Report from Microsoft Forms Data in Excel with Power Query
Question details
The user needs to transform Microsoft Forms feedback stored in Excel into a continuous text report grouped by department, excluding rows where the Number field equals zero, with a heading that updates from an Excel field.
- Product
- Microsoft Excel with Power Query (Microsoft Forms data)
- Device & OS
- not provided
- Scenario
- A user receives Microsoft Forms responses in an Excel workbook and needs a repeatable, refreshable report instead of manually copying and formatting entries.
- Observed behavior
- The desired report is grouped by department, contains formatted employee details, omits Number = 0 rows, and shows a dynamic heading from an Excel field.
Make sure the Microsoft Forms responses are connected to an Excel workbook and the table has clear headers for name, department, number, and feedback columns. Before opening Power Query, identify the feedback fields you want to include in the grouped text report.
Use Power Query to Filter, Group, and Format Microsoft Forms Responses
Use this solution when you have Microsoft Forms data in Excel and need a refreshable text report grouped by department, with zero-number rows removed and a dynamic heading.
Power Query can perform the entire cleanup and grouping in a single query. The query reads the Microsoft Forms table, filters out unwanted rows, transforms the feedback columns, groups by department, and combines employee details into a formatted text block.
The key is to turn the response range into a query, apply the filter and group transformations in order, and output the result to a worksheet.
In the Excel workbook linked to Microsoft Forms, click any cell inside the response table, go to the Data tab, and select From Table/Range. In the Create Table dialog, confirm that the My table has headers checkbox is selected and click OK.
In the Power Query Editor, select the Number column, click the filter arrow at the top of the column, clear the 0 checkbox, and choose OK. This removes all records with Number equal to zero before grouping.
Select the feedback columns you want to include. Use Add Column > Custom Column or Transform > Merge Columns to create a single formatted text value, or use Add Column > Extract Text to combine multiple feedback fields with separators.
Click the Department column, use the sort ascending button, then go to Home > Group By. Set the Group by field to Department and choose an aggregation such as All Rows or Sum/Count depending on the text layout you need.
After grouping, use the Group By dialog or Add Column > Custom Column to concatenate employee name, department, and feedback fields into one text string. Use Text.Combine or combine operators with line breaks or delimiters to create the continuous text report.
Create a separate Excel cell or named range for the report period. In Power Query, add it as a parameter or query, then use it in a custom column or formula so the report heading updates when the Excel field changes.
Click Home > Close & Load in the Power Query Editor to place the grouped text report in your workbook. After new Microsoft Forms responses arrive, click Data > Refresh All to rerun the query and update the report.
WPS Office as a Free Alternative for Excel Report Workflows
This task relies on Microsoft Excel Power Query and Microsoft Forms, which are Microsoft Office-specific features. If you need a free, lightweight office suite for opening, editing, and creating grouped reports from Excel files, WPS Office is a practical alternative with a familiar spreadsheet interface.
Frequently Asked Questions
How do I connect Microsoft Forms responses to the Excel workbook before using Power Query?
Open the Excel workbook created from Microsoft Forms. The responses are normally stored in a table. In Excel, select any cell in that table, go to the Data tab, and use From Table/Range to open Power Query.
Can I filter out records where Number equals zero in Power Query?
Yes. In the Power Query Editor, click the filter arrow on the Number column, clear the 0 checkbox, and click OK. The filter stays in the query and applies every time you refresh.
How do I make the report heading update from an Excel field?
Store the heading value such as the period in an Excel cell or named range. Load that cell as a separate query or parameter in Power Query, then reference it in a custom column or formula so the heading changes when the Excel field changes.
How do I group rows by department and combine employee details into one text block?
Use Power Query's Home > Group By on the Department column. After grouping, use Add Column > Custom Column or Text.Combine to merge employee name and feedback fields into a formatted text string with delimiters or line breaks.
Can WPS Office create the same refreshable grouped text report from Microsoft Forms data?
WPS Office can open and edit the exported Excel workbook and use filters, sorting, formulas, and pivot tables to build a similar grouped report, but it does not provide Power Query or live Microsoft Forms integration. For Microsoft's Power Query workflow, use Excel; for a free editing and reporting alternative, WPS Office works well with .xlsx files.
