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!

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

Add yours

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

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

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

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

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

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

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

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

Leave a Reply to Airell Cancel reply

Powered by

Up ↑

%d bloggers like this: