Duplicate questions in microsoft forms answers downloaded in excel

[snehlatagiri]

I have a rate colour form where user has to rate colours based on their liking and their mother's liking. 

This is my form 

But the problem is when i download the response here in excel the questions are not coming. 

Here it is coming as Red, Red2 if i have 100 of these questions for each family member and friends then this response won't give me any idea of what is the response for whom. 

Is there any way to identify that. Or is it like for each question I will have to add whose response we are asking for. 

[#2]

Hi @snehlatagiri,

solution to this problem is to use the Respondent ID column.
The Respondent ID column is a unique identifier for each respondent in Microsoft Forms. You can use this column to filter the responses in Excel and identify which responses belong to which respondent.

To do this, follow these steps:

1. Open the Excel workbook containing the Microsoft Forms responses.
2. Select the Respondent ID column.
3. Click on the Filter button (the funnel icon).
4. In the Filter dialog box, enter the Respondent ID for the respondent whose responses you want to view. Then, click on the OK button.

Excel will filter the data to only show the responses that belong to the selected respondent.

For example, let's say you have the following responses in your Excel workbook:

Respondent ID Question 1 Question 2

1	Blue	Red
1	Green	Purple
2	Yellow	Orange
2	Black	White

If you want to view the responses for John Smith (Respondent ID 1), you would select the Respondent ID column and then click on the Filter button. In the Filter dialog box, you would enter "1" and then click on the OK button.

Excel would then filter the data to only show the following responses:

Respondent ID Question 1 Question 2

1	Blue	Red
1	Green	Purple

You can use this same method to identify the responses for any respondent or group of respondents.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#3]

@LeonPavesic sorry but this is not what I am looking for. Like i said i have duplicate questions which are part of different likerts. So the problem is when I download the response the heading of the likert is not there. its just the questions are there with numeric value appended. So if I have asked to rate the color "Red" twice in my form response I get back is Red, Red1, Red2 and so on. So I want to see the likert heading as well. Is it possible to do that?

[#4]

I guess impossible.
It is possible to convert the downloaded .xlsx to a new report with heading.

[#5]

Hi @snehlatagiri,

thanks for your response, I understand your problem now.

Unfortunately, there is no way to automatically include the question headings in the Excel export of Microsoft Forms responses, so you can do it manually, but it can be time-consuming if you have a lot of questions in your form.

To do this, simply open the Excel workbook and type the question headings into the first row of the spreadsheet. Then, you can copy and paste the responses below the question headings.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#6]

@LeonPavesic Sorry but this is not something that will work for me. I want it to be automated only. 

Is there some kind of an application which can extract this data for me. In this screenshot the data is visible for the questions but in excel it is not. So is there any tool like power automate or something. 

[#7]

@snehlatagiri,

like i wrote before there is no built-in feature in Microsoft Forms or Excel that can do this automatically.

However, there is a workaround that you can try using Power Automate.

Here are the steps to create the Power Automate flow:

1. Go to the Power Automate website and sign in with your Microsoft account.
2. Click on Create and then select Flow.
3. Select the Automated from scratch option and then click on Create.
4. In the Trigger section, select When a new response is submitted and then select the Microsoft Forms form that you want to use.
5. In the Actions section, add the following actions:
   • Get response details
   • Get question details
   • Filter array
   • Create table
   • Add row to table
6. In the Get question details action, select the Question column from the Dynamic content list.
7. In the Filter array action, select the Question column from the Dynamic content list. In the Filter by section, select equals and then select the Question Text column from the Dynamic content list.
8. In the Create table action, select the Excel connector and then select the Create table action.
9. In the Add row to table action, select the Excel connector and then select the Add row to table action.

Configure the Create table and Add row to table actions to create a new table in Excel with the following columns:

• Question Heading
• Response

Add the following dynamic content to the Question Heading column:

item()['QuestionText']

Add the following dynamic content to the Response column:

item()['Value']

Save and run the Power Automate flow.

When a new response is submitted to the Microsoft Forms form, the Power Automate flow should be triggered and it will create a new Excel file with the question headings and responses.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#8]

@LeonPavesic I can't see "Get question details" in form actions

[#9]

@snehlatagiri 

that's one of the problems with responses that are at least partially AI-generated which I suspect that one was : there is no "get question details" action in Power Automate. The way you can achieve what you want quite easily IS with a flow in Power Automate and it's not complicated, but depending on how many likerts you've got it might take a while to build.

One thing I have noticed is that if you duplicate a likert question and the statements in it, sometimes a flow can select the wrong answer even though you added it correctly in the flow. So my advice would be to go through and add each likert question and the statements and answers individually. I know that's a pain but I think there might be a bug somewhere with the duplication of likert questions.

Anyway the steps are as follows:

This is my the form which I'm about to submit and it has low medium and high for each of the 4  colours. 

Next, create a spreadsheet with a column for the responder's email then a column for each of the answers in the likerts. You'll see I've called them You - Green, Mother - Green, Father - Green etc. Make sure you format it as a table.

Next create an instant cloud flow from a blank flow. Do not use the Forms template in Power Automate as it has an error which Microsoft have never corrected.

Every flow has 1 trigger followed by actions. The trigger is the Forms when a new response is submitted" and you select your form from the dropdown.

The first action is "get response details". You again select your form then click in the Response Id field. The dynamic content box will appear at the bottom right of your screen and in this case the only thing you can select is Response Id.

We just need to add 1 more action which is the Excel add a row into a table. For the File field click the folder icon and navigate your way to where you've saved the spreadsheet and select it and then the table.

 After a few seconds the columns from the spreadsheet will be displayed and you click in each field and select the appropriate item from the dynamic content box. It's usually helpful to use the search box and enter Green or Mother or Father and then you select the question that's correct for the column in the spreadsheet. It's this bit which could be time-consuming if you've got lots of duplicate likerts as you need to be careful you are selecting the correct one. So I entered Mother then Father in the search box.

When a response is submitted the flow will run and will populate the table, as shown below.

I hope that helps. Do come back with any questions.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#10]

In this screenshot the data is visible for the questions but in excel it is not

draw chart in Excel?
What kind of automation do you need?

[#11]

"What kind of automation do you need? @peiyezhu strange post,I thought I'd given the method of automating it in my previous post.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)