Properly Create Surveys with Microsoft Forms & Export to Excel

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
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)
Info
Channel: Leila Gharani
Views: 147,136
Rating: 4.9551401 out of 5
Keywords: XelplusVis, Leila Gharani, Excel online course, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, XelPlus, Microsoft 365, Excel 365, Excel 2016, excel online, Microsoft Forms, microsoft forms export to excel, Microsoft forms export data, microsoft forms download, microsoft forms examples, microsoft forms automatic export to excel, microsoft forms tutorial, microsoft forms star rating, microsoft forms free, microsoft forms dropdown, excel data entry form
Id: nXeYv-W-Wt4
Channel Id: undefined
Length: 15min 29sec (929 seconds)
Published: Thu Oct 22 2020
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.