Forum Discussion
Forms responses missing when Excel is opened
I have created a Form in Office 365 Business and now have 81 responses. When I try to open the responses in Excel, it only shows that I have 1 response. Please advice.
Dear Forms customers,
Forms for Excel contains a live data connection to your form. Any new response data will be stored in your form, and also be reflected in your workbook. On occasion, the workbook may not contain the latest data for a number of reasons.
If this is the case, Forms provides a new feature, "Sync all responses to a new workbook", in order to “sync” your most recent responses to a new workbook. This new workbook will be stored next to your original workbook on OneDrive for Business or SharePoint Online. This will ensure your data is up to date. Learn more about how to get missing data.
Regards,
Zhongzhong
I posted a solution back in May on this thread but it seemed to have disappeared. Here is a summary.
I had the same problem with 50% of the responses missing.The best way to maximize your chances to have all your responses in your Excel is to have two excel documents. One document for the data and one for the processing of the data.The reason is that Excel tries to calculate every formula each time forms add a response. It can take a long time especially if you have a lot of formulas (I had 1000 complex formulas referencing the data). Forms is like a user, it tries to add data but if the document is calculating or blocked by another user it gives up. You then end up with missing data.Having a document for data allows forms to input data without being stuck by excel calculating or another user modifying the document. Excel will only try to calculate formulas when you open the processing document containing the formulas. It's also a great way to change your data document if needed.With this, I nearly never had missing data.Hopes it helps.Having a similar issue as described.
I realised that the link between my Microsoft Form and Microsoft Excel Workbook broke when I added a macro to one of the sheets and it saved the file as an .xlsm macro enabled workbook. I got rid of the macro and tried saving as an .xlsx again but it made no difference.
Have tried changing the False to True as described several times earlier, have also tried syncing all responses to a new workbook and also opening in Excel from the Forms Responses tab but no luck.
Raised the issue with our IT support 3 weeks ago who raised a support ticket with Microsoft, unfortunately I have had no response other than one offer of a callback one week ago which I agreed to, but Microsoft never bothered to call.
Yesterday I escalated to our IT support again and they have escalated to their own IT support who are closer to Microsoft.
I am hoping for an answer promptly as it has been broken for 3 weeks and I need to have this resolved tomorrow as I need to use the data for end of month reporting.
Any help would be much appreciated.
longdingMicrosoft
Chris-Smith The user is a member of the SharePoint site or the owner of OneDirve to sync to a new workbook, Please first make sure this is true. Then try "Sync all responses in a new workbook" as in below link, it should work usually. https://support.microsoft.com/en-us/office/how-to-get-missing-data-in-forms-9fb98299-4dcc-41a4-bb29-34a9c3daf8cc
If "Sync all responses in a new workbook" still not helping, please collect network trace and escalate to support team.
And we suggest NOT to change the workbook Forms sync to, like formula, macro, etc. It may cause live sync broken.
What a mess! What a complete mess! My original Excel spreadsheet was missing lots of responses (blank lines appeared in the spreadsheet where the responses should have been). So I followed the recommendation to synchronise to a new spreadsheet. Hooray, I could see (all?) the missing responses. However, I needed to open the spreadsheet in Excel 2016, but it refused to open. The path in the SharePoint document library was already fairly long and the new name automatically added for the new spreadsheet was really long as well. So I tried renaming the SharePoint folder, but that didn't change anything. Next attempt was to create a new SharePoint folder with a much shorter name and move all my files to that and sync it.
Great I can now open the original forms spreadsheet (with its missing responses) but I can't edit the form. Brilliant, just what I needed (not). All I get is the option to create a new form. I can still get to use the old form but the responses don't seem to get recorded anywhere. In the new spreadsheet (created by syncing to a new spreadsheet) I can see the data but all the Forms options are greyed out.
Right, so let's recreate everything from scratch. I've got nothing else I want to do with my life, after all.- Zhongzhong_Li
Hi Alan,
Sorry for the late response.
Looks like you have met several critical errors
1 - Missing data in Excel workbook
2 - Forms settings (e.g. anyone with the link could response) takes hours to reflect on customer side
3 - "Sync to new Excel workbook" doesn't work well for you.
It would be great if you could share your form URL with us, so we could have further investigation.
I'll send you a private message to have more detail follow up with you.
Regards,
Zhongzhong
I have recreated everything from scratch and it seems to be working. However, there are a couple of dodgy things about this:
1) The Forms icon on the Excel online spreadsheet can take several minutes to become enabled.2) When I make changes, e.g. to allow people outside my organisation to be able to fill in the forms, it can take hours before some people on the web can use the updated version. For example, I made that change to my form about 3 hours ago and one user down the road from me is still being asked to log in.
I'm getting the same problem (30th May 2018). I was getting blank lines appear in Excel Online and have been deleting them. Users have now been telling me that they have been entering data on Microsoft Forms, but I thought they had done something daft like not pressing the Submit button. However, in forms responses I can now see they have indeed been entering data, but Forms / Excel Online have been messing things up. What a mess!
Note that I have Synced the Document Library used by the spreadsheet/form to my hard drive as I link to it as a read-only linked table in Microsoft Access.
I see this has now been going on for 8 months. Come on Microsoft. Pull your finger out!
- Zhongzhong_Li
Dear Forms customers,
Forms for Excel contains a live data connection to your form. Any new response data will be stored in your form, and also be reflected in your workbook. On occasion, the workbook may not contain the latest data for a number of reasons.
If this is the case, Forms provides a new feature, "Sync all responses to a new workbook", in order to “sync” your most recent responses to a new workbook. This new workbook will be stored next to your original workbook on OneDrive for Business or SharePoint Online. This will ensure your data is up to date. Learn more about how to get missing data.
Regards,
Zhongzhong
Hi Zhong ZhongZhongzhong_Li
My sharepoint form was created using Form for Excel in Folder A, which means that the responses from the Sharepoint form syncs to an excel file. However, I need to move this excel file to another folder, lets call is Folder B.
When I move the excel file to Folder B, new responses to the form does not go into the excel file anymore. The best solution I managed to find so far is to use the option "sync all responses to a new workbook"to a new excel file. But the new excel file is again created in Folder A instead of Folder B.
Is there any solution or way to relink the excel file to the Sharepoint form after I moved the excel file? Pls let me know urgently. Thanks!
- Zhongzhong_Li
Rele_2018 we use FileID to identify the file in SharePoint. If folder A and folder B are in the same SharePoint site, then you should be free to move the file, and the data sync connection keep working. If you met problem, please let me know, it should be a bug.
However, if folder A and folder B are in different SharePoint sites, we don't have good solution here (you could sync data into another workbook via Flow).
"Sync data to a specific file" is in our backlog. But I'm not sure if x-site file is in the scope.
Hi Zhongzhong Li,
We are currently not able to view all of the responses to our form in Excel, could you please advise?
Please let me know if you need any further information for this.
Thanks,
Rachel
- Zhongzhong_LiHi Rachel,
Based on the log we had for the URL you have shared, it shows your form hasn't connected with an Excel Online file, it is a standard form with "Download Excel".
Could you share me the actual error you have received?
Regards,
Zhongzhong
Dear Microsoft,
When are you going to sort this out, please? I am again missing at least one response from my Excel spreadsheet. I know this because I demonstrated to someone how to use the Excel form. They got an acknowledgement of their input and when I got into Edit Form --> Responses I can see the item I added, but when I click on "Open in Excel" the spreadsheet opens in Excel Online missing that data.
This is a mess. Please sort it out so it works properly. Your post says, "On occasion, the workbook may not contain the latest data for a number of reasons." It's time to sort those reason out, don't you think?
Hi,
Zhongzhong_Li I sent you a private message regarding a similar problem, can you please check your messages?
Thanks,
I'm having a related issue:
I was working on a type of rudimentary ticketing system in which clients fill out the form and then our team can update status on the back end.
Everything was working fine until I added our backend columns to assign and track. Now it is no longer updating with new form submissions.
I'm guessing it is some sort of mapping issue but I can't find where I can fix it and there's little to no documentation or support...
UPDATE: Apparently it didn't break them but added them to row 1000+ ... no clue why and probably randomly making me file ginormous. but at least it still works...
Hey!
I've created a form that had 4 questions. The form was used by us to gather data in a bounty program, when we saw that we already had over 10000 completions, we decided that we needn't more so we changed the intro text of the form to inform them that the bounty is over. Then we thought that we should delete the questions because we didn't want people to fill in more data. We did not download the CSV before deleting the questions and when we downloaded the CSV it was filled with 10000 + empty responses. The problem seems to be that if you delete a question it wil automatically delete the column from the csv that powers forms. Of course we did not know this and couldn't guess that it would delete even all the previous responses. So I think we need a previous version of the csv, before deleting the questions.
The form was exclusively handlet through forms.office.com.
- Hi, I'm having the same problem. My form populated the Excel database (in OneDrive) for the first 16 responses. I tried copying the data from OneDrive and something must have happened to the connection. I can see new responses in the form, but not in the Excel sheet. I urgently need to be able to fix this. Thanks!
Just ran into this problem last week and now again today. I have 4 broken Forms I'm using for student assessments. Zhongzhong_Li, I pm'd you with the URLs
I am experiencing the same Issue, it seems like a specific Form is linked to a specific Excel sheet and if you change anything manually in the Excel sheet it might break the possibility for the Form application to save any data in the Excel sheet.
I expected it to create a new Excel sheet if it cant save or find the original Excel sheet. But that's not the case but should solve the issue for most of us.
- Zhongzhong_Li
Hi Emanuel Foglander, Maria Kilgore, Nate Kennedy, Timothy Denton, Erik Michael and Evan Raiewski,
Thanks for reaching out and sorry for the error for your Forms and Excel workbook.
Do you mind to send me a private mail with your form URL, so I could ask our engineers to follow up?
Regards,
Zhongzhong
If you click the export to excel button the link will flash and be redirected. But if you copy and past it then change the false at the end to a true it will open a new spreadsheet that will have all your data in it. This is the only work around I have found. But you will have all your data this way.
Example
data. https://forms.office.com/Pages/RedirectToExcelPage.aspx?id=RgHwM8xv6Um1aHiWswadRM6biPXhOnlKtde69MV1dHQUFYT1g5VzFUMjBPR0QxOUdQTi4u&forceReExport=false - change this false to a true
I hope this helps some of you!
Having this issue as well.
