SUPER EASY Excel Data Entry Form (NO VBA)
- Articles, Blog

SUPER EASY Excel Data Entry Form (NO VBA)


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)

About Bill McCormick

Read All Posts By Bill McCormick

100 thoughts on “SUPER EASY Excel Data Entry Form (NO VBA)

  1. This is awesome , thanks so much!
    But I learn that it has a limit, my dataset has 50 columns and
    when i try to activate the form it tells me that there is too many fields 🙁

  2. Hi Leila,
    Is it possible to create data entry when you have all your heading in one column (and separate rows) as oppose to what you did by having all your headings in one row but separate columns?

  3. This is sick Leila. Do you have a course or tutorial wherein these data goes to multiple or different PDF Forms? If yes tell please tell me and I will enroll. Thanks

  4. I'd created a presentation/tutorial in MS PowerPoint, run it in a terminal mode, where users navigate through vba coded buttons. Now, I'm figuring a way to capture who views the PowerPoint (also, trying to capture their feedback). Could anyone please help with a vba code that could be tied to a userform in the beginning or the end of the presentation that would allow users to fill and submit the feedback/registrations? Could an embedded Excel w/VBA help with this?

  5. Just when I thought I was a better than the average Excel user I randomly come across this super easy and useful tip that could have been so helpful during all those meetings where my spreadsheet was projected on a large screen and I totally feel like a dumb dumb now.

    Thank you for the clear and helpful Excel vids, this is the 1st one I’ve seen but I’m gonna check out more!

    Keep up the great work, I know I appreciate it!

  6. I have a couple of spreadsheets that uitilize this form but recently have broken. When i run the VBA to open the form (for trouble shooting purposes) i get an error stating that the object has disconnected from the client. And the only way i can get the form to actually work is to convert the table into a range. The other strange thing is that both my files that utilize this form broke at the same time despite only one of them being actually in use. Any thoughts on whats causing this issue?

  7. I've been working with Excel for about 10 years and learned this TODAY. Amazing tip! 🙂

    Others have asked, but is there a way to have a drop-down in a field instead of free text input? I know data validation is possible, but it'd be nice for users to simply pick from a drop-down list.

  8. I can't find the FORM command on my Excel 365 although i remember using it on older version in windows. Has this function been removed from later versions?

  9. That was a lot easier than I thought. Thank you! You could add the command to the ribbon instead of the quick access toolbar with just one more step. Worth it if it's something you would use regularly.

  10. For Mac users running Excel 2019, save yourself some time as this feature is not available in the latest release. Yay! JK. Bummer.

  11. A nice tutorial, but I don't see the advantage of using a form versus just pressing tab for the next column when in a table? Basically I'd like to understand more why this feature is something that is objectively better for practical use.

    The criteria is cooler, because of the ability to search multiple fields, but for the data entry…I don't get it why people think it's 'professional' or good.

  12. Please can you help to design a student record sheet that contain admission number, names, parents contact, monthly payment(12 months), owing, and easy to search. thank you

  13. Leila, you are great as always. However, what the exact point of forms, when it's easier to edit/add directly in the table. I find this feature a bit useless and even less efficient. but would be glad to be convinced opposite.

  14. can you save these forms ?

    Ps , ive been struggling with subtotal entry …. it keeps giving me total of each row and subtotal at very top with no defining text ..
    how to do this …? ( before i crack up , msoft help is not helping ) ….. thanks..

  15. What if a column on the table contains formula, will the form allow you to chage the data or will it be not editable? Thanks.

  16. You are simply awesome. All this while, 10 years, working with Excel – i used VBA to create a data entry form. I had no idea this option was available already. You are great Leila.

  17. Thank you very much. after dizzing with my excel sheet because of looking the same tutorial like this but my microsoft office is different level (lower level) than the tutor's. (i can't find the command of "what you want to do") And when i ask the tutor, he never gives any explanation. Once thank you. Best regart from indonesia. 🙏🙏👍👍👍

  18. The keyboard shortcut is Alt+D+O when you’re clicked on a table. However, there seems to be a limit on power query tables. 🙂

  19. Thi is nice.. But i have a question, can we make this more complex? For example : I have 3 column (Date, ID Product, Serial Number). I want to input 1 Date and IP Product, with 5 serial number with one create.

  20. Does she deserve thumbs down? I don't understand why some people are so evil. We love her presentations and appreciate all the work she has done for us.

  21. Miss , will you please me about Circular reference,
    Another things, when i wanna insert a new row in my excel sheet all formula showing =REF

  22. This is awesome! What do you do if you don’t want all the columns to be filled with forms?
    A + B = C
    I only want the form to fill out column A & B.

  23. Perfect! Any idea, how this can be used in the Excel web version? I added the form button to the ribbon, but upon uploading the spreadsheet to the Sharepoint, it could not be found. 🙁

  24. Liela we in Iraq really appreciate your amazing simple way.
    If we need to build a database by using excel (we work remotely from different countries) and this data entry form have to validate the new data before submitting them (reject submission for already saved data)
    Can you help please, it's mean a lot for our simple team

  25. Hi Leila, Thank you for this video.
    I was wondering if we can have a drop-down option in the form? For example, for the form in the video, let's say the user can only choose certain tasks which are shown in the drop-down option in the form. I tried using data validation and it only restricts the user from adding different information than mentioned in data validation but does not show a drop-down list in the form window. Kindly let me know if you can help me?

  26. Leila again, great tip and the no VBA is a great feature for users. Now, it appears the data validation feature does not allow a drop down list as the regular data validation does; would you have a workaround to address this? Thanks again.

  27. This is AWESOME. However, if my data validation criteria is based on a "List", it won't include the list drop down. So, if I don't remember how something in the list is identified, I get caught up in my data entry. is there any way around this?

  28. Once the workbook is opened, hide the table and pop ups only the form. How it is possible without complicated VBA?

  29. How to have multiple users, at different computers using a form (custom printable/styled) and being able to collect the data at one source?

  30. Dear Leyla you're great, amazing, smart and beautiful. You keep your audience interested, you keep teaching and I am not bored and each time I look forward to your new videos, thank you

  31. I think it’s too basic. You can’t compare vba VS non-vba possibilities. Cause vba is very more streght compare to non-vba. So your form just allows to add new lign. But if you want for exemple, add or delete a column, you can’t. So without vba, it’s too limited; non-vba is just for rookies. (Sorry for my bad english, i’m french).

Leave a Reply

Your email address will not be published. Required fields are marked *