This post is the first of a few where we look at how easy the Office 365 stack integrates. In this series, we will:
- Create a Form and have the data save directly in Excel (this post)
- Add the Form to Microsoft Teams
- Notify the Team a submission was made
- How does this look like on the phone
I came across this neat little feature and instantly fell in love. We know about Microsoft Forms, the Office 365 quiz and polls tool. We know this can be easily exported to Excel. However, if you have an existing Excel file, you can use Forms to import directly into your existing Excel file!
Yes. Import. Directly. Into. Your. Existing. Excel. File.
I find this as a big deal. There have been numerous use cases for this over my last 20 years of consulting. People just want to collect data, in a clean manner, and then analyze it in Excel. This also comes in handy when you want to use the form for more than just a survey tool. I’ve set up a Form as a request tool for the intranet. Users submit intranet requests it in Forms, it writes to my Excel file, then I can triage the request, manage that data in my Excel file, without having to download it, or writing a Flow to write the data to the Excel file.
If Excel isn’t enough, check out this other post and you can save your Forms into anything else, like Planner, Trello, Slack, etc.
Start with Excel in SharePoint Online
Create your Excel file, if you don’t have one already. Any Excel file will do, make sure it’s the xslx file extension (if you created it in O365 or Office 2016, you should be fine). Now throw that file up in SharePoint or Teams.
Here I have a basic Excel file, with the goal of managing a booth at a trade show. I need 3 people at the booth, and I want to collect who wants to man it when.
Alternatively, you can create a fresh new Excel file, with a Forms front end, from your SharePoint library!
press the New menu and select Forms for Excel. Name your Excel file and you’ll be sent to the Forms designer.
Will this work with OneDrive?
Great question! Thanks for asking. I don’t know. Use SharePoint. I have normally used a shared Excel file in Teams/SharePoint, but for my first round through this post I decided to use OneDrive. I got through creating the form, but no results would save in the Excel file. Really weird. I’m guessing there’s a connection missing somewhere. If you try it in OneDrive, let me know how it goes!!
Open Excel file in Office Online and create the Form
Go open up your file in a browser. A fast way to do this is to click File menu, then click the path and click Copy path to clipboard.
Now paste that in a browser. Any modern browser will work, I’m using Chrome.
Edit your file, click Edit Workbook, then Edit in Browser.
Create your Form
Up in the HOME ribbon, in the Tables section, click Forms then New Form. This only appears in Excel Online, maybe it’ll be in Office 2019?
Did you know that was there? I found it one day and clicked it. Awesomeness
And now you’re in FORMS!! It’s awfully nice of Microsoft to assume the same name of the form to the Excel file. Yes you can change that.
Click Add Question and start creating your form! Add your first question and navigate back to the Excel file in the browser and see what’s happening.
This might take a few moments to catch up, sometimes it’s immediate, sometimes I had to refresh the browser to effectively see the columns.
First off, a new worksheet has been added, called Form1.
Each question gets added automatically, and you’ll see a bunch of additional columns get added like Start Time, Completion Time, Email, and Name. These auto-generate, which is awesome.
Finish throwing your questions in, here’s my impressive form:
Yes, XXLT is a size, and my perfect size. No one ever offers it.
You can test it real quick, press the Preview button at the top and fill it out and watch Excel load!
Once this data is in Excel, you can then treat it like any other data table. Use vlookups, functions, etc. to continue your analysis.
In my example, I used a vlookup to populate the Mobile Number of each person. Now we have a single page view of who’s working the booth, when and how to reach them.
Couple of things…
If you want to change the form, you’re best bet is to access it via the Forms button. You’ll probably get assaulted with something like this to remind you:
If you go to Forms, depending on where you stored the Excel file, you may see it either under My forms or Recent group forms. Since I accessed this via a Team (a Group) it’s a group form
I’ve noticed that the data is pushed into Excel, if you delete rows in Excel, it still exist in Forms, BUT not sure how to get it out after the fact. I deleted a bunch of rows from Excel, and they still exist in the Forms app. I think that’s great, but there doesn’t appear a way to refresh, download, or otherwise get that data from Forms. If this is a need, we can use a Flow to write the data to another datasource.
When Forms writes to Excel, the Last Modified By is SharePoint App. It’s nice something uses a service account in Office 365 (which Flow doesn’t support yet).
Do you have an existing Form you’d like to hook up to Excel? Check out my other post Saving data from Microsoft Forms into Excel using Power Automate (flow)
Done!
This was relatively painless right? Stay tuned to my next post on how to add this form to Teams!
This solution has almost what I need. Is there a way for this form to be worked on then returned to submit later? I have a lengthy form that won’t get filled out at once.
sadly no, I don’t think that capability exists. You’d have to look at using PowerApps or something similar, where you can write the data to persistent storage (i.e. SharePoint list or SQL) and then pull it back up for a user when they return.
Can you add additional columns to the forms sheet to continue manual data input for the forms entries? i.e. I want to assign each forms submission to a few various employees to follow up on each entry. Can I do that in the forms sheet without breaking the form?
“in the forms sheet” meaning Forms or Excel? I believe Excel, and yes, you can add columns next to it in excel as needed. I also create a new worksheet and use vlookup to pull data from the forms data into my tables. Many ways to handle it.
David,
I have gotten everything set up with the form and such, but can you tell me how you pulled the name from the forms excel sheet over to the main excel sheet you were filling in. You mention that you use vlookup for the mobile numbers, but how can i get the excel to see what times the perosn picks and then populate their name in my main sheet which is basically a schedule like yours.
I can’t find the original Excel file, so I _think_ it was just an =worksheet$B2, targeting the cell. So it’s a manual pointer, then the rest of them can use vlookup to get the other columns
Hi David, I have found you posts very useful as our organisation moves to O365 and have shared those relating to Forms with colleagues. I am not clear whether to start in Forms and use Power Automate (PA) to download the data to an Excel file on SharePoint or whether to use Excel Online to create the form so it is already linked? Is there any advantage of one approach over the other?
I used PA to download new records from two forms to a single Excel sheet but not sure how to automate the clearing of records from Forms once downloaded – i am concerned about what happens if the 50,000 record limit is reached as i am looking to use this approach for a national study so responses could top that figure. Any thoughts and advice greatly received.
It all depends ;)
If I am making a quick form and want to manage the data just in Excel, then I’ll create a Form from Excel.
However, if I think I may want to pull in additional data after the form is submitted, or add routing rules, or anything more than simply saving to Excel, then I’ll create the Form and use Power Automate to save it to Excel (along with other actions I need).
As far as removing the data from Forms, no idea. I haven’t figured that one out yet.
Consider upvoting to help MSFT know we want it!
https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Flow-action-to-delete-all-MS-Form-responses/idi-p/131849
Hello David, I am missing something on Forms. I want some basic formatting like font, size, color, etc. but for some reason when i click on my survey question, title or description, there is no format tool that populates. I have searched the internet for hours on why and how to fix this. Do I need a different/paid version for basic font formatting? Your response will be very very appreciated. Thank you very much! Have a good day :)
Sadly there isn’t much by way of formatting in Forms. See https://davidlozzi.com/2018/11/13/squeezing-a-little-more-formatting-out-of-microsoft-forms/ for some tricks to squeeze some formatting out. This is a little dated but still applies
Hi David, thank you for your tutorial.. do you think it’s possible to avoid duplicate data entry? i need to build a database with that form
For sure, you can have that data from the form go directly into a database if you’d like. Check out https://davidlozzi.com/2020/03/06/saving-data-from-microsoft-forms-into-excel-using-power-automate-flow/, and instead of writing to Excel you can write to almost anything else.
You’ll have to process that in Excel, using the remove duplicate data option. Or you could use Flow to process the forms, and have that check for dupes before submitting.
Hi David, thank you for your tutorial.. do you think it’s possible to avoid duplicate data entry? i need to build a database with that form
You’ll have to process that in Excel, using the remove duplicate data option. Or you could use Flow to process the forms, and have that check for dupes before submitting.
David – loved the article. We are making a first attempt at this. I have some data that already exists in a workbook. I made the form and have collected some information, is there a way to import the data into the new workbook created by the Form? Also, I noticed that there are keys and time stamps, can you turn that off or do you need to include it?
Hi Tim! If you already have the form created, you can’t link it directly from Forms to Excel. However, you can use a Flow to copy data from the Form into Excel, see this other post for more info: https://davidlozzi.com/2020/03/06/saving-data-from-microsoft-forms-into-excel-using-power-automate-flow/
There is no “Forms” button in Excel online. Why isn’t there a Forms button??
Are you using Excel in the browser? Where is your Excel file stored?
And I just answered my own question. Apparently the forms button only appears in Excel online if the spreadsheet is in OneDrive for Business or a Team site associated with an O365 group. In my case, it is on a SharePoint site that is not associated with an O365 group.
Bingo! It’s a weird “feature”?
There is no “Forms” button in Excel online. Why isn’t there a Forms button??
Where is the file located? AND are you in Excel Online in the browseR?
Thank you for this, it was very helpful. Except, I actually did this in reverse order. I created a form(quiz), submitted a test result and exported the results spreadsheet. I then uploaded the results spreadsheet to OneDrive. I went to the home tab in the new spreadsheet and created a new form. To my surprise, everything including the multiple choice answers migrated over to the new attached/linked form(sections didn’t go with it; but totally not a big deal). This is awesome!
But, here’s the rub…the points and final points column then dropped out of the new spreadsheet. This is the MOST IMPORTANT PART! We need to know instantly by looking at the spreadsheet results if the person got an 80 or above(the passing threshold).
Any idea how we can get a spreadsheet of the form results?
Hmmm… then what you might want to do is use Flow to move that data over into your Excel file, that way you can map every field/question appropriately. I am almost done with a post that will explain how to do this. Basically, it’ll cover taking an existing Form, export the Excel and use Flow to update it.
I deleted information on my excel spreadsheet and can’t seem to get the old data to reappear. Have you found a way to get the responses to appear back into the excel file? Could refresh selected connection do the trick?
Sadly I don’t think so. Try the refresh option?
Amazing! Thank you so much for sharing this with us!
Is there are way to configure an excel spreadsheet to refresh on an existing form?
I have not found how to have an Excel spreadsheet connect to an existing form. You can use Flow to push the data into Excel but only for new upcoming submissions
Create a tutorial!
Sadly when a tried to implement it for personal use, I found out Forms For Excel is only available in office 365 for Business.
It is possible to create a Form directly from Onedrive, but the responses need to be manually downloaded as an Excel file.
The only workaround I came up for sending form responses to an online Excel sheet is creating the form using the old Excel Survey, which seems to work fine, except for the fact the interface is not responsive and the design is probably 10 years old.
I hope someday Microsoft releases Forms for Excel for personal subscriptions plans as well.
If anyone have any other workaround for this, please let me know!
Have you tried using Flow?
Amazing that you’re still answering questions on this! Love this feature, running into 1 issue. I have a macro-enabled .xlsm file that I want my form to connect to. Unfortunately, I can’t open the form in excel online because excel online does not support macros. No excel online = no create form button. My genius solution to this was was to create a Flow to update my rows in the .xlsm file BUT, it turns out that the excel online (business) connector ALSO does not recognize .xlsm files as “excel files” so there’s no functionality there.
Any thoughts? Do I just have to accept that I will have to give up on my fancy macros?
Sadly yes. I haven’t done much with macro workbooks and namely for this reason, they’re just not supported as well as normal workbooks. Could you have the Form write to SP or Excel and then have a macro read that data and pull it in? That may be your best bet here.
Hi David, I read this and it is very interesting. I have one question concerning the possibility to share the xls answers file with other colleagues, how is this feasible?
br
Marco
Very easy. You can share it like sharing any other file or if the excel is in a SharePoint site you can add them to the site.