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
Is there a way that Forms can add data to an existing Excel spreadsheet? Like the way Google Forms does?
Amy Hill commented
Is there no way to make this work for forms that were created through Forms and not forms for excel? I am REALLY trying to use Office 365 for work instead of my personal google account but at every turn it is just so damn hard and inefficient.
and what about new feature "Quiz for Excel" ? "Forms for Excel" doesn't include note and feedback because it generates a survey only. we can't choose between survey and quiz. I would like to get en Excel workbook with all the responses data of a quizz in real time
"NEED this. Having to open a new file each time is awful. You can't capture new data once you download your responses, nor live edit the data. Google forms does this really well. You can edit the data (add columns, rows, etc.) without messing up the live form responses. It's perfect."
That's right! Please check how it is working in google forms and sheets and do it like this. In Google you can edit the spreadsheet which is connected to the form without problems. And the sheet is still connected to the form.
The idea is from "May 24, 2017". SO WHAT YOU ARE WAITING FOR?!
NEED this. Having to open a new file each time is awful. You can't capture new data once you download your responses, nor live edit the data. Google forms does this really well. You can edit the data (add columns, rows, etc.) without messing up the live form responses. It's perfect.
Does anyone else think this should be just "how it should work"?
Malissa Thomas commented
How about for non-business users? I don't use SharePoint for my small business and having this data in a format I can manipulate is essential. My goal is to have the data linked to power query for reports, graphs and charts. Thank you
I have created several forms using the app that need an excel sheet that is linked to the cloud/onedrive. is there a way to change a regular form to a form for excel without having to rebuild each form? We have created over 4000 forms and a way to do this would be a great help. Perhaps a button to convert to forms for excel.
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.