Create Fully Automated Form in Excel Using Simple Easy to Learn Formulas | Sanjay Sharma | Skillshare
Drawer
Search

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Create Fully Automated Form in Excel Using Simple Easy to Learn Formulas

teacher avatar Sanjay Sharma, Never Say no to anything. Yes You can do

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

    • 1.

      Introduction Creating automated Full Form in Excel

      3:23

    • 2.

      My insights on Using this Tool and Class outline

      2:30

    • 3.

      1 Setting up the Form Part 1

      6:39

    • 4.

      2 Setting Up the form Part 2 and Creating Radio Buttons

      4:39

    • 5.

      3 Creating Drop Download List & Check Correctness of Form also check box is working

      7:49

    • 6.

      4 Creating Submit and Clear Button of the form

      6:50

    • 7.

      5 Saving Form Data in Database Automatically Recoding Macros

      12:36

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

164

Students

2

Projects

About This Class

What you will learn in this Class?

In this class I will teach creating Simple but fully automated Registration Form in Excel. We will use Very simple formulas that you can learn very quickly. In This class you will not only Learn to create a form but also Learn very useful Excel Formulas and Using Macros as well.

Let me tell you in Advance Formulas we used while creating this form. We will make use of excel formulas like Counta, And, If, conditional formating and also macros. Yes only form type of formulas and functions we are going to use to create this wonderful form. 

Features

Features of the Form, Entries that you enter in the form will be saved automatically in another sheet and the fields of the form will be clear once you have submitted the form. Sound good?

Suitable for 

This class is suitable for all students who wants to learn excel. as I tried to use very simple language and formulas did not use vba while creating this form. I hope you will understand and will created different kind of forms in excel and send your form as class project. 

Meet Your Teacher

Teacher Profile Image

Sanjay Sharma

Never Say no to anything. Yes You can do

Teacher
Level: Beginner

Class Ratings

