Using Data Entry Form in Excel (NO VBA or CODING Needed) | Easy Step by Step Guide

Using Data Entry Form in Excel (NO VBA or CODING Needed) | Easy Step by Step Guide



hello and welcome to the video by Trump Excel I am so much Munsell and in this video I'm going to show you how to use a data entry form in Excel now edita entry form is something which is already there in Excel it's an inbuilt functionality so you do not have to use any VBA any programming any macro to do this it's really easy to use all you have to do is just open it and start doing the data entry now here I have a data set already where I have these five columns and I've already added these four records and I'll show you how to use a data entry form to add more records or to go through the records and all the amazing things you can do with it but before you start using the data entry form you have to add it to the quick access toolbar so that you can open it so to do that I would go to any of these ribbon tabs right click go to customize quick access toolbar this opens the Excel options dialog box and here I would click on all commands now I would scroll down and I'm looking for an option called forms so these are listed alphabetically and you can come down and here you find the form option and then you click on add so this would add it here and now I click OK and as soon as I do this it would add the data entry form icon in the quick access toolbar and when I click on it it'll open that form the other thing that I recommend to do is you convert this data into an Excel table now you're denied entry form might still work without converting this into an Excel table but this is something I highly recommend and to do that you simply select this entire data set or any cell in the data set go to the insert tab and click on table and when you do that it will open this create table dialog box here check that the range is correct check my table has headers because it has and click on OK and as soon as you do this it would convert your data into an Excel table you can also format your Excel table you have all these options in the design tab and you can also individually format these so I would quickly go to the home tab and change the color of the header so these are visible now let's start using the data entry form so I say any cell here in dataset and I will click on the data entry form icon here in the quick access toolbar and it opens this dialog box and it has the name which is the name of the worksheet now you can see that it has these five items listed here which are nothing but the column headers so all of the column headers that you have here would be listed in this form in this case there are only five and you see that it lists one of the records which is the first one here you also have all these options here at the top you have one of four which tells you how many records there are and then you have all these buttons so the new option the new button when you click on it it will give you an option to enter a new record of fresh so all these would be blank and then you can quickly enter this and it will be entered in the in the data set delete would delete this existing record so in this case Mike Payne's is selected if I click on delete this would be deleted here it only deletes the data not the row you can use the restore when you are editing the data the data so for example in this case if I am changing anything here and I change the record and I want to get back the record which is already there I can click on the restore button you can use the find previous and next to go through the next record in the previous record you can use the criteria button when you want to find specific records so for example in this case if I want to find out all the records where the area of expertise is PHP then I can use the criteria record at the criteria button and close would close this dialog box now let's see how to do data entry in this case so what I am going to do is I will click click on view and in this case I would start with the date here so let's say it's 15 july 2019 now the date format i'm using is the UK format or the india format and if you're in the u.s. you would have to change this accordingly because in u.s. you use month date and here so in this case i'm using the format which is date month and here the name of this case let me use the area will be Excel let's say I'm interviewed by someone called urgent and the status is let's say in progress and now what I can do is I can simply either click the hit the enter key or I can click on new and when I click on new it would enter this record here and I can start afresh with a new record so this again makes my data entry a lot faster so see what happens when I click on new I can now come back enter a new record here and the one that I already had is already added here so you can easily keep on doing data entry in the same small data entry form instead of worrying about this entire data in the worksheet this is also helpful if you have multiple columns so in this case let's say you have 30 columns or 40 columns then all of those are listed here so this is easier to make the data entry and less error-prone because you may enter a data which is in the wrong column or in the wrong room but with a data entry form these chances are less also this makes it a lot faster other thing that you can do with the data entry form is you can find specific records so in this case let's say I I can scroll through these records so I can I have this scroll bar here so if I use this I can use this button to go through all these records I can also use this find next and find previous button but let's say I do not want to go through all the records I only want to go through the records where the area of expertise is PHP so I would come here to the criteria button click on it and here I can specify the criteria you can see this changes and it says criteria now in this a in this example I would type PHP in the area field and now when I go to find previous or find next it is only going to show me those records where the area of expertise is PHP see what happens when I click on find next it gives me the record for Ross Taylor which is this one and the area of expertise is PHP now if I go to find next it gives me Steve grant and now if I click on find next again it is not going to give me anything similarly if I go to previous it gives me an Australian but if I click on find previous again it is not going to give me anything because it is only identifying two records two out of five now this is just one thing which you can do with criteria you can do a lot more so for example instead of like a an exact match you can also do a partial match by using wildcard characters so let's say you want to find out all those records where the status is in progress now it may happen that someone may enter this with in progress as a single word someone may type it the way I have done it which is in – progress and someone may type it as in space progress now in those cases instead of doing it three times or four times you simply come to the status option here type the asterisk sign and type progress now what this does is East risk is a wildcard character that represents any number of characters so this is going to find all the records where it may start with anything but it ends with the word progress so if I come here and I click on find next it gives me Steam grant which is this one which is in progress then I go to find next again Ruth Fowler then I put qualify on find next and it gives me my record similarly if I go to find previous it is only going to show me in progress record it is not going to show me anything which is which is other than in progress so rejected and hired will not appear similarly you can also use criteria which are which use operators such as greater than or less than so for example I want to go through all the records where the date is Lee is greater than 10th July so I can simply use greater than 1007 2019 and that is it now if I can you again use the same thing I can go to find next it gives me 12 July 15th July and if I go to previous it only gives me till 12th July because these are the only two records which satisfy this criteria I am using these buttons you can also use these icons here but in this case if I if you use the scroll bar it is going to give you all the records so if you're using a criteria it's better to use find next and find previous options the last thing that I want to show you in this case is how to restrict data entry in these based on a condition so let's say you do not want these cells to hold a value which is first of all not a date and if it is a date you only want to enter dates which are between july 2019 and December 2019 you do not want to enter any day which is outside of this range so what you can do is you can select these cells where you want this data validation rule to be applied on the condition to be applied you go to the data tab here and you click on data validation and in this data validation dialog box I would come down here and select date so I'm saying that you only allow those criteria those values which is a date and is in between these two specify dates so here I can type I only want this to be between let's say july 2019 to 31st December 2019 and I click OK now what happens is if I go to the data entry form I open I click on this icon and open the data entry form and I try entering a record which is outside of this range it is not going to allow me to do this so for example if I come here and I say 1st March 2020 and I try and enter this record it is going to give me an error it says the value doesn't match the data validation restrictions so it is ill asked me to retry and I can go back and I can correct this so this allows me to make sure that my data entry is again correct there are no errors also because I have specified that this should only be a date type data value if I by mistake enter something which is not cut date and I again try and enter this it is again going to show me this from this box where it says that this does not match the data validation restriction so this is again a very important thing very good thing if you're doing a lot of data entry that you restrict it so that in case you enter something which is incorrect the data entry form is going to show you this error and your data entry will be a lot less error-prone so this is how you can use a data entry form in Excel I hope you found this video useful also I would really appreciate it if you liked the video and you subscribe to this channel so I can inform you whenever I have new videos coming up thank you and have a nice day you