Creating Easy Data Entry Forms in Excel

in this tutorial I will show you an easy way to create data entry forms in Excel and these forms make it so much easier to enter records or data into Excel you can see here on the screen I have a pretty simple spreadsheet this is an employee list with names hired a its employee numbers and departments let's say that a business has decided that they want to have an employee sheet and this is just the beginning of setting up that employee list now imagine how time consuming and painful it would be to enter all of this data for let's say 200 employees yes you can do it you can just click on the appropriate cell you can type tab over type but doing it this way can lead to more mistakes and so let's look at how to set up an easy data entry form in Excel the first step that I'm gonna have to take is to go up here to the quick access bar this is a handy bar where you can add additional tools and features that you want to be able to access easily and quickly that's why it's called the quick access toolbar and if you haven't already watched my previous tutorial on how to customize the quick access toolbar please do so but for the purposes of this video I'm just gonna click here on this button this is the customize quick access toolbar button when you click that it gives you a list of some of the items that you can add to the quick access toolbar or take off the quick access toolbar but if you look toward the bottom of this pop up menu there's an option for more commands and I'm gonna click that it takes me to a list of popular Excel commands but in this case what we're looking for is not necessarily popular so I'm gonna go down to either all commands or commands not in ribbon I'll just pick commands not in ribbon and what I'm looking for is form that's what I'm trying to create a quick easy data entry form so I'm gonna browse down look for form there it is I click on it and if you'll notice here on the right side there are some items already in this list compare those to what's up above here on my quick access toolbar it's the same things the same features are listed here as are here in the upper left so I want to add form to that list having selected form I just click add it's now added to this list and when I click on OK it will now be added to my quick access toolbar so there it is now that I have that button readily accessible to me I will be able to easily create a data entry form for my excel spreadsheet all right the next thing we need to do is take this data that I have here in my spreadsheet and turn it into a table for some reason that's a prerequisite of using these quick forms so how to turn this into a table all I have to do is click somewhere inside the data how about here and then here on the Home tab home ribbon in the Styles group there's a button that says format as table now there is a faster way to do it once you've clicked anywhere in the data you can just hold the ctrl key and tap T for table and that will bring up this pop-up and here Excel has tried to guess what my data consists of and in 99% of the cases it does it correctly you'll notice that there's a dashed line that's kind of moving across the top here and on the left and on the right that is outlining the shape of the table and in most cases it gets it perfectly correct if not you may need to change some of the numbers here you may need to click this and then outline what you want to be included or you might need to check or uncheck my table has headers I'm just gonna click OK let's see if it works it looks like it did I can tell that Excel has identified these items here as headers so I think that worked just fine and now let's move on to the next step at this point I just want to have clicked somewhere in the table so I'll I'll just make sure that I've done that I click and now I go up here to the quick access toolbar and click the form button now notice what it did it has identified 22 Records or rows in this table it doesn't count the header row it just skips it but you'll notice that record number 1 is Gina Pollan here at the left but all of the data and information related to Gina Pollan is listed vertically in this case instead of horizontally now there are a couple of reasons why this is helpful one of them is let's say you have a spreadsheet with like 14 columns or 15 columns you wouldn't want to have to click this bar and drag to the right and type in the data or to tab over up to 14 times in order to enter in your data it's kind of cumbersome to do it that way and so that's why you might want to do what I'm showing here by creating an easy data entry form now if I click find next look what it does it goes to the next record marcelina Restrepo click find next again Rajini aloft as' and so forth and it's numbering those records here at the top right so I can click through those now let's say it's time to resume entering data for the employees of this company I can click new notice it says new record I just click and next to name I'll type the next employees name if I tap tab it does move to the next data entry cell hire date now you can just type that in the way you typically would maybe with the day than the month in the year or you can hold the ctrl key and tap semicolon and that puts today's date into the form in this case Gary Larson was not hired today so I will go ahead and change that to a different date put in the employee number and I can either click to the next cell in this entry form or I can tab just like I did before put in the department and at this point I can tap enter on the keyboard it inserts the record into my spreadsheet and gives me a new blank form if I close and browse down the page you can see there's Gary Larson hire date employee number and Department jumping back into the form is super easy I just go up here on the quick access toolbar click form I'm right back in there I want to enter a new employee and I simply resume so if you do a lot of data entry you're gonna find yourself really preferring these easy data entry forms that you can create and use in Excel and it's really easy to do while we're here in this form tool I want to point out some of the other features and buttons that we have so we've looked already at new notice that there's also delete so this is a mistake obviously I don't know how I made that one but I can just click delete displayed record will be deleted that's exactly what I want so I click okay and it's gone we also have a find previous button so that goes from number 25 to number 24 to record number 23 et cetera you can also find next that advances to higher numbers and then we have a criteria button and a restore button let's look at the restore button so i'm gonna click find previous a few times here we have a person named Eileen Osterberg and let's say that the company is considering changing her department from sales to customer service so I can just type that in but what if I realized that that was a mistake I can just click restore and it goes right back to the way it was before I started changing the data in this form so as long as you don't click close or tap enter on the keyboard everything that you type here is easy to undo just by clicking the restore button ok one button left to look at besides clothes and that is criteria the way criteria works is you click it and then you enter criteria into this form and then Excel will see if it can find one or more of the records here in the spreadsheet that matched the criteria so for example I would really like to find someone who has a name that includes the word Bob and the hire date doesn't really matter the employee number doesn't really matter but the department should be communications all right so I have Bob and communications at this point all I have to do is tap enter on the keyboard and look I found Bob in communications there is now you may at first think that this number here in the upper right indicates that there are 25 or 18 Bob's in communication but if you click next you'll notice that's really all there is just one Bob in communications so what if I want to see everyone in communications not just Bob to do that I'm gonna close the form open it back up and go to criteria put in communications tap enter on the keyboard and now look it found Virginia Loftus if I click next palma new Meister and there is good old Bob again so I hope that you see how useful an Excel data entry form can be you can take your data entry tasks that might be too horizontal and it can make it vertical and notice that you can scroll so it could be 10 15 20 different bits of data that you're entering in and it makes them vertical not horizontal and it gives you these tools at the right to also help you to search within the table that you've created thanks for watching I hope you found this tutorial to be helpful if you did please click the like button below and consider connecting with me on my social media accounts like Facebook Pinterest and Twitter and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe click the bell next to the subscribe button that way you'll be notified whenever I post another video and watch for another video from me at least every Monday if you'd like to support my channel consider becoming a supporter of mine through my patreon account and you'll see a link to that in the description below

Channel: Technology for Teachers and Students

Views: 984,709

Rating: 4.9458938 out of 5

Keywords: excel data forms, excel data entry forms, creating data entry forms, creating excel data entry forms, how to create excel forms, how to create excel data entry forms, exel data entry forms, ecxel forms, doing excel data entry, exel data entry, excel data entry, creating excel entry forms for dummies, creating excel forms, data entry in excel, data entry in exel

Id: 5soiJ5rsqKc

Length: 9min 51sec (591 seconds)

Published: Mon Jan 27 2020