馃攳
SUPER EASY Excel Data Entry Form (NO VBA) - YouTube
Channel: Leila Gharani
[0]
Today we're going to take a look at
[1]
creating data entry forms in Excel.
[4]
There's going to be no VBA,
[6]
it's going to be super easy.
[8]
(groovy beat music)
[11]
So let's say for one reason or another,
[13]
you have to input some
data manually in Excel.
[16]
And you want to make the
experience as pleasant as possible.
[21]
You can use data forms to do that.
[23]
It's especially good if
your tables are really wide
[27]
and you want to avoid
horizontal scrolling.
[29]
Now, just to keep things
simple, in this example,
[32]
I have a small table
where I want to input data
[35]
in category, task, date,
person, and status.
[38]
One way of entering data is
just to input it here, right?
[41]
I'll put in site, task,
let's say health check,
[45]
date, person, James.
[47]
Now if I wanted to edit something,
[49]
I have to go back to
that cell and then click,
[51]
go to edit mode and then expand that task.
[56]
An alternate way of doing
this is to use a form.
[59]
But there is one prerequisite.
[61]
And that is that you
have to turn your data
[65]
into an official Excel table.
[67]
But it's really easy to do that.
[68]
All you have to do is click anywhere
[70]
inside that data set and
then press control + T.
[74]
We're just going to go with
the default and click on OK.
[77]
Now it applies the standard
or default table formatting
[80]
so I'm just going to go
up here to table styles
[83]
and take away that formatting.
[85]
So now that my data set is turned
[87]
into an official Excel
table, I can use a form.
[91]
But where is the button for the form?
[94]
The thing is, it's not in the ribbon.
[96]
To be able to use it, I can add it
[99]
to my quick access tool bar.
[102]
So just click on this down arrow,
[104]
go to more commands,
[106]
it's not a popular command,
[108]
it's a command not in a
ribbon or all commands,
[111]
so you can get to it both ways,
[113]
I'll just go to commands
not in the ribbon,
[115]
and scroll down to F,
I think I just saw it,
[119]
form, right here,
[120]
and then click on add
to get it on this side,
[123]
and then click on OK, right?
[125]
So now I see it here.
[127]
Now all I have to do is click on it
[129]
and my form appears.
[131]
Here's the thing, though.
[133]
If you're outside the table
and you click on this,
[136]
it tells you this can't be
applied to the selected range.
[139]
So it really depends where
your active cell is.
[142]
Make sure that you're
somewhere inside your table
[145]
and then click on this to
activate the form, right?
[147]
So I can see my first line of data.
[150]
To add something new, I click on new.
[152]
Let's add course here, task, date, person,
[156]
and status, let's say started.
[158]
And to add this to the table,
you just have to press enter.
[163]
You can see it right here.
[164]
It automatically takes you to the next one
[167]
so you can just go and add
person, James, press enter,
[171]
it's right there.
[172]
The good thing is that you can scroll
[174]
through the table right here,
[176]
you can go and edit something,
[179]
and then press enter to
add it to the record there.
[182]
You can also search for something.
[184]
So, notice here, we have criteria.
[188]
So when I click on this, I can actually
[191]
search for any of these categories.
[194]
So, for person, let's type James
[196]
and then either click on
find next or find previous.
[200]
So here I can see one
record for James is SEO
[203]
and another one is health check
[206]
So to toggle between that
criteria and the form,
[209]
you have to click this button here.
[213]
Now let's see if you change something
[216]
by mistake, so I put 30 here
[218]
and then I say, Oh no, that was a mistake.
[219]
I just want to go back to what I had.
[221]
I can click on restore and it
puts that record back
[226]
So as long as you haven't
pressed enter and sent it there,
[229]
you can restore it.
[230]
Now the other good thing
about data entry forms is that
[233]
you can also add data validation to this.
[236]
So the moment you activate data validation
[239]
on any of these categories here
[241]
it also applies to your form.
[244]
So let's say for these
dates, I want to make sure
[246]
that people input the right date.
[249]
Go to data, data validation,
[251]
instead of any value, select date.
[254]
For start date, I'll pick 1/1/2019
[258]
and my end date is 1/1/2020.
[261]
So I want my dates to
be between these two.
[264]
I'm going to add an error alert as well.
[267]
Now, let's go back to our form
and let's enter a new record.
[272]
What if I input the wrong date here?
[274]
So let's go with 2/2/2030
and I press enter.
[280]
It puts it here, but notice it says,
[282]
Please input date between this.
[284]
And when I click on retry,
[287]
it takes it away,
[288]
So now I can go update
this, let's say 2019
[292]
and when I press enter, it adds it there.
[296]
But let's add the rest
to this, press enter,
[299]
and the other records are
in there as well
[302]
And just close the form.
[304]
Now, you can also use forms
[306]
on your existing tables that you have.
[309]
And you can use it to look for stuff.
[312]
So let's say this is a bigger
table I have on this other tab
[316]
and I want to look for something.
[318]
So I'm going to click on the
form here to bring this up
[322]
and click on criteria.
[324]
And I want to look for
values that have revenue
[327]
greater than 500
[330]
So the good thing about forms is that
[331]
you can use the greater
than, less than sign,
[334]
you can also use wildcards here.
[336]
So you can use the
asterisk sign as a wildcard.
[339]
So when I click on find
next, then I see this record,
[343]
beverage is Coke, has
revenue greater than that,
[346]
then it's 540-512 and so on.
[350]
Okay, so that's how you can use data forms
[354]
on your existing data sets
[356]
to either input data easily
or to easily look for stuff.
[361]
If you like this video,
give it a thumbs up.
[364]
And if you want to become better in Excel,
[366]
if you want to improve
your Excel knowledge,
[369]
consider subscribing to this channel.
[372]
(upbeat music)
Most Recent Videos:
You can go back to the homepage right here: Homepage