Excel has an inbuilt Data Entry form that makes it super easy to enter data in Excel.
You don’t need any VBA/Macros or coding or formulas to do this as this is an in-built functionality in Excel. All you need to do is open it and start with data entry.
There are two things you need to do before starting to use the Data entry form in Excel:
1) Add the Data Entry form icon in the Quick Access Toolbar
2) Convert your Excel data into an Excel table.
Once you have done this, you can simply click on the data entry form icon and it will open it. Now you can start using it.
Data Entry form is amazing as it allows you to make the data entry process faster and makes it less error-prone.
In this video about using Data entry form in Excel, I cover the following topics:
— Adding Data Entry form icon to the QAT
— Converting the Excel data into an Excel table
— Using Data Entry form to add a new record to your dataset
— Navigating the existing records using the form
— Finding all records that match specific criteria (such as before or after a specific date or matching a specific text value)
— Find partial matches using wild card characters
— Creating conditional data entry rules to ensure the right data is entered.
Data Entry form makes it easy when you have a dataset that spans multiple columns. It also allows you to quickly navigate the records and correct/edit record if you want (right within the Data Entry Form).
Some additional things to know about the data entry form in Excel:
— If you have a named range with the name ‘Database’, then the Excel Form will also refer to this named range, even if you have an Excel table.
— The field width in the Excel Data Entry form is dependent on the column width of the data. If your column width is too narrow, the same would be reflected in the form.
— You can also insert bullet points in the data entry form. To do this, use the keyboard shortcut ALT + 7 or ALT + 9 from your numeric keypad. Here is a video about bullet points.
Note that an Excel Data Entry form is different than a VBA Userform. While you can easily customize a VBA Userform, It’s not possible to customize the Data entry form. But in most cases, the in-built data entry form in Excel is good enough to do most of the data entry work that you may need in your day-to-day data entry job.
You can read more about the Data entry form here:
Free Excel Course –
Paid Online Training –
Best Excel Books:
⚙️ Gear I Recommend:
Camera –
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic –
Wireless Mic:
Lighting –
Subscribe to get awesome Excel Tips every week:
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Watch more new videos about Excel Office | Synthesized by Mindovermetal English
Hi Sumit.. great video.. very useful and informative. This little gem of a forgotten feature in EXCEL is great for all the reasons you outlined. And.. so much easier than programming an official User Form via VBA.. at least for simple data entry on any table of data. And.. you can easily add Data Validation, as well. Too bad there is no way to get to it from the ribbon and you must add the button to the QAT. For those who are interested, you can also access it through the legacy ALT key sequence of: ALT, D, O (from earlier versions of EXCEL). Just like with the button on the QAT, the cell pointer must be within the table of data for the form to appear. Thanks for all of your great videos. I learn a lot with each one and recommend your site and channel to anyone seeking to better their knowledge in EXCEL. Keep them coming. Thumbs up!
PS – Note that the Delete button will actually delete the entire subject row of data.. and there is no undo from the delete.. the Restore button will not undo the delete, nor will CTRL+Z. However, I discovered an exception. If the the data set is a formal EXCEL table, then closing the Data Form and using CTRL+Z will undo deletions. So, the undo stack is preserved if the data is in a formal EXCEL table, but not if it is just a simple list that has not been made into a formal table.. interesting!
Is there a way to get it to autofill formulas of columns in between the data columns?
Thank you so much, that is so clear and easy.
I have one question, how can I let the new entry goes to the first row after the header instead of having it at the end of the table?
It's really appreciated,
Thanks.
sir how ther give work show for me
Hello, thank you for your great videos. Unfortunately am on Mac and there is no Form function in Excel 2019 for Mac. Is there a way to reproduce the same function with simple coding ?
Thank you so much for your help.
Well said
Hi Sumit. thank you so so much for this vid. I had subscribe your channel. looking forward to watch other excel vids. 👍👍👍
Can you edit the data thru the entry form? Great video by the way.
My Quick Access Tool"n doesn't have FORM in "all commands". I'm using MacBook Air". Your suggestion will be highly appreciated. My email is reazansari90@gmail.com
Hi sumit… Can we edit this form with dropdowns to enter values, create auto generating values etc?
How many columns I can include in a data sheet.
Thank you for this Video. Your instructions are very clear and easy to understand. Thank you once again.
Hi Sumit – Is there a way to get a dropdown list (picklist) for a field on the form? I have defined it for the cell on the excel (list using data validation) and it works fine in excel but not on the form. Form has this field as a open text field. Any way to address this?
I need to create form which has 75 columns. Can u tell me how to create without VBA
Sir, How many columns use in tebal
65 colum add in table
What about pre-defined fields? Can we select them from entry form using choice list? Can we do some dynamic calculations there while we enter data?
Hi, Do you know how to find the "Form" option for the QAT in Excel for Mac (version 16.21.1, from 2018)? I'm following each step you describe, but there is no option to add Form to QAT in Mac…
Better another one. Superb
Hello Sumit. Thank you for the video. I want to know whether we can add a drop-down list in the form as we do with List data validation in the cell? Kindly let me know!
Hi Sumit, i liked your style of explaining. Very pleasant voice. Your speed of articulation is apt. Now I would like to have your suggestions to the following problem. I want to create a database in excel of songs. Title, singer, music director, year, movie name, raga etc will be the fields. The number of rows to be used is unknown since I will be appending songs every now and then. If I search for a particular song, when I type a few charachers, excel should show me the records with all details. The search criteria may change as the year, moviename etc. How i can use your published video in this case. Please give me the link for the video pls. Thanks
I entered a new form entry all lower case (faster for me. Also some entries may be entered in lower case by mistake). Then I used =PROPER function to change to the Proper case (only first letter is capitalized). However, I got a "#SPILL!" error.
Any ideas? Thanks for the video.
Nice but voice very slow
Sir, how can i create this excel sheet. Please tell me.