Hi, this is Wayne again with a topic “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 gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 .