How to Create a Data Entry Form without VBA - No Macro Required

[Music] hello everyone welcome back you're watching the little apps my name is the lip in this video we will learn about how to create a data entry form in excel without using visual basic application code so with the help of this form which I am going to share with you you know you can add edit fine and delete rule by using that form okay so let's move to excel window and see how can we prepare this form without macro so before moving ahead I know let's prepare the raw data sheet first and then and then we will try to create other form okay so let's enter the data basically I am just going to create a employee data entry form so let's enter name gender qualification city state country salary and the last is no tax okay so let's click the let's select the table basically let's check the range here and press ctrl T to create the table okay just a thick my table has header here click on OK remove the filter press L dff let's go to view and uncheck the grid line right and again select this table and expand the column of it basically okay then select the table go to design and just select any of the style which you want so let me select this one maybe this one right so this is all about the raw data sheet and the name of this they are no this sheet is data entry form so as we are just going to create the form without using macro so we need to apply and as we need to apply validation so that whenever user will you know enter some information in that form then that form will be able to validate the user entry and then it will show the mesas basis on and the validation which has been applied on this like this table basically so for demo purpose I'm just going to apply the validation on you know know like let's apply validation on gender basically so press alt d ll okay so once you play pac lik all right press alt d ll shortcut key you know then it will open the data validation window you know in data validation window or just select the custom here and let's put the formula over here like know just start all this b2 is equal to the male comma b 2 is equal to female right and the error alert should be if like if someone is entering some other information except male or female then there should be some pop-up right saying that okay this is incorrect value you can't enter this information so I am just applying the you know the one validation over here in only one gender column so that you can you can know how can apply validation and you can apply on your columns actual columns whether it's it would be one two or three whatever the columns where you want to apply the validation it maybe you you would apply validation one city state country salary even qualification and name as well you know you can you can apply the custom validation on data validation okay so here I am know this title should be incorrect incorrect gender incorrect gender right and the message should be please enter only male or female in gender column right and just click on okay so this is the validation which we have applied now on gender let's for demo purpose I just want to use a calculation basically so that you know you can learn how to apply the calculation in that form basically you know so whenever we earner we calculate some field basically we whenever in that database whenever we have some calculation on a column so there's no need to enter that value by by with the help of form okay because that column is totally based on some other column like so here we are just going to calculate the tax and it's it will be based on you know salary so it means that you know user form will not allow you to enter the tax only okay tax basically so let's okay and this multiplied our natural bit 10% so this will be the tax amount right so this is the form beta this is the database which we are going to utilize you know the the actual sheet basically you know and on the basis of that formula like on the basis of this data structure like name gender qualification city state country salary and tax form will generate a custom form like and user will start entering those information and that will get transferred in in this particular sheet right so before moving ahead let me tell you about like data entry form so basically a deterent reform is one of the best way to enter data in a table so in this table either you can enter some information like with the help of sheet or you can use the data entry form right but determine tree form is really very very helpful I know if you want to enter some data in a table right but you know like creating a visual basic application form is a tough task basically you should know the visual basic application code and as well as you should have a lot of times to develop and test all those all the forms and features available which you will you are going to design basically you know but the good part is you know if you do not have time or you are not aware about you know like complex code Visual Basic application code they can then you can utilize the inbuilt no form that is a pre-designed and flexible form has been provided by you know and the Microsoft Office Microsoft basically you know and with the help of dat form because in that form has a lot of you know a lot of feature and you can utilize those like to add add the records in excel sheet or you know to edit the records find and delete the records by using that form okay so before before going further like before going ahead we need to activate a dad Excel and that predefined form so let's go to a file menu and click on option and this is the Excel options okay so in these exception you just need to go to owner quick access toolbar and increase access toolbar just select the choose commands from so let's select the commands not in the ribbon basically and just go down and try to find out the form basically so let down okay so here you can see that this is the form right so you just need to add this just select this form click on add and again click on OK so once you click on OK it will start appearing in quick access toolbar so here you can see that it is there is a button I know the form is available here okay so either you can use this do this shortcut shortcut basically there you can click on this form tool on show the form or you can apply the shortcut key that is alt d oh okay so once you will place all to D o or you can you click on this icon in quick access toolbar formal a pop-up blank form will appear in on your screen so poor like if you just want to enter enter the like into the information or make some amendment in the existing data you just need to select any of the cell in particular table where you want to enter the data right and just click on the form so once you click on the form you can see that data entry form is available here okay and this is data entry form so let's discuss about the data entry form first and all the features available here and then we will we will learn how to apply this form in a proper way right so basically the you know this form header is you know this form is speaking the header from the excel sheet so here you can see that you know it's a data entry form and that's why the header of this form is also data entry form right and this form is speaking all the columns basically named on gender qualification city state country salary and tax so this is all these columns are coming from the table which we are we are utilizing to enter the form right and these are the fields basically name is name will get entered by you know user and gender qualification city state country salary but you can see that in the taxes there is no user input basically you know because see we have used earn the calculation in the formula excel formula to generate the tax and that's the reason it it's automatically you know identified that okay tax is a field where there is no need to enter the information it is based on know some other columns that is salary column right and this is very brilliant you know that's why it's not picking our considering you know tax filled as a entry field basically no and all the columns basically you can see that in a column width are equal so basically column width are also picking from you know and the table here so basically what it does it it picks the earn the maximum column width from all these columns and set the maximum dad column width to every you know every entry field basically name gender or politician it means suppose let's close this and let's explain the city okay so we have now we have the city with this maximum it means Oh No whenever we will select this any of the sale and just click on the form then you can see that you know it has picked only the maximum column it that is the status for city and it has assigned that weight to every column that his name gender qualification city state country salary even tax but tax is not editable and that's why it's not showing okay and so this is all about the column width and all the things okay so in whenever you enter some information and if you think that you know column which is not sufficient then just try to increase any of the columns in width and then just click on any of the cell and then click on form and you can see that okay the form the the text box field will be automatically adjusted basis on the maximum column width available in your table right so this was all about the structure and this is the help icon you can click on and jump to the Microsoft website to get help with like on custom our data entry form basically and this is close and this is showing that you know we have and know basically a one of one it means we do not have any records you know and this is blank basically that's why is showing one of one means blank is the only blank credit with the help of new you can like start new records because as there is no no data over here that's why there is no need to or like click on new you just start entering information and then we will click on new then it will fill the information whatever the data has been entered in this form and then it will turn recreate a new field right this is a delete basically whatever the data has been selected over here that once you click on delete then it will get deleted right restore whenever you make some modification in data then the option to restore find previous and find next basically you can move from previous and and next we will see with the example criteria with the help of criteria you can find on the records basis on the criteria which will get in torino ah this field basically we will also see with the exam and this is close so suppose if you want to make a one entry so just fill all the data and then just click on close so first of all it will it will update the data into excel seat and then close this window okay and apart from all these you can see that there is a scroll bar okay so this scroll bar is no like this scroll bar helps you know moving from one record to another record but as of now we do not have any record that's why it's showing you know only one record right so here you can see that this is blank and once you click on you know new records so let's enter the first you know first record fell yeah couple of you know records and then we will utilize the delete find the previous find next restore and criteria features of this form right so let's say you know enter let me enter my name clip meal so as we have applied you know some validation over here like either you user can enter male or female if we will enter some other information then it will give some error so let me enter you know ma male and one basically that's a that's not the part of our by addition idly it should give the error while updating the information so let's move to and qualification let's you know or MSC you know city you know I know Valley State again Delhi country India salary 90 and right that's and just click on a new okay so you can see that you know this is giving error basically incorrect gender because see if we have inter mail here you know and that's why it's giving error because it's showing that please enter only male or female in gender column and remember we applied custom validation over there and that's why it's giving error so that was the simple one write a simple to how to apply the validation so you can apply a validation on one or more columns where you could where you need right so just click on cancel okay and then just mail you know and right but you can see that name is not appearing somehow I do okay so with the help of this right a scroll bar so here I have a scroll down you know and that's why it's showing our new records once you click on this icon above icon and it will start showing one of one it means this is a this is the record which we have selected as of now so we can you can edit this let's enter the lip and suppose if you want to change the city like Delhi to New Delhi basically then you can make the modification as well now you can see that as earlier it was blank you know and CT was Delhi now we have made we have a link made some changes and that's why it's showing a restore so once you click on restore then it will start showing the previous value okay so now name is blank and city is Delhi so let's again enter the lip here and the city should be New Delhi okay now click on new so it will update all the information like glyph mail and see New Delhi and all the information as it is okay let's put some more information like some fictitious information like employee one and so female qualification BSC you know City no Ghaziabad okay state a you peak country India and salary let's litigate right and just click on new okay it means whenever we click on new it updates the information which are available in this bar text box basically and then it creates the new basically it's a blank record so that either can enter some information let's enter a couple of more in more decodes basically right so these are as of now we have internal are for for records with the help of this form right so let me show you the feature basically so you can see that our scroll bar is on top of that you know top of the like the bar basically so it's showing one of four records it means it means there are 40 cards and we have selected one so this is the first record right you can make modification you can also click on delete so to delete the record so let let me delete this one just click on delete and once you click on delete it will our take a confirmation from you right and display the course will be deleted it means display means whatever the information base is available and that associated record will get deleted from the sheet right so once you click on either you can click on cancel to cancel this or you can click on OK so just let's click on OK now you can see that the first record has been deleted successfully right and with the help of this one find find previous find next even move from one one to next and again with the help of you know a scroll bar you can move from one record to next record and if you'll reach on the last record then if you'll click on the down arrow right move the scroll bar then it will create a blank record over here and with the help of blank record you can you know enter some information like employ for female you know BSC again suppose gzn Haryana India right and click on new so now we have now we have total 40 codes you can move for above and you can see that one of protocols okay suppose you want to like as you as we see that you know using the delete you can you know delete the records use the restore we can restore the information if we make some modification right with the help of fine next fine previous and find next we can move from one decode to another record and this is the criteria basically so with the help of criteria you can search suppose you here for 1,000 or 10,000 recalls right and and you you want to search any specific record basis on or some of the records basis on some criteria you just need to click on finally criteria so once you click on criteria then this is the criteria window right you can see that this is showing the criteria it means you just need to put some information so basically this this is only - only for search right it will not update update the data to form because you can see that the tax is also no you can also enter some information in tax this is just just to search not to update basically and that's why it's showing a criteria and the criteria button has been seen to form basically so you can you know switched from criteria to form or prompt to criteria right so let's use some criteria right so poor if you want to search the qualification like the qualification should be B SC right and with now you can use this fine previous you know so it will only show the where the qualification is BSC so as you know graduation is also and the third record is a graduation but whenever be like this is employee one and we are it's employee one data is showing if you click on next right then it's employed too because qualification is also BSC again you click on you know find next then it's showing the employee force so it has not it has ignored the why three non-employee three records because because qualification is graduation right then so we have no we have only 3d courseware qualification is BSC basically so this is very useful form and there is no need to code the macro and apply some macro coding over here right so it's very helpful and if you're not familiar with the macro coding or visual basic application then there is no need to worry about that you just use this panel you just form to enter the information before finishing the video you know let's discuss about some important points about this data entry form so there are some limitation of this form okay so the maximum number of input boxes you can use in data entry form is 32 it means if you have a table which has more than 32 columns then this form will only pick 32 columns okay and addressed will be discarded you know and you know you can utilize the wildcard character while searching the searching the criteria that's additional point you can do that you know and there is do or like inbuilt validation over here there is no drop down over here there is no combo box all of the fields are only text box so of for like doing that you know you just need to realize rely on excel validation has I know B as we already applied to some validation on gender in that way you can apply the validation but this form has no inbuilt validation okay and if you want to create the complex form where you need to some custom validation and pop up a message over there and you know some additional features like applying conditional formatting you know like B conditions to mark the background color red if someone has entered in incorrect information then you need to go or you know go for a visual basic application form and that will be not so complex but that will take time to develop develop that form and you need some basic understanding of visual application Visual Basic application if you think that you need that form as well and if you just want to learn about that form then just put a comments in comment section so that I will I will record a video on like custom dating custom design reform with the help of Visual Basic application and covering step by step so that you can learn that method as well pocket hope you enjoyed this video please like share comment and subscribe thank you for watching have a great day bye bye

Channel: TheDataLabs

Views: 130,856

Keywords: How to Create a Data Entry Form without VBA - No Macro Required, Creating a form without VBA, Employee Data Entry Form, Microsoft Custom Form in Excel, No Macro in From, Form by TheDAtaLabs, how to make macro enabled form in Excel, How to Create a Data Entry Form in Excel [One-Click, No VBA], Quick Data Entry Form (Without VBA)

Id: xlKHDD16rWE

Length: 23min 43sec (1423 seconds)

Published: Sat Jun 01 2019