How to Use Excel FILTER Function with Microsoft Forms Logs
Question details
The user wants to organize Microsoft Forms procedure logs dynamically in Excel by keeping a central aggregate view for a program director while distributing individual records to separate worksheets for each resident.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Sorting and filtering a continuously updating list of form responses into separate, individual views based on specific names or criteria.
- Observed behavior
- Form responses are collected in one central location, but they need to be automatically filtered and displayed on separate, individual worksheets as new submissions arrive.
Ensure your Microsoft Forms responses are successfully synced to your main Excel workbook and that you identify the exact column header containing the names you want to filter by.
Use the FILTER Function with an Excel Table
Convert your raw form data into a dynamic Excel table and apply the FILTER function to distribute data automatically to individual worksheets.
By converting your aggregate data into a structured Excel table, any new submissions via Microsoft Forms will automatically expand the table's range. The FILTER function on the individual tabs will dynamically pick up these new entries without requiring formula adjustments.
Navigate to the worksheet containing your raw Microsoft Forms responses. Select the entire data range, go to the 'Insert' tab, and click 'Table'. Name this table descriptively, such as 'ProcedureLog'.
Create a new worksheet for each resident. In a specific cell (for example, B1), type the exact name of the resident as it appears in the Microsoft Forms responses.
On the resident's worksheet, select the cell where you want the filtered data to begin. Enter the formula: =FILTER(ProcedureLog, ProcedureLog[Resident]=B1, "No procedures found"). Replace '[Resident]' with the actual column header containing the names.
If residents should not be able to view each other's data, protect the worksheets using the 'Review' > 'Protect Sheet' option, or place the FILTER functions in entirely separate workbooks that reference the main aggregate file.

Easily Filter Data and Manage Forms in WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like FILTER, allowing you to organize synced form responses effortlessly. Handle large datasets and create tailored reports with high compatibility.
- 1. Import Your Data: Open your synced form response workbook in WPS Spreadsheet.
- 2. Convert to Table: Highlight your data range and press Ctrl+T to convert it into a dynamic table for easy referencing.
- 3. Apply Dynamic Array Formulas: Create individual sheets and enter the FILTER formula referencing the main table to separate data automatically.

Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
The #CALC! error usually appears when the FILTER function finds no records matching your criteria. You can avoid this error by adding an 'if_empty' argument to your formula, such as: =FILTER(Data, Criteria, "No records found").
Can I filter Microsoft Forms data based on multiple criteria?
Yes, you can use an asterisk (*) for 'AND' logic or a plus sign (+) for 'OR' logic. For example: =FILTER(ProcedureLog, (ProcedureLog[Resident]=B1) * (ProcedureLog[Status]="Completed")).
How do I prevent residents from viewing the main aggregate sheet?
To enforce strict privacy, you should use separate Excel workbooks rather than separate worksheets within the same file. Keep the aggregate file restricted, and build individual files for each resident that use external references to pull their specific data.




