Collect Data into Excel Online | Forms, Surveys, Questionnaires using Office 365


If you are an Office 365 user,
which is what I have pulled up on the screen, I’ve just gone to office.com, and
you’re looking to gather data from users and put it into Excel. We’re going to open
up Excel. As we go through this, you just want to give them a simple user
interface, validate the data before it comes in. There’s a built-in way to do
this. From this workbook, let’s go ahead and change the name. We’re gonna
say survey results. Now we have a blank workbook here. Only one worksheet, it’ss called Sheet1, but we want to insert a Form. If you go into Excel online and go to
the Insert menu and you don’t see the option for Forms, you’re probably not an
Office 365 subscriber. If you want more information on it, just go down into the
description field. I have an affiliate link that will bring you right there so
you can sign up. This is going to start up Microsoft Forms and it’s going
to be linked to that Excel spreadsheet we were looking at just by virtue of
you starting it from that file. You could also start Forms from OneDrive or
SharePoint if you wanted to. But, since it’s going to be linked to this
spreadsheet, I think it’s more intuitive to start it from there. You see it’s
already built out this table. It’s made a new worksheet called Form1 and,
for every respondent, it’s going to record their start time of the survey,
completion time, and email if they’re in your organization, and name automatically. It’s put those fields in. Let’s go over to the Form, and this is what you’re
going to end up sending out. Users are going to fill it out, submit it back, and
the data is going to flow back into the spreadsheet. We’ll go back to
Microsoft Forms. We’re going to change the name of the survey first because
this is just a survey, it’s not the survey results. Those go into Excel. Then the next thing that you want to do is just add a question. There’s
several different question types and we’re going to go over enough of these
for you to get a feel for what they’re trying to do. Let’s say the first thing that you want to do is gather names. If you’re
sending this outside of your organization, you’re not going to have
their names so let’s ask for a first name. You do need to look at these
different options, so a name is going to be a short answer. Don’t worry about this,
but if you did need it, click it and it gives them a bigger box. Let’s act
like you do need their name. If you make this question required, and
I’ll come over and preview it, it’s going to give it a little red asterisk. The
definition of that red asterisk is that it’s required. We’ll click the back
button. Go back to the survey. Some other options, you can restrict what the users
enter into this field, but these are mostly to validate numerical values and
that doesn’t really pertain to what we’re doing right now so let’s leave
those alone. Come back, add another question. You’re going to want to ask
their last name and forms happens to use A.I. here to figure out that that’s
probably what you want. Let’s not bother with middle names, we won’t do “Add all.” Let’s just left click on last name and then left click on “Add selected.” So
you have two fields now. You’re not really going to send this out yet
because you’re not really asking for anything except names. So, the next thing
that we want to ask for, it’s suggested middle name again, but I don’t want that.
We just want a text field because all I want is an email address, but let’s not
require that one because some people might not feel comfortable giving that
out. What that does it it increases the chance of this getting sent back
because someone can just skip that question. Now let’s add another
question and we’re going to ask people how was their service. We’ll act like this is
a customer service survey. We’ll say, “How was our service?” It defaults to a star
system that you can do. You can control the amount of levels and you can control
if it’s stars or numbers. We’ll just leave it as stars from now. I
want to make that require because that’s the whole point of sending this out. So
we have that one done. We’ll see how that flows in the spreadsheet in just a
second. Let’s add a few more questions so you can see some other options. We want
to know when we can contact these people. If they
give us two stars and we want to find out why, we want to have someone get on
the phone, but we’re going to send this out to hundreds of people so let’s just
give them a few options on how to respond. Someone can say “When can we contact you?” We’re going to just give daytime
nighttime and we’re going to add an other option but I want them to click
one of these and only one of these because I want to get most of them back
with one of two options. Then I can have someone do the calls during the day
and someone during the night and we can sort the list when it comes back. So,
leave multiple answers off. We’re not going to require this one because if
someone doesn’t want to fill it out, we still want to contact them. That one’s
done. We’ll just do a few more question types here. There is a date field and
this date field is important. Let’s say, “What day should we call you?” I’m going
to preview this. It makes it easier on the user to input it. When they left click on
this field, they get a calendar date picker where they just left click on the
date. What that prevents is someone typing in February 9th with the th on
the end of the 9, 2019 and it’s not a valid date. The spreadsheet would accept
it, but you can’t sort by that because Excel want to know that that was a date. But, this type of date field will only accept valid dates. That can be really
handy when you’re working with the data. Let’s go back. There’s a couple of other
types here. They’re going to return mostly numeric values and I just wanted
you to see that they’re available. I don’t think that we really need to go
over them because they’re going to behave in a similar way when they get
into the spreadsheet. Let’s act like we’re ready to send this out. First,
we’re going to go to the three dots before the share menu for some settings.
Go down to settings. For this one, let’s act like anyone can respond. What that does is, instead of this survey being behind a username and password
wall, it just makes it accessible to anyone with the link. In a minute here, we’re gonna show you how to distribute that link. Let’s open up
the survey right away, so I have a check mark in Accept responses. You can
schedule one to start and end it. If it is only supposed to last one day, but you might not get to it for a while, you can set an end date on it. You can do your
own little thank you message and you can control some other things as well. This is the meat and potatoes of it. These are the important parts. We’ve
gone through the settings. Let’s left click on share now. We already chose
that anyone with the link can respond. This is the link here, so you can
left-click in it and copy it or you can just use this button, I guess, to make your
life a little bit easier. That’s showing because the link option is
highlighted here. If you had that link, you could email it to someone, you could
send them a Skype message, do whatever you want with it. Here are some
other interesting ways to send this out. You can get a QR code, you can download
this, you could put it on your website for someone to just scan with their
phone, or you could print this out and maybe a hanging on your bulletin board at your
shop to get a survey results. You can also embed it. This would be the code
that you would put on your website. You would embed this in a little window in
your website. If I click preview, this is what the users would see on your page.
It would be surrounded by, perhaps the menu and the image of your website, but
this would just be embedded right there. If you look at the mobile preview,
this is what it would look like mobile. Let’s go back into share and then
there’s the option to just email it. You have to be careful with this option
though because this will launch your default email program. If you’re using
Outlook on your desktop, you’re fine because you left click on this it’ll
just bring up Outlook. It’ll have a little sentence in there with the link
already. But, if you use an email program on the web, maybe you use Gmail, Hotmail just come back here and use Link. Put that in your message and just
write it out. If you wanted to send it through email, you don’t have to use this
button. That’s mostly it for sending it out. I’ve left it on a boring theme,
right? So, we should touch on this quickly. If you want to send it out to users and you
want to make it look not lame, you can select one of these preset options or
you can add your own. Last, I’m going to act like I’m one of the people
that got this survey. Let’s go to preview. We’re going to see what happens
and how it flows into the sheet. Of course my service was great, and call me
during the day, and call me today if I left click on submit it says thanks this
is the response that you could have customized if you wanted to and your
user now is done with this Form. If we go back to Excel, let’s see what that
looked like. We’ll go to survey results and here it is already. It recorded the
time that I did it. It had me as anonymous because I’m not in the same
account. These are the fields that I filled out and it’s nice and easy. I’m
going to put a few more in here and we’re going to circle back in a second.
Okay, I’ve gone back and I’ve put some responses in there so that we can see it.
Let’s go back to the Excel spreadsheet and I wanted to show you
what you can do. If you just had a few options in here nighttime daytime and
you can filter in short by this and have all the daytime people at the top, nightime at the bottom. If you had just left that as an open text field, you would have four
different responses. You could use this to collect hours for payroll from
different employees, right? The survey could be when you start, when you stop.
You send that to a hundred employees and get it back in. It would be timestamped, so you would have some validation to it. If you want to see how to treat that info
when it comes in, I have a video here that you could watch. That is basically all. I hope that was helpful. If you like that, and you want to see
other videos like it, please subscribe in the lower right-hand corner. Thanks!

