Forms questions out of order when viewing results in Excel

[Roger_Jameson]

I created a survey of 88 questions, the first 8 requiring text answers and the remaining 80 using the Likert answer structure (strongly agree, agree, neither agree nor disagree, disagree and strongly disagree).  The survey was grouped into 6 specific sections.

During its compilation, and due to the number of questions included in the survey, a number of iterative changes were made by:

• Adding 4 new questions
• Deleting an existing question
• Changing the sequence in which 4 questions appeared i.e. re-ordering the survey to improve the flow of questions and make it more logical

Responses to the survey were received from 29 organisations which were then downloaded to Excel for analysis.

A report has been generated and distributed to a consultative group of the 29 organisations for feedback

Issues:

1. The question descriptions in the Excel header are displayed correctly for the first 10 questions but thereafter they are numbered but with an occasional question description being correctly displayed in the Excel header row.  This numbering has posed enormous difficulties in ensuring that the analysis is correctly aligned to the relevant question AND also due to point 2 below.
2. One member of the consultative group reported that the results for its organisation did not correlate to the responses it submitted.  The order in which the results appear in Excel is not the order in which the questions appear in the final version of the survey which was distributed to the sample of organisations. On further analysis, and by entering a range of test dummy survey submissions, we have been able to identify that when viewing the results in Excel, the Forms questions are out of order.  With the issue of the numbering in point 1 above, we now do not know which answer relates to which question. This has thrown considerable doubt on the validity of the report outlining the survey results and will incur a significant and cumbersome amount of work to review and correct.

Questions:

1. Are the Forms questions out of order in Excel due to the changes made to the survey i.e. the results are based on the initial version of the survey when it was first set up and NOT the final version?
• From researching the Forms discussion threads, it is noted that Forms does not retain a version history so there is no way of checking this
• When will this be provided?
2. How can we ensure that the results as exported to Excel align to the sequence of questions as set out in the final version of the survey in Forms?
3. Why is Excel not showing the description for every question included in the survey?

[#2]

@Roger_Jameson I suspect the problem is related to the size of the form: there is a hard 100 question limit on a form, but each likert statement counts as a question. So it looks to me that you've breached the limit, not helped by the changes to questions. Particularly for large forms Forms is the most flakey of M365 apps. For the future I recommend you use a flow in Power Automate to save each response into a SharePoint list. Not only is this good practice for audit and backup reasons but if problems occur you are far less likely to get a problem with a SharePoint list than with the spreadsheet behind the form.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#3]

@RobElliott Thanks Rob