How create form in Excel | How to generate form 16 (2021) :

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,

Create modern form in Excel :

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

Data entry in Excel :

Sign into Office.com :

Sign into office.com

     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 drop down,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 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 forum 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. i just need to add one more final question.where i ask people how many cookies they want to,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 then 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 will apply the theme.

If i scroll all the way down.i can 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 is 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 can 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.

Create a new Excel workbook :

Create new blank document

  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 form1 within thi 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 a 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 the different questions that i added to my form now.

Design form :

    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.

Preview form :

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.

Insert form into Excel :

      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.

Set themes :

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 spread sheed 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 oh 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.

View form in 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 toob.i can view all of the responses and here.

Edit form :

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.