19 Comments

  1. Is there a way to do a sign up form with specific dates and limit the amount of users that can sign up for a specific date?

  2. Eu tenho 5 formulários já criados e com centenas de respostas, como faço para exibir no Excel online?

  3. As far as I know this functionality requires to have the excel sheet stored in OneDrive for Bussines. Are you using that or just the regular OneDrive that comes with the Office 365 Personal/Home suscriptions?

  4. Great video, thank you! Do you know if it is possible for people to add attachments such as photographs when completing a form?

  5. Hey Adam, awesome video. Quick question, is there a way to generate an email once the form is submitted and have the information from the form sent to specific people?

  6. Is the integration between Microsoft Forms and Microsoft Excel no longer the norm? I am an Office 365 user and have access to Forms, however, it is a completely separate program. It does not appear I am able to access Forms from Excel as you do here in the video. Microsoft support had the same issue and were not very helpful in confirming one way or the other. My main goal is to have the surveys auto-generate the data into Excel as you do here in the video. Currently my only option is to export the survey data manually from the Responses tab. Thank you!

  7. Very helpful! Question, you showed us how to collect info in a form and have it sent to excel. Is there a way to have info then sent to a gmail email in a certain format after rules are applied in excel with the Data collected?

  8. I have already created and shared my form but cannot find the excel sheet online (in other words do it backwards) – I did do it months ago so know that it is doable. Can you please let me know how to view my results sheet online? (in excel in One Drive)

  9. Thanks for this tutorial! Is there way to create a form that sends the responses to an Excel spreadsheet on a server? I work at a school and need to collect student data, but I'm told I can't have the responses collected in the cloud outside our network.

Leave a Reply

Your email address will not be published. Required fields are marked *