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
Brian Connelly commented
This could be handled with Power Automate. I have created surveys and feed the responses back to Excel Online which I use as a datasource for Power BI.
Dion V commented
Somehow MS managed to break the Forms functionality. I've been showing staff how to use Forms, and one of the key features was that the Excel file *was* being created even when using FORMS to create the form.
Suddenly, this week, creating a form from within Forms itself does not do this anymore.
Then I read that the Excel file is only created if the form is created from within OneDrive or Excel Online. SERIOUSLY???
Any method of creating a form should do that same thing: Create the Excel storage file. Period.
We need to read this file into PowerBI for reporting and can not be downloading the Excel file every time we need new data in PowerBI.
Omar Ghannam commented
Can we get an option to link previously created forms to excel sheets the way it works when you click "Forms for Excel" in OneDrive as suggested by Zhongzhong Li above?
"Forms to Excel" should be also available for a Personal/Home subscription of O365. It would be very helpfull to collaborate with friends and family
Patrick O'Toole commented
As far as I can tell, editing the workbook (e.g. adding responses from another system or older version of the form) does not cause the results dashboard to update. This means that responses are only copied into an Excel workbook as rows, right?
Peter Brink commented
I found out just testing this was an issue.
WORKAROUND, go to onedrive, create a new excel file (or an existing one), insert -> forms -> new form.
If you go to forms.microsoft.com, create a form and get responses you can only download.
If you go to excel online, then create a form the responses will show there.
WHAT I WANT:
If you linked a form to excel online when you initially created the form you can open in excel. Right above that you can click the 3 dots and can click "Sync all responses to new workbook". If you did not create the form via excel initially you do not have this option.
All I want is to have that option on a form that was created initially in forms. Let me select where to save the file in my onedrive. This would also be good for the current option so i can move it to somewhere else.
A created file available in OneDrive and dynamically updated with results should be a basic feature, not some worked on extra and accessed via a different route!
This functionality is a major omission in this offering.
Loads of functionality missing that could be added and still way off G forms so some work yet to do.
A better implementation of long existing html forms elements would be a good place to start.
This feature does not give you an option to create a quiz. Perhaps all forms created should have all the functionality so you don't have to change between a survey or quiz.
Quentin Poncelet commented
Hello the Community of Forms !
What a frustratin when I tried this cool 'Forms' feature in my O365 environment ...
My wish was to get an online Excel where I can find all the answers, linked to the Forms I have created and sent to all the people to whom I asked questions.
But ... When I click on "Open in Excel" button, it DOWLOAD AN EXCEL FILE ... :'(
After some searches, I found that to get this great feature, I need to FIRST create an Excel Online, AND THEN add a Forms.
So great ! When i click on "Open in Excel", it open the Online Excel file.
Frustration is coming...
I can't switch from the first mode 'download' to the second 'online' one ...
But the link is already sent to hundred of poeple ... :'(
And both Forms are really working exactly the same (even if I've created them in 2 different way), except this feature of collecting data, so that I can 'play' with them as I wish in Excel ...
Hope this can be solved in the meantime, within the configuration panel ?
PS1 : don't hesitate to ask questions if my explanations are not clear - sorry for my English, I am a Belgian guy ;-)
PS2 : I've attached a visual explanation ; because images are sometime better than words to explain ! :-)
Since it's now possible to have "Forms for Excel", could it be possible to add the file name and location (on one drive) to the settings in Forms so that "Forms for Excel" is just "Forms" with a specified saved location.
how do i change the value of the cells in my spreadsheet to number, rather than the default of a text value so i an total things in the spreadsheet
I have added my Excel forms to my TEAM in Microsoft Teams, however I would like to be able to add the LIVE spreadhseet (of the responses to the forms) to my management TEAM (within Microsoft Teams) as well and can not figure it out. If I upload the file the spreadsheet does not update automatically within Microsoft Teams. I need them to be able to view the worksheet and not the summary
Deb W commented
Option to send it to a list...so I can add a column and manage my results (actions etc)
Joel Myers commented
It seems pretty obvious that people would need this. Any updated
Any reason "Forms for Excel" is only available on individual level and not group level? I'd like to be able to make forms and their excel workbooks available for several people in my team. However, when I create a group form, the excel workbook is automatically created in the main "Documents" folder in sharepoint, with no apparent fix to sort this into a specificly chosen folder within our structure.
It's causing major clutter.
As soon as we try and move the workbook to a different folder, the link with the form is lost and Forms automatically creates a new workbook for new responses.
I have built and published a number of eLearning content for my organization that includes links to quizzes. I was hoping I could link any quiz I made to the same Excel file, but there doesn't appear to be an option for this.
I can create an Excel file and create a form for it, but it doesn't give you the option to create a quiz. Even if the option was there, I would not be able to link multiple Quizzes to the same Excel file.
So if I wanted to create a dashboard of all the quiz results using data from all the quizzes from separate sheets in one Excel file, I can't.
Google does this....(YOU NEED TO PROVIDE AN OPTION TO LINK MULTIPLE QUIZZES OR FORMS TO THE SAME EXCEL FILE) that way data from multiple places/forms/quizzes can be compiled and reviewed.
If there is already a solution for this, where is it???
In the results, can we choose only to see results from specific question(s) from the form? Also, can we have results from multiple forms combined in one spreadsheet.
Rick Hyne commented
I am in Teams.
I create an employment application form.
The excel book is stored on SharePoint under the Teams channel.
I don't want to destroy the data in that book, so I create another book and pull from the first book.
All is good as I can refresh the second book. I can pull subset data and put it in a separate sheet.
On the first book the questions were part of the header row. Now they are gone and the header is named column 1, column 2.
When reviewing the entries from the form and selecting delete entry, it does not delete from the excel book. .
Dan Taylor commented
How do I save the excel file without the data source features attached to it? I'm importing the excel file into Cognos for reporting purposes and it appears that Cognos doesn't like the form feature within the excel file. Please help? Hope that made sense.
Why not SaveAs a Master the first downloaded workbook, then just transfer new information into it manually when you download new information. Added columns remain.