Extract filtered data from Microsoft Form

Question

Hello,

I have a Microsoft form, shared with many people every day. The answers are collected and gathered in an excel file (normal).

I would like to extract these data to make separate and filtered excels (and also automatically updated when new answers arrive).

For example:

In my form, there is an answer "A" and an answer "B".

I have a team to analyze the "A" answers and another one to analyze the "B" answers.

But these teams do not have to access each other's responses.

How to provide an A file to team A and a B file to team B that is updated each time a new response is submitted?

We could do it with Google Forms but for technical reasons we have to switch to Microsoft Forms.

Any help/advice would be very useful.

Thanks in advance

Answer 1

Dear Fabien,

Good day! Thank you for posting in Microsoft Community. We are happy to help you.

As I understand you want to create a Form that would autopopulate the answers in Excel worksheet and then sends these answers to other worksheets for different teams based on the answers in Forms.

If you have created a form directly in Forms app then I’m afraid you can’t get the data in Excel automatically.

If you want Excel to auto populate the replies the form has to be created directly in Excel for Web via Insert > Forms > New Form.

Note: Excel file created from this form must be stored in the cloud storage.

After the form is created Excel file would update as new replies are added.

Then you may create other workbook for the teams to analyze the responses and connect these workbooks with Excel with form’s data.

For my test I created two workbooks for each response category (called Answers for A/B) on SharePoint folder and synced them with OneDrive client app. But you may try another types of connection, for reference Power Query Excel connector - Power Query | Microsoft Docs

In workbook for A I selected Data > Get Data > From File > From workbook and chose Excel with forms data.

In Power Query Editor filter the columns so only the responses for team A would be seen and removed the columns with replies for team B.

Select Close & Load.

Repeat these actions for other teams’ workbooks.

Then you can share these workbooks with related teams and data will be auto populated based on the responses in Form but only with the managed columns.

Feel free to post back to if you need further assistance.

Sincerely,

Igor | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***

Answer 2

Hello Igor,

Thanks a lot for your help!!!

This works almost perfectly. But the secondary worksheets do not update automatically. I have to click on refresh data to get the new information.

But that's not a big deal. What bothers me is that I have to open the excel from the local application to be able to refresh. Online it gives me an error message > "This workbook contains Power Query that can't be refreshed in excel for the web."

Is there a workaround?

Thanks again for the help

Answer 3

Hello Fabien,

Thank you for coming back to us and update a scenario.

Unfortunately, we cannot refresh Power Query queries in Excel for the web. We can see that the file contains queries, but we can’t create, review, edit or refresh them.

As another suggestion you may connect workbooks with links. For more detailed information, please refer to this article Create an external reference (link) to a cell range in another workbook (microsoft.com)

Feel free to post back to us if you need further assistance on this process.

Regards,

Igor

Answer 4

Hello Igor,

thanks for the help. But I am testing and I think there is a problem. I will have to give access to the main file (the one that records all the answers) to all the teams.

For example, if someone from Team A needs to update their file and refresh the data, they also need to download the main file locally. Excel (opened locally) will not be able to retrieve data from the excel that stored online

Is there by any chance a way to do something different?

Regards

Answer 5

Hello Fabien,

Thank you for coming back to us.

Per my tests, users would not need to have access to main file. Besides, if users download the main file to their local storage, they would get a copy of the file that will not be updated when new responses are submitted to form.

Below you may see the steps I made in my test:

• Create three Excel files in one SharePoint folder – main Excel file with form, and two files to distribute to teams.
• Sort the data in main file and create an external reference (link) to a cell range in another workbook with the teams’ files.
• Share a team file with another user in the organization (File > Share in Excel app) who does not have access to SharePoint library.

As a result - user is able to see only the shared Excel file, and new responses submitted to form appear in the linked files as well.

May I know if you get another results in your tests?

Regards,

Igor