Did you ever need to collect
data from different people, perhaps for a survey to get
some feedback on your project, maybe get feedback from students or offer an online form for data entry? With Microsoft Forms, you
can easily collect data in a fillable form that
you can control from Excel. You can analyze the data in Excel as well and share the results with your audience. By the end of this video,
you're going to be able to create your own
fillable data entry forms that can include
drop-downs, multiple choice, calendar, date selection, number checks and even star ratings. No programming, no VBA, no add-ons just a Microsoft 365 account. (upbeat music) Before we get started,
a brief thanks to Grid for sponsoring today's video. Grid empowers you to
instantly turn any spreadsheet into a visually compelling
interactive web document that can be securely shared online. I'm going to be chatting more
about them towards the end so stay tuned for that. Now let's jump in and let me show you how you can set up your first form. Fo create your data collection form, just go to office.com. If you aren't logged in, login now and then click on the app launcher. This is going to show
you the list of the apps that are available to you. Now here's the thing, you can create your data
collection form or your survey by clicking on a Form right
here or by going to Excel here. Now, this is an important decision to make and it depends on how you
answer the following questions. Question one, do you have a
free or paid Office 365 account? If you have a free account,
your entry point is here. So you need to go directly to Forms. If you have an Office 365 account you get to answer a question
two before you decide and question two is, do you
want an automatic connection between Excel and your Forms results or do you want to download
your answers to Excel whenever you need them? If you want an automatic connection, which I think most of you will, then your entry point is Excel. This is Excel Online and
not Excel on desktop. Now I'm going to start off
by going to Excel Online but even if you aren't a 365 customer, stay with me because we're going
to end up in the same place. Whether I go through here
or I go through here, the steps you need to
do to create the form are going to be the same regardless of which entry point we take. So I'm going to go to Excel Online
and create a blank workbook. Now, first thing I want
to do is give this a name. Next thing is to insert a form. If we do it from Excel, we're going to connect the forms results to this Excel file right here. So let's go to Forms, select New Form. Now we can add our questions, we can see the name of the Form here is the same as the name of our file. Now, if you went directly to Forms, let me just show you how that looks. So if I come here now, you
can see the current forms you have in place, if you have any. This is my special survey one, the one that I created from Excel Online. To create a new Form if you're
not going through Excel, you just have to click
on New Form right here. Now in this case, I'm just going
to go back to my existing Form and show you how you can enter
different types of questions. To add your first
question, click-on Add new. Here you get to decide what type of question you want to add? You have more options, if
you click on this dropdown, you can, for example, at likert scale, you can even ask people to upload a file. If you select File upload, it's going to create a new
folder in your SharePoint drive and people can upload their
files to that SharePoint drive. As a first, I'm going to select Rating. We can type in the question, you can add the number
of stars that you want or you can switch this to a
number instead of the stars. I'll just leave it on stars. You can also make a
question to be mandatory and not optional. So if I turned this on, this
question is now mandatory. Next, let's add a new question this time, I want it to be a choice. Let's put, select your gender. Now the moment I type this in, it's smart it's already giving me a
list of suggested options I don't have to type them in. I can select individually
an option from here or I can add them all and it's going to create them
all automatically for me. If you want to add more options,
you can click on the plus. If you want to delete an option, you can just delete it from here. As the next question,
let's add a drop-down. The drop-down is also from
the choice option here. So I'm going to ask, where
are you watching from now? Now I want to have the list
of different countries here or at least the top countries that people are currently
checking my YouTube channel from. I don't want to input each one
individually manually in here because that's going
to be really annoying, I want to copy and paste
it where you can do that. So I have the list of countries in Excel, highlight everything, copy it and I'm going to go to the first box here and I'm going to do Control + V and it's super smart because
it paste each individual cell in a separate box here. I also want to add another
option, added it right here, I'm going to delete that one. Now I want it to be a dropdown. Here's where you can transform
into a dropdown click on more settings for this
question and select Dropdown. I'm going to leave this
on Required as well. Now let's take a look
at the date choice here. So let's select Date for this question and my question is, what date are you looking forward to you? So you get to select a date
from this date pick right here. Now this, I'm going to set to optional. Next, I want to add a text box so that you can input
what the occasion is. I can activate long answer
or leave it as short. If I was collecting numbers,
I can activate restrictions and pick one from the dropdown. In this case, I don't need it so I'm just going to turn
it off, just uncheck it. What a nice question,
let's add a likert scale. My question is, how often do
you use these applications? Now, I already get some suggestions here so I'm just going to add them all so I don't have to type the
different options in here. Now for application, we're
going to go with Excel of course then let's add Google Sheets,
PowerPoint and OneNote. Now my last question
is this one right here the net promoter score. And here I want to ask, how do you rate your current Excel skills? Not at all likely doesn't apply so I'm going to say not so
great and extremely great. Okay, so that's the set of our questions. As you can see we have a lot of options. At any point in time,
you want to preview this click on Preview and you can
see how it looks on a computer or how it would look on
someone's smartphone. Now let's go back to our original survey and add theme to this. So pick a color that you want from here add your own image that you want or customize the color if you need to or just select from one of
the options we have here. Once you're done, you
are ready to share this with either the people
in your organization or if you want to make
this publicly available to anyone outside your organization, you can go with anyone with the link. You can copy this link
and share it with others, you can send them an email embedded in a website if you want or you can download the QR Code so you can add it to your
PowerPoint presentation. Now, if you have your phone with you, bring up your phone, scan
this, go to the survey, answered the questions so that
I can get to know you better. If you want to control
how you get notified when people are answering this, click on more options here on
the side and select Settings. You can turn off Accept responses if you don't want people to
fill up this form anymore, you can enter an end date, you can activate shuffling questions and get email notifications
for each response if you want. Okay, so that's the form, it's been set up now, all we need is to wait for responses and then we can analyze these together. (clock ticking) Now I have added in some
responses with my phone but I also want to show
you how the form looks from the point of view of
someone who's completing it on the browsers. So I'm just going to paste
in the link of the form, that's our special survey. How do you rate your mood right now? We get the ability to
pick the number of stars. I'm going to go with five, select your gender, that's easy. Where are you watching from? So we get to pick from
this list that I put in sadly, Austria, where I am
is not on my top countries where people are watching
my YouTube channel. So I'm just going to type it in manually. What data are you looking forward to? This gives us the calendar
where we can select our dates. So I'm going to go with the ninth. What's the occasion? It's Friday. How often do you use this applications? We can make our selection
really easily here. How do you rate your current Excel skills? Well, I'm going to go
with a seven and submit. Then I get your response was submitted and I have the ability to
submit another response. How can we view the responses? So let's bring back my
form where I'm logged in, let's refresh this an we
should see a seven right here I'd added six before. So we get to quickly view the responses by clicking under responses tab here. We can view the overview of our responses just by scrolling down
and we get the ability to view each separate response
by clicking on View results. Here, we can scroll through
each single response then I'm going to go back and of course if you want this in Excel, you can open this in Excel. Now remember, this was
a form that we created from Excel Online, that's why
we get the cloud icon here. The Form results are
automatically connected to our Excel Online file. When I opened this in Excel,
it's going to open Excel Online and we can view our responses right here. So the first few columns
are automatically generated, we get an automatic ID, the start time, the person filled in the
form, when they completed it. Then email and name, these are anonymous if you're sharing it with the public, if you share it with
people in your organization and they're logged in to their accounts, you get their email and name as well. Then we have the answers to
the questions we asked for. It's all neatly organized
in an Excel table so that you can do
further analysis on this. And all of this is live. So I'm going to show you, I'm
just going to bring up my phone and let's quickly submit another response. So this time I'm going to
add two stars for my mood, woman as gender and the
country I'm watching from I'm going to go with Singapore. Okay, the rest were optional
so let me just submit this and I should see it
pop up here and it did. Okay, so that's the advantage
of starting from Excel Online you get an automatic
connection to your Form. Now, if your starting
point was Forms directly, you're not going to see
the automatic connection. So you're not going to see that cloud. So here's another one I created before and I created this one
directly using the Forms not from Excel Online. Here, I can download
the responses to Excel. When I click on this, it's
going to create a new Excel file and download it my drive. It's not going to be
an automatic connection like we had with Excel Online. Okay, so if you have a
Microsoft 365 account, I suggest you start from Excel Online so you have an automatic
interface to your Form. As you can see, it's really
easy to create a data entry or data collection form
directly from Excel Online and analyze the responses
in Excel in real time. Now, I'm a fan of seamless
interfaces and automation which brings me to today's sponsor. If you find yourself
frequently reporting on data you've pulled together or sharing models you've built in Excel, Grid
may be a solution for you. Just bring your spreadsheet, select the data you want to display and how you want to display it, write your narrative
and publish immediately. All other parts of the workbook
remain hidden from view. In addition, Grid documents are linked to the underlying spreadsheet. So any changes you make to the data is going to be instantly
reflected in the document. No more tedious copy
paste of charts and tables from Excel to update PowerPoints and PDFs with the latest numbers, just a seamless flow
from the updated workbook to a beautiful mobile friendly document, where you remain in
control over the narrative and the distribution. You can even give your
audience a way to interact with your models without them
being able to change the model or even see your formulas. Unlike so many data solutions, Grid leverages your
everyday spreadsheet skills so you don't have to be an advanced data or technology person to make
your spreadsheets come alive. Grid is a brand new offering, but you can sign up for early access to give this innovative
solution a spin on grid.is that's g-r-i-d.i-s. Thank you for watching, I
hope you enjoyed this video. If you did, as usual, don't
forget to give it a thumbs up and if you haven't subscribed
yet to this channel, consider subscribing and I'm going to see
you in the next video. (gentle music)