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!

49 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

  5. how does the first original sheet gets updated , the I can see the forms sheet get the values I entered but you didn’t mention how they are both linked .

    Like

    1. They are linked by Office 365 magic. There is no actual insight into where/how they’re connected. When you follow these steps O365 makes the link. Unsure how to change it or view any more than this.

      Like

  6. Hi there! Can this work offline, i.e., no need to send data to some cloud based storage for it to get pushed to Excel? I’m looking to collect data from different people on a form displayed on the same physical offline tablet, not online. Then the data just gets pushed to the same excel sheet in the background without any risk of privacy breach (almost inevitable with free online apps) or data entry error (as with paper forms etc).

    Like

  7. Hello!

    We have a GDPR problem. After loading the responses to a Sharepoint list we would like to delete Forms responses, not just from the excel, but also from Forms too with Microsoft Flow or PowerApps. Have you any suggestion to solute this problem?
    Thanks you very much

    Like

      1. Hey,

        I don’t know if you manage to get an answer for this but you can remove responses from the form.

        In order to do it you need to open the responses tab, click on “view results” and from there you have to click on the 3 dots on the right side and then “delete”.

        It has to be done with every single answer.

        Hope it helps!

        Like

  8. Hi David,

    I’m trying to create a form that links to an excel that is stored in a sharepoint shared site. Have you had any experience with this? I can’t find anything else online. When you create an excel in sharepoint there is no form option, and if you create it in your onedrive and then move the excel linked to the form to sharepoint the link appears to get broken – new entries in the form no longer updates the excel.

    Like

  9. Hi David,

    I’m not sure if this was already addressed, but are you aware of a way to add more than one form to an excel workbook? I would either want to combine two form responses into one workbook, or create a new form form the existing responses file.

    I used to do this with Google Forms for a major project that gets refreshed yearly. My company would prefer we use Microsoft this year, and I am not sure how to combine multiple forms into one workbook, which would be an essential function.

    Like

  10. Hello David, This is incredible! BUT I don’t have any of those options. I work for a university and it is driving me nuts that Office did not have this feature, as Google forms synced with sheets seven years ago, but then your article was a ray of sunlight into my dark and dreary existence…. Then when I went to try it, I neither have the “new” option in my SharePoint site nor do I have the form items in my ribbon within office 365. I have talked to my IT department and they say they have the most recent updates. Do you know if this is build dependent? is it possible that we have a version of 365 that does not allow this feature?

    Thank you for your time!

    Like

    1. I’m sorry, I just read another post where you said this does not work in shared sites…. thank you for the heads up! I think I can still make this work! No need to reply to my other post.

      Liked by 1 person

  11. Hi David,

    I’m on SharePoint and I’ve already created a form.

    Is there a way to have Form responses automagically record a new row into an Excel document on Sharepoint, instead of having to click “Open in Excel” each time?

    Like

      1. Yes. My issue is that the Forms option does not appear in the “+ New” drop down menu within the SharePoint site.

        Also, the Forms option is not shown in the Tables section of my Home ribbon on Excel Online.

        I had to create the Form under my personal SharePoint app, copy the link, and then add it to my group’s SharePoint by selecting “link” under the “New” drop down menu.

        Like

      2. It won’t appear in the + New for the site, but should within a library.

        I think it only appears in Excel when the Excel file is stored in library that can support it.

        Once it’s created, I don’t think there’s a way to get it to link back. You could create a Flow to copy the survey results into Excel!

        Like

      3. I’m sorry I’m pretty new to SharePoint. Where can I find my site’s library? How can I make the library support this capability?

        Like

      4. Simply navigate to Documents, normally in the navigation of the site. If not, click the Gear/Cog in the top right, then Site Contents. You should see Documents or Shared Documents there. If still not, then you may not have one created. Go back home, click the “+New” menu and add a Document Library. LMK how it goes

        Like

      5. I just can’t seem to get it to work. I’ve spent hours trying to figure this out. Not sure how to enable the Forms option across the entire site or if it’s even possible.

        The Forms shows up as an option in my personal SharePoint, but not in my department’s site.

        I guess I’ll just have to upload the data manually each time.

        Like

      6. Hey David,

        Do you have any good resources to help beginners with creating a Flow to copy Form submission results automatically into Excel on SharePoint?

        Like

      7. Not off the top of my head, some googling should help, and I have some more content, under the O365 menu, for Flow. However, no introduction/walk through type content.

        Like

  12. hi, i created a sharepoint form linked to an excel file using the method you have suggested, by clicking on Form for Excel. I tried to move the excel file to another folder in Sharepoint but i realised that the link no longer works. So if someone fills up the form, it no longer links and updates to that excel file. :( would you know how to move and relink the form to the excel?

    Like

  13. OK, I have read this blog, and its very interesting, and I am struggling to get my Flow to add all the form info to my excel spreadsheet. Even had Microsoft on the phone, and I am unsure they have people that really understand or just read from a script, at a loss and need assistance please.

    Like

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

    Like

  15. 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?

    Like

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

      Like

  16. 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!

    Like

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 )

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: