Formatting a spreadsheet to receive Form responses

Create a separate Excel workbook populated by Power Automate, then use one FILTER formula on each skill tab so respondents who select multiple skills automatically appear on every applicable tab.

1. Prepare the destination workbook

Store the workbook in OneDrive for Business or SharePoint Online.

1. Create a worksheet named All Responses.
2. Add headings such as:
   • Name
   • Contact
   • Open Response
   • Skills
3. Select the headings and an empty row, then choose Insert > Table.
4. Give the table a recognizable name, such as Responses.
5. Create separate worksheets named House Cleaning, Cooking, and so on.

Use a separate workbook rather than adding tables or formulas to the Forms-connected response workbook. Microsoft warns that manually adding a table or formula to that workbook can cause Forms synchronization errors.^1^

2. Send each response to the new workbook

In Power Automate:

1. Select Create > Automated cloud flow.
2. Choose the Microsoft Forms trigger When a new response is submitted.
3. Select your form under Form Id.
4. Add Microsoft Forms > Get response details.
5. Select the same form and use Response Id from the trigger.
6. Add Excel Online (Business) > Add a row into a table.
7. Select the workbook, the All Responses worksheet’s Responses table, and map each Forms answer to its corresponding Excel column.
8. Save the flow and submit a test response.

This is Microsoft’s documented pattern for writing new Forms responses into an Excel table.^2^

3. Filter each skill into its worksheet

On the House Cleaning worksheet, enter the column headings in row 1. In A2, enter:

=FILTER(
    Responses,
    ISNUMBER(SEARCH("house cleaning",Responses[Skills])),
    "No matching responses"
)

On the Cooking worksheet, use:

=FILTER(
    Responses,
    ISNUMBER(SEARCH("cooking",Responses[Skills])),
    "No matching responses"
)

Repeat for each skill. SEARCH checks whether the selected-skills cell contains the named skill, while FILTER returns the entire matching row. The results “spill” into the surrounding cells and automatically resize as rows are added to the source table.^3^ SEARCH is case-insensitive.^4^

Keep the area below and to the right of each formula empty; otherwise, Excel can’t spill the filtered results.

Important matching detail

A person selecting both House Cleaning and Cooking will appear on both tabs, which matches the requested behavior.

If one skill name is contained inside another—for example, Cleaning and House Cleaning—the shorter search could match both. Use distinct checkbox wording, or search for the exact text format that appears in the Forms response after submitting a test entry.

Alternative: route rows directly with Power Automate

If each tab must contain independent stored rows rather than formula-generated views:

1. Format a separate table on every skill worksheet.
2. After Get response details, add one Power Automate Condition per skill.
3. Test whether the Skills response contains that skill.
4. In each condition’s If yes branch, add Excel Online (Business) > Add a row into a table targeting that skill’s table.

Do not use mutually exclusive “else-if” branches: separate conditions allow a respondent with several skills to be inserted into several tables. This approach requires more flow maintenance whenever skills or worksheet names change; the single master table plus FILTER tabs is simpler.

References

1. How to ensure your form and workbook are in sync | Microsoft Support
2. Common ways to use a form in a flow
3. FILTER function | Microsoft Support
4. Check if a cell contains text (case-insensitive) in Excel