Data Import & Export

How to Create Separate Excel Worksheets from Microsoft Forms Answers

Amos Gikunda Sep 30, 2026 868 views

Question details

The user wants to organize Microsoft Forms responses by splitting them into separate Excel worksheets based on specific multiple-choice answers.

How to Create Separate Excel Worksheets from Microsoft Forms Answers

Product: Microsoft Excel
Device & OS: not provided
Scenario: Exporting survey or quiz results from Microsoft Forms and categorizing the data by a specific choice column.
Observed behavior: Responses currently export into a single master Excel table, but the goal is to have isolated worksheets for each category while retaining respondent names and dates.

Before you start

Ensure you have exported your Microsoft Forms responses to an Excel workbook and saved a backup copy of the original data before applying transformations.

Solution 1Recommended

Use Power Query to Filter and Load Data to Separate Worksheets

Power Query provides a robust, dynamic way to filter your main response table and create dedicated worksheets that update automatically when new data is added.

This method is highly recommended if your Microsoft Forms survey is still active and receiving new responses, as refreshing the workbook will automatically update the separated worksheets.

Load data into Power Query

Open the exported Excel file, select any cell inside your data table, and go to the Data tab. Click on Get Data > From Table/Range to open the Power Query Editor.

Filter for a specific category

In the Power Query Editor, locate the column containing your multiple-choice answers (e.g., 'Sports'). Click the drop-down arrow on the column header and uncheck all options except the one you want for the first sheet (e.g., 'Basketball').

Load the filtered data

Go to the Home tab in the Power Query Editor, click Close & Load To..., choose Table, and select New Worksheet. Rename the newly created worksheet to match your category.

Repeat for other categories

To create sheets for the remaining categories, right-click the query you just made in the Queries & Connections pane, select Duplicate, change the filter in the new query to the next choice, and load it to a new worksheet.

Use Power Query to Filter and Load Data to Separate Worksheets

Dynamic Updates: When new responses are submitted to Microsoft Forms, simply open your Excel file, go to the Data tab, and click Refresh All. The new data will automatically route to the correct worksheets.

Solution 2

Use the FILTER Formula to Extract Data Dynamically

You can use the FILTER function (available in modern spreadsheet software) to pull specific categorized responses into new sheets without needing VBA or Power Query.

Easily Organize and Filter Survey Data with WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools, including advanced array formulas and PivotTables, making it effortless to split your Microsoft Forms survey data into multiple sheets. It is a lightweight, efficient tool designed for high productivity.

1. Open your Forms data: Launch WPS Spreadsheet and open the .xlsx file containing your exported Microsoft Forms responses.
2. Insert a PivotTable: Select your entire data range, navigate to the Insert tab, and click on PivotTable to create a new report.
3. Configure the PivotTable fields: In the PivotTable fields pane, drag your category column (e.g., 'Sports') into the Filters area, and drag the Name, Date, and other relevant columns into the Rows area.
4. Generate separate worksheets automatically: Go to the PivotTable Options menu, click on Options, and select Show Report Filter Pages. Choose your category column and click OK. WPS Spreadsheet will instantly generate a separate worksheet for every choice.

Fully compatible with Microsoft Excel (.xlsx) formats and exported Microsoft Forms data.Supports dynamic array formulas like FILTER to instantly separate data by category.Built-in PivotTable features allow for one-click worksheet generation using Report Filter Pages.Lightweight, fast, and completely free to use for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I automate splitting the worksheets without writing VBA code?

Yes, you can use Power Query or the PivotTable 'Show Report Filter Pages' feature to automatically generate separate worksheets based on column categories without writing any code.

Will the separated worksheets update if new responses are submitted?

If you use Power Query or dynamic formulas like FILTER, you can include new responses automatically by refreshing the data queries or letting the formulas recalculate. Manual copy-pasting requires you to repeat the process for new data.

Why does my FILTER formula return a #CALC! error?

The #CALC! error typically occurs if there are no responses in the source table that match your filter criteria. You can avoid this error by adding an 'if_empty' argument to your formula, such as =FILTER(A:C, B:B="Baseball", "No data yet").

Can I split data into separate files instead of just worksheets?

To split data into entirely separate workbooks automatically, you would generally need to use VBA macros or an automation tool like Power Automate, as standard formulas and PivotTables only split data within the same workbook.

Excel & Data Problems