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.
Sanjay Sharma, Never Say no to anything. Yes You can do
