Function Problems

How to Separate Microsoft Forms Responses into Excel Worksheets

Maira Mehtab Sep 27, 2026 869 views

Question details

The user wants to automatically categorize and display survey responses collected from Microsoft Forms into separate worksheets based on specific column criteria.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Sorting mixed survey data from a single master response sheet into dedicated category sheets, such as placing 'soccer' and 'football' responses onto their own tabs.
Observed behavior: By default, Microsoft Forms stores all responses in a single Excel workbook tab, requiring a formula or function to dynamically route data to separate worksheets.

Before you start

Ensure your Microsoft Forms responses are actively syncing to an Excel workbook and identify the exact column letter that contains the criteria you wish to filter by.

Solution 1Recommended

Use the FILTER Function to Sort Responses Dynamically

Extract data into separate worksheets automatically using the FILTER function, which updates dynamically as new form responses are submitted.

The most efficient way to separate responses without disrupting the original data flow is by using the FILTER array function. This method pulls data from the master response sheet into new tabs based on matching text.

Open your response workbook

Open the Excel workbook containing your synced Microsoft Forms responses. The default sheet is usually named 'Responses'.

Create a new worksheet

Click the '+' icon at the bottom of the window to create a new worksheet, then rename the tab to match your target category (e.g., 'Soccer').

Enter the FILTER formula

Select cell A1 in your new worksheet and type the formula: =FILTER(Responses!A:Z, Responses!C:C="Soccer"). Replace 'Responses' with the actual name of your source sheet, 'C:C' with the column containing the category, and 'Soccer' with your exact target keyword.

Apply and repeat

Press Enter to populate the data. Repeat this process by creating additional worksheets and adjusting the keyword in the formula (e.g., "Football") for each category you need to separate.

Dynamic Updates: Because FILTER is a dynamic array function, any new responses submitted via Microsoft Forms will automatically appear on the correct categorized worksheet in real time.

Advanced Data Filtering

Organize Form Data Efficiently with WPS Spreadsheet

Easily filter, categorize, and analyze your survey data using advanced functions in WPS Spreadsheet. It offers full compatibility with Excel formulas, a familiar interface, and seamless data management.

1. Open the form data: Open your exported form responses file (.xlsx) in WPS Spreadsheet.
2. Add a category tab: Click the '+' icon at the bottom sheet bar to add a new worksheet for your specific category.
3. Apply the filter: In cell A1, type =FILTER(Sheet1!A:Z, Sheet1!C:C="TargetValue") and press Enter to pull the specific data automatically.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports advanced dynamic array formulas like FILTERLightweight, fast, and completely free to useBuilt-in data visualization tools for presenting survey results

microsoft office alternative - wps office

Frequently Asked Questions

Can I separate form responses using Pivot Tables instead?

Yes, you can insert a Pivot Table from your response data, place your target category column into the 'Filters' area, and click on PivotTable Analyze > Options > 'Show Report Filter Pages' to automatically generate separate sheets for every category.

Why is my FILTER function returning a #CALC! error?

The #CALC! error typically occurs if there are no responses matching your specified criteria. You can fix this by adding an 'if_empty' argument to your formula, such as: =FILTER(Responses!A:Z, Responses!C:C="Soccer", "No matching responses").

Will formatting applied to the source sheet carry over to the new worksheets?

No, the FILTER function only extracts the data values, not the cell formatting (like colors or bold text). You will need to manually apply any desired formatting to the columns on your newly created category worksheets.

Excel & Data Problems