Use Microsoft Forms to collect data right into your Excel file

This post is the first of a few where we look at how easy the Office 365 stack integrates. In this series, we will:


I came across this neat little feature and instantly feel 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.

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.

Brand New Excel file

Alternatively, you can create a fresh new Excel file, with a Forms front end, from your SharePoint library!

Forms into Excel option in 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.

Get the Excel File Path to open in SharePoint

Close the Excel file and paste that in a browser. Any modern browser will work, I’m using Chrome.

Office does support co-authoring, closing the file shouldn’t be necessary. However, I’ve noticed it gets a little wonky through the rest of these steps. Just close it, you’ll edit it in the browser shortly.

Edit your file, click Edit Workbook, then Edit in Browser.

Edit Excel in the 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.

Initial empty Form

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:

My Microsoft Form for a Conference Booth Signup

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!

Excel with data from Forms

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.

Excel file using vlookups to get Forms dataMore Forms data in Excel

 

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:

Edit Forms from Excel

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

Microsoft Forms in the Group

 

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).

Done!

This was relatively painless right? Stay tuned to my next post on how to add this form to Teams!

Advertisements

10 thoughts on “Use Microsoft Forms to collect data right into your Excel file

Add yours

  1. Hi David. Great article! Quick question: after we submit data through the form, the data is no longer showing up in the Excel online spreadsheet. It seems as though the connection is ‘broken’. The data is still getting logged, as per the Responses continuing to increase. I can see the data by manually looking at View Results. However, it’d be preferable to have the data automatically pushing into Excel again. Please help! Thanks.

    Like

  2. Hi David! Super helpful post.
    However, I’m running into a problem at the moment where the form that I create in Teams or Sharepoint does not show up in the “recent group forms” tab of Microsoft Forms. So I can access the Excel sheet and Form through Teams and Sharepoint but not through Forms.

    Liked by 1 person

  3. Any suggestions of how to create an Online Excel doc that syncs to an already existing Form (already have 15 responses, so don’t want to create a new Form and have to circulate a new Form link)?

    Liked by 1 person

  4. Any suggestions of how to link an Online Excel doc to an already existing Form (already have circulated a link to a Form and have 15 responses that’ve come through)?

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: