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)
This was relatively painless right? Stay tuned to my next post on how to add this form to Teams!
Hi, I’m looking to use forms as a tool for marking attendance for a weekly school assembly type session. The students scan the QR code on entry and then this data is used for attendance.
I need to then summarise the data so that a school admin can mark the attendance into our system – she needs a full list of those both present and not present.
I’ve written a VLOOKUP to gather the form data and compare to an existing list of students to show which students have completed the form on another Sheet in the excel workbook, but I’m looking for a way to do this week-in week-out without having to make a new form and/or new excel workbook each time. Ideally, I’d love to be able to have the attendance form for each week import into a new sheet in the workbook, but can’t see a way to have more than one form linked to a workbook. I saw your method above and thought maybe I could have Form1, Form2, Form3 etc, but excel only seems to allow one form created through that method.
Would you suggest that using a Flow might be the best way to achieve this, or any other thoughts?
I want it to be a new form so that students can’t easily save the QR code and mark their attendance while not being there the next week. Alternatively, if there is a way of forcing an MS Form to update/change its QR code?
Dorry for the late reply here! Didn’t see the alert until today. I would explore using Power Automate for creating the reporting data. it can read your excel file and create a report on whos missing or not. I would also suggest moving the datasource out of Excel, maybe a SharePoint list (depending on how many kids) as it may be easier to manage outside of Excel. With a Flow you can use the same Form every day!
I want to create a form and transfer the data, I understand how to do this process. My question is, when creating the “form” are there different options in creating the form besides the one shown?
What kind of options are you looking for?
we are importing right from Forms to Excel – however, one of the columns has the last number of the 16 digit number automatically change from whatever number it was to a 0. Every line. Oddest thing ever and we have no idea how or why this is happening.
that’s weird! sorry, didn’t see this sooner. Did you figure it out?
Hi, I have created Forms and saved the results in a shared drive. This means other people have access to viewing the excel results. Question – what can be done and not done to the results excel data? As it is an ongoing form (people can keep inputting information), can others go to the excel results and manipulate things? add columns that are not part of the form (i.e. “approved” column)? can rows of info be directly entered, pasted into the results excel sheet instead of entered through the form? Does anything break the link between the form and the results if the results data is manipulated? Thanks.
can others go to the excel results and manipulate things? yes
add columns that are not part of the form (i.e. “approved” column)? yup, I do this a lot.
can rows of info be directly entered, pasted into the results excel sheet instead of entered through the form? i believe so, although haven’t tried I don’t think. I believe it appends to the end fo the table, so should recognize your existing data and then append. might want to try that out
Does anything break the link between the form and the results if the results data is manipulated? Nope, it’s a copy of the data that gets pushed into Excel.
With all that said, I highly recommend testing it out if you have specific requirements or concerns, I haven’t been this intentional with the data yet. Let me know what you find out!
I am not sure after reading your article, but 1) can i put this form online and will it deposit form date right into excel online which would be on the server. 2) Alternatively can i put this form online and also have it deposit form data from this online form and download it from the server to my excel file in my personal computer. —— i am not sure if your talking about a Microsoft forms that i can put on my website, but that is what i would like to do, is just have a simple form on my website, but put the information into an excel spread sheet. If it is too much trouble having it download the data information from the excel on the server, it would be ok for me just to access my server and manually download the excel information that is on the server.
yes to both, if I’m understanding the ask. A form in Microsoft Forms can be set up for anonymous access so you can include it in your website. If you use the steps in this post, the form data will write into your Excel file. Alternatively, you can use a PowerAutomate Flow to write the data to another Excel file, or other data source, see https://davidlozzi.com/2020/03/06/saving-data-from-microsoft-forms-into-excel-using-power-automate-flow/.
I have a form for people to fill out information for change orders. The answers go to an Excel workbook in Sharepoint. Once the form is completed my flow collects the responses from the Excel file and sends an email. Problem is it is adding a repeat row of the information collected by the form in to my Excel file. Is there a way to stop this from happening
So your excel file has double entries? If you followed this post on getting Forms directly into Excel, do you have a Flow doing it too? If not, might have to contact Microsoft support
This all seems fine but I don’t see the Forms button on the Home online version of Excel. I’m using Office 365 and have Forms and Excel as part of this but can’t find anything that links them. I really want to do what Google used to do well which was to collect data with a form, put it automatically into an Excel sheet and then share it with a colleague, while all the people submitting data can edit and update their responses. I got as far as the Sharepoint Excel file and opening it in a browser, but I just don’t see any Forms options.
If you’re in Excel Online, in the browser and not opened in the local Excel app, then the Home version may not have this capability :(. You can try using a Flow instead, check out https://davidlozzi.com/2020/03/06/saving-data-from-microsoft-forms-into-excel-using-power-automate-flow/
Its in the Insert tab instead of Home
Hi! I was wondering we can do it the other way around. Wherein we can utilize the Microsoft forms to search for a particular set of data from an excel document and present it.
I don’t believe that’s possible (havent looked in a couple of months). Forms is purely intake, for surveys, etc. You’d have to look at something like PowerApps to create something more dynamic and interactive.
Hi, so I am creating a tool that allows medical staff to look up patient contact information held in an Excel file on a Teams private channel. A Form simply asks for the patient’s unique medical number. When submitted it looks for matching number in the Excel file. If found it presents the staff member with an Adaptive Card in Teams that contains the existing information which they can update and resubmit, otherwise it presents a blank Adaptive Card that allows new patient information to be added to the Excel file.
That sounds great! Are you using Forms in Teams? Or using a bot in Teams?