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

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

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.

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

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!

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


  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 .


    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.


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


  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


      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!


  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.


  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.


  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!


    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?


      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.


      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!


      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?


      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


      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.


      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?


      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.


  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?


  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.


  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?


  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?


    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.


  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!


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


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


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


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

    Liked by 1 person

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


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


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


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


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


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


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


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


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


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


Leave a Reply

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

You are commenting using your 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

Blog at

Up ↑

%d bloggers like this: