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!