Hi everyone, Kevin here. Today, I want to show you how you can create a modern Excel data entry form. This is extremely easy to do and it doesn’t require any VBA at all. Once you create your form, you can send out a link and whether someone uses a desktop or a mobile device, they can fill out your form and it’ll adapt to whatever screen size they happen to be on. Once they fill out the form, it’ll automatically add their responses to your Excel spreadsheet and there’s no risk that they’re going to mess up your Excel spreadsheet because the form and the spreadsheet are separate. Also, when someone fills out your form, you can validate the data that they’re entering. Let’s say maybe I have a cookie order form and I want to know how many cookies do you want to order? I can require that someone enters in a number. This is far and away the best way to have people enter data into Microsoft Excel and I’ll show you step by step how you can pull this off. All right, let’s check it out. To create a modern Excel data entry form, let’s head to the website office.com and one caveat before we jump into this, you need a work or school account to use this and unfortunately, this does not work with personal Microsoft accounts. Even if you’re a Microsoft 365 subscriber, unfortunately, if you have a personal account, this won’t work. On office.com, click on sign in and then log in with your credentials. Once you sign in to office.com, over on the left-hand side, let’s click on the Excel icon. We’re going to use Excel on the web to set this up. However, once it’s set up, you can also use Excel desktop. On the Excel start page in the top left-hand corner, let’s click on new blank workbook. This drops us into a new workbook and today I want to create a cookie order form for the Kevin Cookie Company. To do that, up on the top bar, click on insert and on the insert ribbon over on the left-hand side, there’s the option to insert a form. When you click on this dropdown, let’s click on new form. This drops us into Microsoft Forms. If you’ve never used Microsoft Forms before, you can create things like surveys and quizzes and there’s lots of rich functionality. If you want a full overview of Microsoft Forms and what all is possible with Forms, I’ve included a link to a video in the description and this form is now connected to our Excel spreadsheet. I mentioned that I want to create a cookie order form. So, let’s get started. Up above, here I can see the title of my form. I’ll click up here and let me type in cookie order form. Next, I want to start adding some questions. So, I’ll click on add new. Down below, I see some suggested questions and for an order form, all of these make sense. I need to know the first name, the last name, the mailing address. Let’s put down the city and the state. Here, I’ll click on add selected. And look at that, my form is starting to come together, and it couldn’t have been any easier. Of course, this is a cookie order form and I need to know what types of cookies do people want to order. Down below, once again, I’ll click on add new and this time, this is going to be a choice question but I also have all these other types of questions that I can insert. Here, I’ll click on choice and for this one, I’m going to ask what cookie type. And look at this. Once again, I get another set of suggested options. And what do you know? These are all the types of cookies that we carry at the Kevin Cookie Company. So here, I’ll click on add all and this question is also done now. Now, I just need to add one more final question where I ask people how many cookies they want to order. So once again, I’ll click on add new. For this last question, I’ll select text and for the question itself, I’ll simply say quantity. Down below, I have a few different options for this question. Here, I can make it required. And when I click on the ellipsis, I have the option to turn on restrictions. This is also known as data validation. And right here, I have several different options that I can select. Now, we have some pretty ambitious quotas at the Kevin Cookie Company for cookie sales. So let me select greater than and let’s say that people have to order at least 50 cookies. Maybe this is a way to encourage some more sales. I’m now all done building my form and I’m getting ready to distribute it. But before I do that, I should probably preview what it looks like. Up on top, I can click on this preview and here I could view what it looks like on a desktop PC. It looks pretty good to me. Right up on top as well, I also see an estimate for how long it’ll take to fill out this survey. So, it looks like about four minutes. Also, to the right of computer, I can also preview what it looks like on a mobile device. So, this truly is a modern way of pulling together a form. It’ll adapt to whatever device your respondent is on. Let’s click on back. Back on the main form screen, over on the right-hand side, I could also set a theme for my form. Here I could simply click on this and that’ll apply the theme. If I scroll all the way down, I could also create my own custom theme, but this one looks pretty good, so I’ll stick with this. Now imagine trying to do all of this in Microsoft Excel. This truly is a better way to pull together a form. Up in the top right-hand corner, there’s also an ellipsis. And when I click on that, I can access various settings. Here, for example, I can decide who can fill out this form. Is it just limited to people in my organization or can anyone respond? Here I’ll select anyone can respond. Down below, I could also decide whether I’m currently accepting responses or maybe the survey’s all done. So, let’s say I want to turn that off. And I have a few other options here. And another interesting one, at the very bottom, I could set it, so I receive an email anytime someone fills out my form. Once again, try doing this using a legacy form in Microsoft Excel. Now that we’ve looked at all of the different settings, how do we actually share out this form so people can start filling it out? Well, right up in the top right-hand corner, there’s a very prominent share button. And here, when I click on that, here I could get a URL for this form. I could also get a QR code. Here I can embed it into a website, and I could even email this out to others. Now that we know how to share out the form, let’s jump back into Microsoft Excel. And up on the top bar in my browser, I still have the Excel workbook open. Let’s click back into that tab. This drops me back into Microsoft Excel. And at the very bottom, I can see that it automatically added a new worksheet to my workbook called Form 1. Within this worksheet, it added a new table. And here I see a whole bunch of different columns. Right over here, the first five columns are added by default by forms. Here I see an ID, a start time, a completion time, an email address, and the name. If someone filling out the form is in my organization, it’ll automatically provide the email address and the name. However, if someone outside of my organization fills this out, I won’t see any values here. It’ll simply say anonymous. To the right of these default columns, here I see all of the different questions that I added to my form. Now let’s say maybe I want to go back and I want to add some additional questions, or maybe I want to modify some of these questions. Well, the good thing is I can very easily do that. Right up on the top tabs again, I’ll select Insert, and here I see Forms. When I click on this, here again, I can preview my form, and I can also go back and edit my form. When I click on Edit, this drops me within Forms again, and here I can go through, add additional questions, or I could change some of the questions. Back within Microsoft Excel under Forms, here too, I could also send out the form to others so people can start filling it out, or I could simply delete the form altogether. Now that I’ve created my form and I’ve set up my spreadsheet, let’s say maybe I prefer working in Excel desktop. Right up on top, I can also open this spreadsheet in the desktop app. I’ll click on this, and check that out. Now I have my form in Excel desktop, and I think it’s now time for people to start ordering some cookies. So let’s order a cookie and see how quickly it shows up in this spreadsheet. Here I have my order form opened up side by side with my Excel spreadsheet, and I’m ordering a lot of cookies. Let’s click on Submit and then see how long it takes before it shows up in my spreadsheet, and check that out. The results just came in. Here we have our first cookie order, and wow, 800 cookies. We better get to work in the kitchen. The nice thing about using forms together with Excel, let’s say that you have charts, or maybe you have some pivot tables, and you’ve done some analysis on all these different form submissions. You can simply update your charts. You can simply update your pivot tables. The form is connected directly to Excel, so it makes analysis extremely easy. To get back to your spreadsheet, you can go through Microsoft Excel and access your sheet from there. However, you can also go directly through Microsoft Forms. Here I see my cookie order form, and when I click on this, once again, I can see my form, but there’s another tab here for Responses. And when I click on this, here too, I can view all of the responses. And here I have a button that says Open in Excel, and there’s a cloud icon on there. When I click on this, this opens up my Excel workbook, and here too, I can see all of the form submissions. All right, well, let me know down below, were you able to create your own Excel data entry form? If you enjoyed this video and you’d like to see future ones, please consider subscribing. I’ll see you next time.