Excel and Microsoft Sync

Question

I have made a microsoft form and have an excel online form that is synced with it. I had to add/remove some questions from the original & rearrange them as well from the Linkert. In the Linkert, there was no option to reorder the questions, thus I had to delete/rewrite the questions while adding the new ones as well in order to get the correct order I was told to have (example, Original Linkert had questions: A B C (in this order), but for the new one, I had to add Questions D & E, and my team wants me to have Question D to be the 2nd question within the Linkert. Since theres no option to rearrange the Linkert already, I had to delete B and C (so I can make sure D is the 2nd question), add D, rewrite B and C again, and finally add E ,so the final linkert can look like: A D B C E)

The excel sheet continues to collect responses but it turned into a mess and at this point im not sure which questions are which cause the excel sheet keeps a record of all the original questions from the original Linkert. Again, using the previous example, in my excel doc, I have "A B C " for columns but I think because I rewrote the same questions in Linkert, I have "A B C D E B1 C1". All the options to answer Linkerts are "1" "2" "3" "4" (multiple choice) so its kinda tidious to see which question is which since answers are limited with options. I seen that some columns have blank cells after a form is submitted so I am guessing those are the questions that are no longer in the form. I wanted to see what the best way would to see which question is which instead of doing it manually.

I also want to make sure that the column name matches with the title of the question in the form cause excel doesnt show all the questions matching (and if they are, they have a number in front of it). I need for the questions to match without numbers (exactly how it is presented in the form) since I need to reference those columns in a separate table I want to create (it wont look good to have Question A1 instead of A since people are going to be confused for why the 1 is there)

You can also see the first question in the form is "Basic Process Control " but in excel its "Quality Control and Foreign Body Detection (Vision System Checkweigher and Scales, X-Ray, Metal Detector, Sieves and Magnetic Traps)". The "Basic Process Control" question does not pop up until 6 columns after "Quality Control and Foreign....", meaning that the order of questions is not correct.

In summary, original Linkert has been changed throughout to meet the requirements of "question order" while also adding new questions. Linkert does not have option to reorder questions so I had to delete some, add the new question (to satisfy the order I was told to have) and then rewrite the ones I deleted + any additional questions. Excel keeps a record of all columns even after deleted and also created duplicates (my guess for the duplicates is because I had to rewrite the same question) It is messy right now since I have a decent amount of questions. To give perspective, the columns for questions exist across columns I1:EL1. I want to see the best way to know which question is which, which question is collecting data, and how to figure out fast as possible since I have 100+ questions in the form.

Answer 1

Hi Sruvs,

Greetings. Thank you for posting the thread to our community. I carefully review your description, it seems you are trying to modify the Likert which export from the Forms, and when the Likert tries to collect responses from the Form, it leads to some mistakes.

I understand your concern, the root cause of this issue is that the syncing process between Forms and Excel is a one-way****synchronization**. It's not a two-way synchronization.**It’s only available to sync the content of Forms to the Excel worksheet, it will not work in the reverse way. In that case, if you modify the original Likert in Excel and then collect new responses from Forms. The content will lead to a conflict which causes the mistaken content, so the best way to modify the questions and answers of your form is to modify them in the Microsoft Forms instead of modifying the synced worksheet in Excel. The workaround I suggest you can try is to do a copy of the exported Excel Likert as a back-up and you can customize in your own way on this back-up file so it will not conflict to the new responses, by the next time you want to modify the new collected responses, you can try to use Power Automate to flow your old worksheet to the new one. If you want to try this workaround, since we mainly focus on the on general and built-in feature queries in Excel, I sincerely recommend you post the question in Microsoft Power Automate Community - Power Platform Community. It is the specific channel handling this kind queries, members and experts are professional and will assist you further. I also notice you mention there is no option to reorder the questions and answers of the form. Personally, I understand the inconvenience of lacking these settings in current build-in settings of Excel and Forms. Due to this situation, I also suggest you can go to this link: Microsoft Forms · Community to go to our Feedback community and send your feedback and requirements. Our product team is in charge of the site, and they constantly check customer reviews and feedback, and a lot of the features are developed and improved based on customer feedback. Appreciate for your patience and understanding. Hope you having a nice day.

Sincerely, Harry | Microsoft Community Moderator