SUPER EASY Excel Data Entry Form NO VBA

Author:

Leila Gharani

Keywords:

XelplusVis,Excel Tutorials,Leila Gharani,Excel 2016,Excel 2013,Excel 2010,Advanced Excel tricks,Excel online course,Excel tips and tricks,Excel for analysts,how to create data entry form in excel,data entry from without vba,data entry,excel data entry form,excel data entry form with data validation,excel data entry form for table,excel data entry form button,excel hidden features,excel hidden tips

Subtitles:
Today we're going to take a look at creating data entry forms in Excel. There's going to be no VBA, it's going to be super easy. (groovy beat music) So let's say for one reason or another, you have to input some data manually in Excel. And you want to make the experience as pleasant as possible. You can use data forms to do that. It's especially good if your tables are really wide and you want to avoid horizontal scrolling. Now, just to keep things simple, in this example, I have a small table where I want to input data in category, task, date, person, and status. One way of entering data is just to input it here, right? I'll put in site, task, let's say health check, date, person, James. Now if I wanted to edit something, I have to go back to that cell and then click, go to edit mode and then expand that task. An alternate way of doing this is to use a form. But there is one prerequisite. And that is that you have to turn your data into an official Excel table. But it's really easy to do that. All you have to do is click anywhere inside that data set and then press control + T. We're just going to go with the default and click on OK. Now it applies the standard or default table formatting so I'm just going to go up here to table styles and take away that formatting. So now that my data set is turned into an official Excel table, I can use a form. But where is the button for the form? The thing is, it's not in the ribbon. To be able to use it, I can add it to my quick access tool bar. So just click on this down arrow, go to more commands, it's not a popular command, it's a command not in a ribbon or all commands, so you can get to it both ways, I'll just go to commands not in the ribbon, and scroll down to F, I think I just saw it, form, right here, and then click on add to get it on this side, and then click on OK, right? So now I see it here. Now all I have to do is click on it and my form appears. Here's the thing, though. If you're outside the table and you click on this, it tells you this can't be applied to the selected range. So it really depends where your active cell is. Make sure that you're somewhere inside your table and then click on this to activate the form, right? So I can see my first line of data. To add something new, I click on new. Let's add course here, task, date, person, and status, let's say started. And to add this to the table, you just have to press enter. You can see it right here. It automatically takes you to the next one so you can just go and add person, James, press enter, it's right there. The good thing is that you can scroll through the table right here, you can go and edit something, and then press enter to add it to the record there. You can also search for something. So, notice here, we have criteria. So when I click on this, I can actually search for any of these categories. So, for person, let's type James and then either click on find next or find previous. So here I can see one record for James is SEO and another one is health check So to toggle between that criteria and the form, you have to click this button here. Now let's see if you change something by mistake, so I put 30 here and then I say, Oh no, that was a mistake. I just want to go back to what I had. I can click on restore and it puts that record back So as long as you haven't pressed enter and sent it there, you can restore it. Now the other good thing about data entry forms is that you can also add data validation to this. So the moment you activate data validation on any of these categories here it also applies to your form. So let's say for these dates, I want to make sure that people input the right date. Go to data, data validation, instead of any value, select date. For start date, I'll pick 1/1/2019 and my end date is 1/1/2020. So I want my dates to be between these two. I'm going to add an error alert as well. Now, let's go back to our form and let's enter a new record. What if I input the wrong date here? So let's go with 2/2/2030 and I press enter. It puts it here, but notice it says, Please input date between this. And when I click on retry, it takes it away, So now I can go update this, let's say 2019 and when I press enter, it adds it there. But let's add the rest to this, press enter, and the other records are in there as well And just close the form. Now, you can also use forms on your existing tables that you have. And you can use it to look for stuff. So let's say this is a bigger table I have on this other tab and I want to look for something. So I'm going to click on the form here to bring this up and click on criteria. And I want to look for values that have revenue greater than 500 So the good thing about forms is that you can use the greater than, less than sign, you can also use wildcards here. So you can use the asterisk sign as a wildcard. So when I click on find next, then I see this record, beverage is Coke, has revenue greater than that, then it's 540-512 and so on. Okay, so that's how you can use data forms on your existing data sets to either input data easily or to easily look for stuff. If you like this video, give it a thumbs up. And if you want to become better in Excel, if you want to improve your Excel knowledge, consider subscribing to this channel. (upbeat music)

Loading