Expectations Met?
    Exceeded!
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction Creating automated Full Form in Excel: Hello friends. In this class, I will teach you the methods of creating forms using Microsoft Excel. Friends, I have been using Microsoft Excel for ten years now and also teaching in an institute. I train students in Microsoft Excel so that they can apply for computer related jobs in corporate office. We are using Microsoft Excel to create forms instead of using HTML or other programming languages. Because you don't have to learn coding for creating these kinds of simple forms. Also, learning programming languages may take lot of time and energy to create such kind of forms. Here in this class, I will show you how to design your form and other tips and tricks and formulas that you can use. In creating these forms. I will try my best to keep the glass simple and easy to understand. Basically, this class is intended for beginners or people who have some basic knowledge of Microsoft XL. After completing this class, you will be in a position to create such forms in Excel. Actually in many offices you may have to work with data entry bar. And if you know how to clear forms, you can easily perform your task and work efficiently. In this, in the class project, I have given few topics on which you can create. In the class project, I have given few topics on which you can create forms and upload your work in the class project section of this class. Now, let me give you a small demonstration of the form that I have created as an example so that you can get a clear picture of what you will be learning in this class. So first of all, let me enter some data random, I'm just picking some random data and we want to show you. Okay. So let's start exciting something, anything. I bring it. Okay? Okay. I have filled the required to DLCO in the soma and now I'm selecting the course that I want to registers. And here we need to make sure that we check this checkbox so that we can submit the Bhutanese uncheck this. It will be like it is gray. And once it green, we can click it. And other thing is that if we delete something, this submit button will begin grace. So that means it is not right to submit the form or it won't allow you to submit. As soon as I click the Submit button, all the entries in the form becomes empty. Entries will automatically will be saved in another seat that I have already created. And just look at the formatting. Formatting will be same as previous. So this is how the form will be working and that's why I call it automated system of automated form that submit your entries in another database sheet automatically. So I hope you will like to know how to create this form and see you in the main class. 2. My insights on Using this Tool and Class outline: You must be wondering, why would you need to learn creating forms while you can enter the data directly into cells. The first is, and I will say is that Excel is so vast and the possibilities are unlimited. More professionalism you gain, better will be for you and your carrier. Who knows when you may be asked to create something that you may not know. Apart from this, if you use form instead of entering data in sell, your work will become faster and more efficient. And you don't have to apply the formulas and the formatting again and again in cells and roles about all these techniques that I explained in the class will help you in getting your data hidden from other users. Like you may not want to allow other users to see the entities that were made earlier in the database. What other we'll see is only the form fields. While working on a data entry project where I have to fill two entries. These forms helped me greatly in entering the data into database directly. Thanks to this technique, I could complete my project within five days time. Now, let me outline the tips and tricks and formulas we use in creating these data entry forms. Basically, we will use the following functions. Conditional formatting. This will help us in formatting the data based on other conditions. Count a formula, a formula, cancer cells containing any type of information including error values and empty text and formula. This helping determine if all or some arguments are true or false. If formula, we use a formula in a few places where we have to compare two conditions. One is true while other is false. Macros. You will also be learning to use macros. Why macros? Actually many times you may have to repeat the same task again and again. In such situations, macro plays an important role. Just recall the macro and you can repeat the whole axon by running macros. All these formulas, tips and tricks are fully explaining each class. So let's get started. Thank you. 3. 1 Setting up the Form Part 1: Just to save still minutes I have already. And the details like a student aim and other details that are required and R form. So let's start. So what I did, I use column B for entering the form fields and column G. Okay? So we can have space for having colon like this. Okay? So first of all, let's add these quadrants in the space provided. So I just did that and let's align it, make it bold. And now we can copy, paste everywhere, wherever we want to place these colons. It just for decoration of the form and it looks good. So press Enter on your keyboard. And now you will have colons. And then here this will be the field of form where you or user will be entering details. And I is another field for registration, date of registration, etc. So let's select two and make it an arrange it according to your likings. I'm increasing the word cell-like this to 51. And similarly, I would like to have here a cell. I'd think height of the form needs to be same for all. So let's select all the fields so that we can, we can increase the height of all simultaneously. So it should be like if T1 would be good enough. Yeah. Maybe 45. Yeah. No, it's fine to me. We have created our form and trees and form field names and also have the input here. And we need to show asterix sign for the required field that needs to be filled and mandatory. So let's reduce the width of these two. And we put six sign here and align it like this. So I copy and paste it like this everywhere. And press Enter. Okay, agenda for gender, we don't need because there will be radio buttons. I will show you how to add them. And for address, let's make it optional. Rest needs to be mandatory right? Now before we start on do this sign is for Swing requirement of the field. And once field is someone has entered input, it's, this sign needs to be changed to something meaningful sign or icon, like something green tick as we have already in the soma as I type something, it changes to green ticked. Okay? Okay guys, to make that arrangement, we need to apply a formula in this column. So let's use IF formula. If this field is empty, band streak pain one. Okay, Sorry I miss something unique to these. And now let's see what happen if we died for something, it changes into one. Okay? So the same formula needs to be applied on all other SDK that we already have. So I copy this and will be pasting the same formula. Okay? So now if you type anything in these fields, they will be changed into been. Now, it's time to conditional format this as strict science into a icon. So just select all. Go to Conditional Formatting. Click neural. Select the first option that says format all cell based on their values come to format is titled Select icon set here. No cell and no cell. We are going to use only single first one when the value is greater than equals to one. And you can have 0 here. Okay? Now, check this option that says Show I can only and best of piano. Ok. Now, if we type something here, it will be changed into this green circle. But we want to change it to the icon. Actually, I forgot to select the icon. So let's go to Conditional Formatting again. This time instead of clicking new rule, click Manage Rules. Double-click to open the Edit window. And from here, select whatever the icon you may want. And press OK and Apply and Okay, now if we type something here, it will change into an icon. So this way, you can make your form look good and professionalism. 4. 2 Setting Up the form Part 2 and Creating Radio Buttons: Okay guys, In this video, I'm going to format these input fields because they needs to be changed. And once we enter some data so that your phone can look more professional, that we see what I mean. As you can see all these fields when empty, I agree. So we want to make the same arrangement for these inputs SL. So what I did, I select all these cells that are the fields, input fields or firm. So select it and go long. Select Conditional Formatting sector noodle. Select format cell that contains the format only sell bid. Blank. Needs to be formatted. You got to pay format. Go fill and select some icon that you may want to have. Like I want to have this color selected. And on the same way you can arrange the text, how the texts we will be in these fields. So I select center line went. And for middle alignment here like this. This is much better now than the field of this form are like blank. They will be greatly. And soon I type something, it will change to white. So you may some innuendo here. And yeah, we can remove the gray color from the agenda. So just like this and go to Conditional Formatting again and clear the rule for the selected cell. Click this option and it will remove this. Now, here in the gender, we need to have radio buttons for selecting male and female. So I select this cell and go to developers. And yeah, if you're Developer, tab is not active, then you can activate very easily by clicking somewhere here and customizing reverse like this option. Here, you will see an option for developers. Just check that if it is unchecked and okay, and your Developer tab will become okay. So now we need to input and insert a radio button. So this is a form control. Click this and select this option button. And we can rename this to whatever we want. We want it to be mailed. Me make it bold. Okay? Similarly, we can select this and press Control D to duplicate and drag it over here. Now, we can change this to female. So we have our radio buttons placed in the correct place. And now we need to give these radio buttons cells so that form can understand if merely checked or female is selected. So select this and right-click and select Format Control. Here. Select, check this. And in the settling, we need to select one cell anywhere in your form. And click that. The cell will be selected and press, Okay. Okay. If the male is set, it will show one. And if female is selected, it will convert into. 5. 3 Creating Drop Download List & Check Correctness of Form also check box is working: We have created male and female radio buttons. Now it's time to create option for selecting Thompson condition and terms and condition or textbox. Here, let me show you what I mean. This is the checkbox for Thompson condition and here we will have terms and conditions option. Let me show you how this can be done. So first of all, we will select the cells, fuel cells, and go to merge and center here so that all the cells can burst into single. I will type first. Okay, let's make it align center and make it bold as well. And here, select few cells for the textbox and do the same. Merge and center. Align it to centre as well. I think. Hi this until we will have terms and conditions and press Enter here and now align it to top, if you will, and make it bold. You can do that. Now, let's create one checkbox so that users can select this and proceed further. So go to developers section here. Click Insert under the form. Drag, click and draw a check box like this. And yes, we can remove this x from the chatbox and make it like this by now and make it big, bigger. And Thompson condition needs to be aligned to this checkbox. So I align it like this and and yes, we can. Okay. No, it's fine. Just to play with the settings. And now we need to assign a cell to this checkbox so that form again, see if the checkbox is correct or not. So what we need to do, we just click this right-click and click Format Control. Here. Check, select the cell by clicking this input field, selling and select the cell you want. Okay? In this case, if the checkbox is checked, it will say true. Otherwise it will say false. Form is almost almost ready. And I lost 30. Yes. Yeah, one thing we need to do more, we need to check if all the required fields are filled or not. Okay? Okay, we are going to use count a formula to see if all the required fields of the forms are filled or not. Okay? So start equals sine h county baggage stat. And I'll select the fields that we want to have in this formula. So click this coma. Coma. Select all the cells that we want to be. Check if they are correctly filled or not. Okay? Okay. Now it is saying 0 because we haven't filled any of the fill. And let's type by checking. Yes, you feel 234. So this formula is working perfectly. So we can proceed with the next step. Now, because states, we want to create a drop-down list for selecting a state and for selecting course because they needs to be fixed, you know, user cannot fill this course name on their own choice. So we need to give them a selection list of selection from which they can select. So we need to create a drop-down. So I hope you guys will be learning new things here in this class as we are using different, different formulas. And these formulas are really very easy and you must have noticed that. So let's go. Okay guys, let's create a drop-down for dog owners for the state. And of course, select the cell where we want to apply this formatting and go to data. Here. Click on Data Validation, click Data Validation. Allow. Allow is select list. And here you can have as many item you want for your drop-down lists. So it's the stage show. You can try it like Florida. You can add as many you want. I'm just using three and drop down list is created, right? And similarly we can do the same process with the course name, data validation, select, list from the loudest and tear type, some course name, etc, etc. Yes. So we learn to create drop-down list and we can select the entries from that list. Okay? So guys, we are almost done and do. In the next video, I will show you how the entries of this form will automatically be saved in another form, another suit that I'm creating right now and naming it as that obeys this, let's call this sheet and the entries of this form will be saved here in this sheet, and that will be automatic. And in the next video, you will learn to create submit and clear button. Let me show you what I mean. These are the Submit and clear button we are going to learn to create these buttons and how they function. So see you guys in the next video. Thank you. 6. 4 Creating Submit and Clear Button of the form: I said previously, we are going to learn to create these two buttons, submit and clear. And let's see how we will create it. We want to place these two buttons here, right? So first of all, just scroll your sheet and come somewhere here and increase the height of this, at least 34 or 35. Let's look good. And similarly for this we will have. And so just like this and go to View home. And low height is 32, Let's make it to the floor and also make it 34. So go to row height and tidy code for this as well. We'll type and name them. Let's make them bold. It's the final. So submit and clear button there, but still they need to one formula to work correctly. So let's go up there. And here we are. We need to apply a formula so that we can see if the form is ready for submission or not. So what we are looking here, we need two options. This one, this needs to be ten because we have ten fields. And this needs to be true because V1, this check box to be checked by the students are Whoever be using this form. So it needs to be true. So we want to look for two condition to be validated. So go to, let's select this. And here we are going to use n formula. And let's draw the bracket. And condition one is this needs to be ten. And condition number two is, is as to be true. This condition is true, it's okay, but this is not ten. That's why it is showing us false. So let's see if we enter all these data. You need to select the drop-down. And let's type something. Yeah. We can do anything. So he has no fan, so it becomes two. So the formula and is now working correctly and we can proceed with buttons, okay? Because The buttons will depend on this formula that we apply in this set. Okay? So go to Submit button and go to conditional formatting here, click a new tool and select. Use a formula to determine which cells to format. Okay? No, slack this equal sign here. Go up and select the cell where we just apply the formula. It needs to be equals to two, right? If it is true, then it should fill the cell with green color. So let's see. Yes. Okay. If it is not true, Let's remove one of the entry. It becomes false, and our button will become this. Again. We can apply a conditional formatting to change the color of this button. Or instead, we can make it gray by default, by just going selecting this and select a fill color here. This gray, it looks fine. We can make this clear button in green always. So because we just need this button to clear whatever we have, this becomes gray if any awful, the condition is not valid. So it becomes true. And a green, this will become green. Okay, Lesser, don't waste time. So I hope you understand how to create these two buttons. And now it's time to place these buttons here. Somewhere here. Alright, so let's select this submit button and Control C, and go to paste and select this link to picture. Okay, Now, hover your mouse and click and drag it like this and bring it somewhere here. Like this. And similarly, if you want, you can increase the height of the cell so that it can grow. And similarly, we will use the same function, Control C. It paste, select the picture, click, and drag this button, and bring it here. Okay, so let's remove this to see if everything is fine. Yes. Now it becomes ingrained and ready to submit. This way you can create your buttons. And in the next video, we are going to learn to submit the entries that form directly to this database automatically. 7. 5 Saving Form Data in Database Automatically Recoding Macros: Already filled this sheet with the heading of the field names that we have for our home. And now it's time to copy. And sorry, it's time to learn how the anti-social firm will be saved here in this sheet. So just copy the headings of the sheet and scroll and paste it somewhere here. Like this, can only two edges, the land and views of these columns, shells. Now, we just need to link the cells associated with the form field name. Select this and click equals sign, we starting formula and enter. Okay? So similarly you do with the father's name as well, equals to sine. And the content of the data will be appear here. And that means linking the cell. For gender. We need to apply your formula is a formula. And we will associate that IF formula with this cell. If this cell has one, then it should say male. And if it is two, then it should say female. So let's apply this formula here in this pursue science. If no, concentrate here in this formula bar. Scroll down, select the cell equals to one, then it should. Male. Okay? So now it's saying, Yeah, female, that means it is two here. And if we click this meal, it will become male here. Okay? Now we continue with the same procedure as the appalling to linking the cell. Let me pause the video and and repeat the same process up to here. For you guys, we have linked all the cells Here according to their field name. And now we can proceed with the procedure or for saving the entries of this form into this database. And for this, we are going to use macro. And yes, you are going to learn to use macro in this class as well. But friends, macro is very, very easy to use and you can easily create macro for anything you want in Excel. But you need to concentrate and to take steps according to this video so that you can finish your phone in one step. So let's see how we can use the macro and how to apply it. So that when you click the submit button, the aunties automatically go here in this form, in this sheet. So I have all the data already filled. So just random letter here, just to save time. And we're going to go to developer section here. I'm going to click Record Macro. No need to change the name of the macro is okay, fine. Click this and come here. Filling the first cell and sift and right arrow to select all the bills here. Control C. We go to database. Shoot here. Select or click anywhere here. Press Control and down. Your Control key, pressed and left key, and then up P and release the control key from your keyboard. And now, click this. Use a relative reference. If you are in Developer tab already, click this. And now without pressing Control, press down arrow like this. Here. Go to home. Or press Control Alt V to bring the paste special window and select values and press. Okay. Now, if you want, you can format the data here according to your wish. You can align center line or left or whatever you want. You can do that like I'm doing. Okay. So it's good. Now, go to, okay, I'll go to Developer and unselect this ruse, relative reference. Go to form again. Scroll up here and delete all these data from it. And click once on the first cell or the first form field. And now you can click stop recording. Okay? So our macro is now recorded. And if we want, we can check it by entering some data here. And just again, doing something not meaningful. Just to using something, something, something random that I hear. And of course here we can type of force, which is already under now. We can go to Macros. Also review, click Macros and click this macro one. You won't see these two other macro because I have other file open with me on my PC. So that's why I'm saying this about you just need to click the macro one and click Run. And it should enter the data that you just filled here in this database yet, okay? So this way, everything looks fine. And cornfield also blank. By after submitting this data in a database. Now, you can hide these texts because you don't want to show these formulas to others. So just go and you can select any text you want. So if sit and already. Now we need to assign the macro that we already recorded to this button so that this button can you start working? So select this button. Click this right-click and click Assign Macro, and select the mental macro that you want to assign and press. Okay, so now this button will start functioning. Can you see I'm just doing random right here. Select this button becomes clean, I click it. Okay, good. So that is now saved in this sheet. Our form is fully functional. These are the grid lines that we can hide by selecting all sheet here and go to View and uncheck the grid lines. Gray lines are not there. And if you want, you can go to row and below the sheet according to your life, just make sure everything looks fine. You can change your other things here. Right? So yes, we need to create 21 more macro to clear the things from this, these fields. So slack. To go to Developer, go to record once again. This time macro record number to click Okay. Select the first cell. Delete, delete, press Delete on your keyboard, select and delete, select and delete. Delete, delete, delete and select macro recording. Let's stop this and right-click, click Assign Macro here. And this time select macro to click. Okay. And now C is also working. So you must have seen that macro is lagging a bit because I have many other programs running in the background. What I will show you, how you can make it fast using a VBS. Yeah. I told you that we are not going to use VBA, but just a single line code that we can enter an art form will become super fast. To open The PBA window. Double-click. And again, it is macro one where we want to apply the application. Integrating novelty is equals to false. Now, you can go and save form to continue serving as a macro theory here we want to know because we want to save it as a macro and naval. Go to Save As and select Excel Macro Enabled Workbook. Skillshare, rebound forms, patient, and make sure this save as type is there as Macro Enabled Workbook and click Save. Now we can close this and let's try filling been form again. Just a few second guys. I'm doing just rubbish text here. And yeah, no lagging. And that is now saved in your shoot. This way you can create your own form. And I hope you guys liked the class and learn a new thing. If yes, please complete the project and show me if you could create a formal so and how easy you find it. And of course, your talent and you can create any kind of form that you may want and upload it as a class project. And guys, thanks for very much for watching this class. And I request you, if you like the class. Don't forget to share it with your friends. And of course, reviews are welcome. If you have any questions regarding this class. If you are facing any problem you can ask me in the comments section, I will reply proactively your questions and even I will provide you solutions very, very quickly.