Sharing Forms Data in Excel

Question

I am on Office 365 Education/for Business. I have a form that is answered ~25 times a day every day by over 300 users. At the start of last FY, we exported the form into an Excel spreadsheet, put in piles of pivot charts and graphs to express the data, and sent up weekly comparison tables. 

On my copy of the Excel document, every entry into Forms syncs into the spreadsheet and updates all tables and graphs. However, with any colleague I have shared this document with, they can only see the data as it was when I personally last synced the document. If they log in and try to sync the data, Excel will ask for my personal Office 365 log in to sync the data. I am often out at other meetings and unable to sync the data when everyone needs it. 

Is there a way to set up the Excel sheet with the charts and tables so that anyone I give permission to can sync the charts?

Answer 1

Hi Cliff, 

Last year, we exported the data to a single spreadsheet and added the graphs and charts. Then, whenever anyone opened that spreadsheet, they were able to synchronize the data and have all pivots and graphs update automatically. 

Adding the charts and updating the data manually is not feasible as the form will be getting thousands of submissions a month and the parsed out data needs to be viewed by people who are not very tech-savvy. 

I found on the MS Tech Forum that the excel/forms syncing tool has been eliminated. I've tried experimenting in OneDrive with the "Use Forms for Excel" tool, which syncs the data automatically but makes adding in charts/pivots an incredible burden as it has to be done in Excel Online. At this point, I think I'm just going to buy a Survey Monkey subscription and move over to that.

Answer 2

Hi David,

To move forward, I’d like you to confirm and provide the following.

1. How do you export and share the Excel file? Could you list the detailed steps about it?
2. List the reproducing steps of the issue and some screenshots about what users are experiencing.

Generally, the default Excel file exported from Microsoft Form is downloaded in the local folder. You may need to add the charts and update the data by downloading a new file each time. Then you can upload the Excel file to a shared folder online with other people for them to view the updates.

Moreover, they can export the Excel file with data just with their work and school accounts or a guest link if you share the form with them via Share>Share to collaborate in Microsoft Form.

Thanks,

Cliff

Answer 3

What you are looking to do is grant someone else permission to edit the survey stored in your OneDrive.  So try searching for that.

You may have better luck recreating the survey in SharePoint. this article talks about editing permission for the survey:

https://support.office.com/en-us/article/View-permissions-on-a-survey-a1ea7488-b16b-464b-a5f5-a89428df6b8d

Answer 4

Hi David,

Please let us know your updates at your convenience if you need further help.

Thanks,

Cliff