Data Entry Form is one of the best ways to enter data in a table.
But, creating a form to enter data it not a cup of cake.
Yes, that's right.
While struggling with this problem, I have found that you can create a form in Excel for data entry just with a single click.
The best part about this form is it’s pre-designed and flexible. Just look at the below example.
Here's the idea:
To create this entry form, the only thing you need to do is to activate it.
How to Create this Data Entry Form
In a short while, you will discover that how helpful this tool is.
But, this is hard to believe that there is no listing of this data entry form in the ribbon. So, you have to make it appear before we use it.
And before you activate this form, you need a table to enter data or at least headers of the data.
To activate it, click any of the cells in the table or just select one of the heading cells and use one of the below two methods.
- Use the keyboard shortcut key: Alt + D + O + O.
- Add an icon to quick access toolbar.
- File ➜ Options ➜ Quick Access Toolbar.
- Choose Command From ➜ Commands Not In Ribbon ➜ From ➜ Add ➜ Click OK.
And if you notice, all the headings of your data table automatically captured in the form.
How to Use this Data Entry Form
To use this form you need to learn some basic things which I have mentioned ahead.
#1. Create New Entry
To create a new data entry fill all the input boxes with data click on the “New” button.
Now if you look at the below example you’ll find that there is no input bar for the amount column.
We have a formula in the amount column to multiply the quantity by the price.
That means this form is quite smart to discover the columns where there is no need to input data.
#2. Navigate Entries
To navigate all the entries which you have entered in your data, you can use the scroll bar or you can use “Find Prev” & “Find Next” buttons.
#3. Edit Entries
To edit an entry you have to first navigate to that entry by the using method mentioned above.
Once you navigate, you can make the changes in any of the input boxes and after that, just press enter key.
#4. Delete Entries
To delete an entry you have to navigate to that entry and click on the delete button.
A pop window will appear for the confirmation of the deletion and it will instantly delete that entry from your data table.
#5. Find a Specific Entry
If you are working on a large data, you can use “Criteria” button to find a specific entry.
For example, if you want to find entries from below table which match some specific criteria, just click on criteria button, enter the criteria in the input box and press Enter.
You will get the data entries matching your criteria. You can also use your criteria in more than one input box.
Important Points about this Data Entry Form
Well there some important points which you should know to play well with this form.
Conclusion
In the end, I just want to say that this data entry form is very useful although it has very few options.
If your requirement is not so high you can use this tool rather than clubbing in some high-grade user-forms.
I hope you found this tool helpful in your work and now tell me one thing.
Have you ever used this form before?
Please share your views in the comment section, I’d love to hear from you and please don’t forget to share this tip with your friends.
You Must Read this Next
Watch Window: By using watch window you can track all the important cells into one place [...] - Barcode in Excel: By installing this simple font you will be able to generate bar-codes in Excel [...]
- VBA MsgBox: Most of the VBA programmers use it in their macro codes to make them more [...]
- Goal Seek: It’s like a simple popup box on your Excel window to show a specific message [...]
- Dependent Drop Down List: A dependent drop down is all about showing values in a drop down list according to the selection [...]
- Dynamic Drop Down List: When you added a new entry into the source list, that entry is automatically updated in the second drop down list [...]
- Auto Format: In auto format, you have numbers of pre-designed formats which you can apply on your data instantly [...]
- Consolidate Data From Multiple Worksheets: The best part with this method is, you don’t need any VBA/Macro code. It’s just a simple option [...]
- Table of Content using VBA: It’s a simple VBA code, and when you run this code it adds a new worksheet with a table of content for all the other worksheets […]
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
I have a question
How can I hide a row automatic when an amount is zero on that row and automatic unhide that same row when the amount is > than 0.
Super helpful, have been wasting too much time trying to understand what you made understandable in minutes–thank you!!!
I like this write up because it is a ready made tool that just be used. Thanks for this.
Myself Ayesha its very helpful for learners the way which has showed step by step really very interesting & helpfull to learn.
I am very fan of your site. Wow fantastic. Really I appreciate your hardwork to make understand us.
Need one help.
I have a Pivot where in the Pivot there is column with XYZ% format. I want to see it like in this format only.
However I got surprised seeing the format automatically got changed from percentage to number format.
65.34%. …. 4567% or 2345.754
like these.
I am not able to understand why this is happening?
Hi
Can I make multiple entries in one user_form in Excel just like Access because if a company sells many items on one date and one invoice number, obviously they will not fill many form for one date, one product and for one invoice many times
Thnx
I need assistance to make the 3 items (ISP ID, Pit Sector, OSP Hole Depth) and the Image to change in the form as I select the Target Sheet from the combo box (OSP ID #)
I want a code that would change 3 text box instantly on the same from as I change the combo box items. I also need the image to simultaneously change as the selection in the combo box is made.
Any assistance please?
I created your ‘How to Create a Data Entry Form in Excel [One-Click, No VBA]’ and it work fine but I want to have a few of the form headers to be a drop down list. Can’t see where I can create this. Please advise?
Thanks for this information. I have one question . Is it possible to have the form in a sheet instead of always looking for it on the top ? It will be more user friendly too . Thanks for your help.
How do you look at the code of the data form that is created by excel?
Puneet please let go private for a detailed discuss on your trainings.
Hi Puneet,
Thanks for these great suggestions which I am sure work like a treat with any PCs but I use a Mac (Excel for Mac) and the Form is sadly not available as you describe it.
Would anyone from the Mac world be able to help please?
Many thanks,
Cat
I’m new to vba and forms and was amazed at how easy this worked but discovered an issue. When I tried to use this on a second table it crashed Excel. I tried it in a different workbook and different table and it crashed. I went back to the original workbook and table and it works fine. Is this a one trick pony or, as I assume, am I missing something basic?
I would really love to use this in multiple workbooks and multiple tables in each workbook if possible.
Thank you
Is it possible to change the form itself? Like – move the text boxes and controls around a bit?
not really, it structures itself according to the table.
I’m new to vba and forms and was amazed at how easy this worked but discovered an issue. When I tried to use this on a second table it crashed Excel. I tried it in a different workbook and different table and it crashed. I went back to the original workbook and table and it works fine. Is this a one trick pony or, as I assume, am I missing something basic?
I would really love to use this in multiple workbooks and multiple tables in each workbook if possible.
Thank you
I tried this Data entry Form. The only problem is that I have drop down lists on some columns and input does not capture my drop down lists when I use the form. How can I make the form to display my list so I can pick options from there
how to add dropdown in a form without using VBA?
It is possible with photo to add.Any option
Not really.
Puneet, i am unable to understand the benefit of it as all these can be done in any range or table…….
download link pls!!!
Puneet, thank you for your article on this Excel data entry form.
For users that want some more advanced Data entry forms can I suggest to try Virtual Forms for Excel.
http://www.virtual-forms.com https://uploads.disquscdn.com/images/c48cb01a7aca0a863855708c4652ceb0936d0de6661bf6d52a5adb4fcb4660bd.png https://uploads.disquscdn.com/images/9152ed5998fec9692bbf3ca35b3223e46a566729aa1d23ad60843189600f86d1.png https://uploads.disquscdn.com/images/66bdf7934034c854b9bf01b3b0b0b31bbe6dda1a195cdac767a23b04bf46a016.png
Is it possible to change the form itself? Like – move the text boxes and controls around a bit?
how can i take a printout of the form with all details
Hello Phanish,
Only Possibility is, to capture screen first. And, then take printout.
No. It’s awesome. Thank you so much.
You are welcome sandip.
is it possible to edit the underlying vba code to format the data to autofit in excel columns ? thanks in advance for your guidance.
You can use a separate VBA code to format your data instantly when you insert a data entry.