Connect results to Excel as a data source rather than opening a new workbook each time
The form is great in terms of collecting responses. That said, if you need to view all the data at once, you need to open the form results in Excel. If you do that, it appears to open a separate result each time. There is no way to edit the results workbook. For example, I want to add a column to the results where I can keep track of whether or not I have contacted the person since they have filled out the form. If I add that column to the table, it will be gone when I next click "Open in Excel" to get the latest data. It would be great if it creates a connection to the workbook and is treated like a data source for the workbook.
Hi Forms users,
Thanks for using Microsoft Forms and providing feedback.
We just enabled a new feature “Forms for Excel” for all Microsoft Forms customers. When you create Forms for Excel in your OneDrive for Business / modern SharePoint team sites, you will get an Excel workbook at the same time. All the responses data will show up in your Excel workbook in real time.
You could get more detail from – https://aka.ms/formsforexcel
Please be free to leave comments if you have any question.
Microsoft Forms Team
While using Forms for Excel has "solved" this issue, why can't forms created from forms.microsoft.com through a business account just be created as a Forms for Excel? Have the Excel document live in the OneDrive folder of the person creating the file that then gets shared with anyone who gets the share link that includes collaborating. That would be ideal.
Samuel Mueller commented
This does not work for the survey. You should be able to do this from the Forms App, not the onedrive app?
as of today 2019-04-22, it seems the "Open in Excel" button will open the ONLINE sheet, that allows for customization and additional work, while still maintaning the link with the originating form. From there, you can edit the results workbook or download it to a local excel file if needed.
This works on a form created with the menu "Forms for Excel" inside any OneDrive folder.
This is really some great advancement and maybe even be considered to resolve the request of this topic.
(create a connection between the workbook and the form, and the form to be treated like a data source for the workbook).
with forms menu in excel, one can make only particular type of forms, but not the quizzes.
can we make the quizzes from form menu in excel?
If I create a Form for Excel, the data will auto-update if I embed the results in a webpage, right?
If I make changes to the Excel results file that is in my OneDrive, will those changes appear also?
For example, if I create a column with a project status on it, and for each form response line, I enter "In Progress" or "Completed". Will my new column and data entry appear? Or will each subsequent response update wipe out my new column info?
Hi Microsoft admin, may i know how to move the excel file (which is linked to the Sharepoint form) to a different folder? I tried doing this, but the responses from the form doesn't sync to the excel file anymore. only when i move the excel file back, it starts to sync again.
Tomas Moreno commented
And link to Power BI!
The forms for excel thing is kind of a pain. I should be able to copy the link from any excel file in SharePoint and put that link into a field in any existing form at forms.office.com and establish a permanent data link. Alternatively I should be able to query the forms data within Excel just like any other data connection by simply dropping in the link to the form.
+1 to the comment that it doesn't make sense to have different views based on different entry points. The primary entry point for a form is forms.office.com
Hi, I have difficulties working with the results from my forms in excell, I want to merge data from many forms into one, but the data does not seem to work?
Google has this feature with their Forms - very convenient!!
Nice to access the Spreadsheets from the online drive.
Changes to Form data sheet layouts are maintained!
Sam Moffatt commented
I really want a way of being able to take a form and link it to a new Excel document in O365. I just made a form and now I can't get to the data easily so I have to recreate the form, again, except now starting from the Excel sheet instead. This should really be two way.
I am not satisfied with this response. The main entry point for most users is via Forms, not Excel or One Drive Business, therefore the average user discovers their data isn't updated in real time until it's too late to fix.
The excel workbook options should be streamlined across all ports of entry into Forms - doesn't make sense to have different data viewing options across the same service.
Microsoft Access would be another option. The forms are so much better in Access than in Excel.
I am not satisfied with this solution as my initial thought when creating a Form is to open Forms, not Excel. In addition, I do not want to be forced to download the data when our company is aiming at going digital.
This does not work as intended. I have used the excel survey option many times and short of rebuilding it in powerapps with excel as the data source in onedrive I end up with responses in the wrong columns if you open up the excel file in web excel it then modified three columns and placed the information in the next table. I cant reliably consider this excel based option over Microsoft forms. Google has this feature however with IT security restrictions I cannot use them. Thus I am stuck trying to work around limitations I did not have in a free alternative and now going the slower route to rebuilt an extensive form into a powerapp in order to complete the form we need. Please link MS forms to excel for onedrive business at least!
Same here. I'd really like to be able to connect an existing form to an excel document in OneDrive for Business. The same way it is possible with Forms for Excel. Normal users will open Forms to create a form and will search afterwards to connect it to excel. Not the other way around. In google, a worksheet in google Drive is automaticaly created. It's much more user friendly.
I have the same issue in my application of Forms. I need to have the results push through to a live Excel which also needs a column added to acknowledge that the Forms entry has been reviewed. Forms is great, and very powerful, but without a way to better work with the results without manually editing the data, alot of efficiency and value is lost.
When you start the questionnaire by Forms, you allow access as it does SharePoint, without the need to download a spreadsheet with each new update or new response.
Judi Stewart commented
Would be great if I could get the form responses to sync to the workbook reliably... When I create either an excel workbook in SharePoint Online, and use the create form tool, or create a forms for excel document, the form is created ok, but the workbook doesn't always check in properly and it never gets sync'd with the responses - but, then again, sometimes it's fine and I can't see what's different that might cause the different outcomes :(
I have connected a mySQL data source to my excel, Can other users with no access to the mySQL db get updated data everytime they open the excel?