Hello and welcome to our new video. In this video we are
going to learn how to generate salary slip of an employee
working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
In this video we are going to learn how to generate salary
slip of an employee working in an organization.
first one is Rahul Imran Jishan you have to write this name
after that add this name you can see here now it is same
Rahul Imran Jishan you can see here so now you have option
to make a list so what you can do is delete from here
I will delete this although it is not showing option of
delete and the reason behind it is that I have just told
you that there are some lists which are default so where
will it show like I have put anything coriander, ginger,
apple so here it shows the option of delete so you can
delete it from here so it is permanently deleted so from
here it is deleted so now what I have to do is
I have to pick up all the names from here now picking up
from here means that you have added it once you have to go
there and tell it a pattern that first of all it is Rahul's
name then Imran's name or Jishan's name and after that all
the other names will be picked up which I will show you we
have added the list here now what we will do is we will
click on ok from here also click on ok so in the name
option we have put Rahul
Rohilla after that we have put
Jishan Khan you have to put at least two names so that it
will be recognized which list is this so I have put Jishan
Khan after that what I have done is
I have selected both the cells and the plus button
I have dragged it you will come up to 10 after that it will
start repeating as you can see this is the same name which
we had written in the custom list
Rahul, Rohilla, Jishan Khan, Nishant, Raghav, Neelima
Radha, Shyam, Ram, Mohan, Shrey now as there was another
list which was starting after the name of Rahul and Imran
so what we have to do is we don't have to pick up this list
again we have to pick up the other list for the other list
what I do no let me remove this this is the reason of
confusion because the names are same here I have written
Imran and now see what I have to do
I have to select both after selecting both I drag so you
will see different names here Jishan, Shivam
Deepak, Hrithik, Hariom, Neeraj, Shyam
Harish is visible here
Neelima, Radha, Shyam so from here to here the list which I
have added in the custom list from here to here to serial
number my list is different because I have just shown you
this thing so we got to know that we have serial number 20
and here there are some names of 20 employees now what you
have to do here employee's ID is also there employee's
account number is also there so let's add that thing from
here
I insert a column here now see if you feel that here you
have written serial number and name after that you remember
that
I have to write employee's ID and employee's account number
I have to write employee's ID before employee's name so in
that case what you have to do in that case you can see this
stick here is column A and here is column B so what you
have to do is go to middle and right click with mouse and
here you will see insert option click on insert option now
see here
I have inserted in wrong place just give me a minute sorry
so undo it with ctrl z where we have to do we have to click
here and see what it is going to do we have to click here
and insert after B if you click here before B it will
insert column now in this column you can put employee's ID
and employee's account number so I put here employee's ID
first of all emp .id now employee's ID generally is in 3 or
4 digit so what you can do now it is like will I write here
no I will not write it is a
now that function what it does is it generates randomly
like I will show you so what you have to do you have to put
here equals to sign then you have to write see it came
double click it it is asking me what is the value of bottom
what is the value of top because employee's ID should be 4
digit the lowest number and the highest number double 9
double 9 so in between some random ID will be generated
which will be assigned to every employee so what I do in
bottom I put 1000 and in top I put
9 double 9 and after that I entered so here a number came
5954 now this number we did not put this random function it
generates if I drag it down you will see from 1000 to 9999
some numbers will be assigned to 20 employees like I drag
here see here above 10,000 means it will be 5 digit but
what we said it should be between 4 digit from 1000 to
double 9 double 9 so in this way
I got employee's ID account number is always 14 to 16 digit
so we will do the same thing like we used random between
formula and generated employee's ID similarly we will write
account number so I will go here see what I have to do
before employee's ID I have to add his account number so I
have to insert one more column how can I insert that column
I will go here because I have to do it before B so I went
here and inserted when I inserted means what was written in
B went to C and B column is empty so now I have to write
account number for account number I will write a slash c
and here account number so how to generate account number
from where you will get number so what we will do 14 digit
and how to get it we will get it from random like we got it
equals to between double click bottom value bottom value 16
568 and 9 7 6 4 5 how many
2 4 6 8 and 10 10 and 12 and then I will insert 17 and 17
you can insert any random number which is 12 digit
I will count it 2 4 8 12 6 8 10 12 close bracket and enter
plus 11 take it here still account number is in power of E
so make it small and make it big 2 4 6 and here also 2 4 6
enter this is account number account number is little big
but we will manage sorry cough is coming so bear with it
169891 what you have to do drag it down it will generate
like here always remember where you have to use ran between
formula always remember whenever we change like this value
I have to use it problem is ran between formula is there so
you have to copy it and paste it here paste special when
you paste special you will get values click on values now
all numbers will not work on ran between formula now it
will work like values we have to do same thing copy it and
paste it in special and paste values you have to do both
you can see the value here, but before this, what happens
is that the formula is visible, like I do ctrl z here, it
will be difficult, I will show you
the next one, I will show you in the salary one, look at
this, what did we put here, we put ran with will, ran with
will, but it will not be visible here, because we have
treated it like a value by specifying it, so we will know
that the serial number has become the account number, the
employee
ID has become the name, I will put it in the middle, it is
very good, what to do after this, after this we have to do
that the basic salary that we have to write, so I write the
basic salary here, and you know that the basic salary is
also a grade, like it is
a grade from 37000 to 51000, so what we will do is that we
will use the random between formula and for these
employees, the salary will be generated from 37000 to
51000, so here I write the basic salary and what we have to
do below is that we have to apply the between formula, so I
have written here equals to rent between and I have made it
double click and after that the botup is asking here, so I
have told it the pay grade that 37600 to 57700, I have put
coma here and after that I have
57700 on the top, after doing this, what you have to do is
enter it, you will see that this salary has come, 54148,
now what I do to the bottom, I drag it, so what will it do,
it will show me all the things from 37600 to 57700, from
here I make its size a little smaller and whatever my data
is, what do I do, I make it smaller from here, okay, my
employee id has become, after that
my name has become and it has become a basic salary, I also
write it on the side, now it is fine, now what I have to
do, now I have to do that after the basic salary, there is
a thing called dearness allowance, now it may be different
that in your city there is less inflation, so you get less
there, because if you are in that district, like I can say
that in a big city like Mumbai, you get more inflation
because there is more inflation, so what we have to write
here, we have to write the
of dearness allowance, so
I have written dearness allowance here and in which I have
to write, I have to write it in the percentage, now what
you have to do, you have to take so much of dearness
allowance, you have to select all of them and from here you
have to change its format, like I take
a percentage, so when it varies from city to city, but we
assume that it will be 16%, so what we did is that here we
took 16% and all the employees get 16%, so we directed it
like this from here, so what you have to do now, now you
have to do that we have to take out dearness allowance,
what does it mean to take out dearness allowance, that But
you have to remember one thing here, some values have come
after the decimal, so what we have to do is, what will
happen to the value after the decimal,
our net result will come out, what can it be, it can be
impacted, so what we will do here, we will use the round
function, so what you have to do for the round function,
like this is our formula, what will the round function do,
it will remove the value of my after, we have seen this
round function before, so what we have to do is, go to the
formula bar and write round in front of the formula bar,
and when you double click on the round, it will show you
that first put the number and then put
digit, so this is my number, after that I put the comma and
put the digit that I want up to 0 digit, what does 0 digit
mean, that whatever value is written after the decimal, it
will move away from here, so if I enter here, then what has
come, 7360 has come, now I will apply the same thing on the
whole,
so whatever value was after the decimal, it moved away from
there, so we understood that DA is fine, now one thing is
house rent allowances, this is also available, so what we
have done, we have written HRA here, one second, house rent
allowances, I have assumed that every employee will get
this, 4000, so I put 4000 as a number, if you want, you can
also do this, that you can put the percentage, like we did
in DA, we put the percentage in DA, now we have understood
DA, now what are the things
than this, these are the things that you get, like DA, HRA,
what else you get, you also get PF, so what is PF, like I
write here, what is the provident fund, so I write PF here,
now how much PF is this, assume that you get this PF,
anything 1250 or 1300, I am taking a random number here,
so you can take it, now I drag it down, now what you have
to do after this, you have to total , you have to total
that how much is your gross salary, how much is the total
of DA, HRA and PF, so what are you going to do here, you
write the name in the column here, gross salary, now this
gross salary, now you have to do in this simple you have to
do this whatever my DA is percentage
DA, HRA, PF and B6LD you have to plus all of them you can
use the function of AUTOSUM if you don't want to use the
function of AUTOSUM then you have to select them
individually and plus them
I am writing here equals to then I have to select DA then I
have to select HRA then I have to select PF so I have
entered so my total salary is
Rahul Rohila because it is a coincidence so you have to
drag it down to 58195 and this will be the total salary of
everyone
now I know the gross salary but there is a deduction like
medical expense is deducted your GPF account is deducted or
the food you eat there is deducted or the government takes
the income tax so all these things are there so let's do
all these things here so we have to make a column so first
we said that some money is deducted from my GPF some money
is deducted from my income tax so I am writing here income
tax increase the size of the column then I said other than
income tax the food I eat
is also deducted and the medical expense is also deducted
so you have done all these things now see here how much
your GPF is deducted so you can consider this as any amount
it is in percentage so you have to make a percentage column
so if you can make it then you can make it
I am not making you here
I have shown you in DMA
I am not making you
I am not making you are not making you are not making you
are not making you But if it can be different here, then
you can do it separately. Food is cut by 1000 rupees. So
what did I do to 1000 rupees? I dragged it down here. Then
if you are cutting medical for 1250 rupees, then I wrote
this thing too. I dragged it down here. After doing so many
things, what do you have to do? You have to get this
deduction.
It was a gross salary.
We have to find out how much total money is being deducted.
So here I write this thing.
Total deduction.
So you wrote total deduction.
In total deduction, you have to add GPF, income tax, food
and medical.
All this money has to be added.
So I put equals here.
So I added basic salary, DE, HRE, PF.
I added basic salary, DE, HRE, PF.
I am going to do the same thing here.
I will add GPF, income tax, food and medical.
So I added GPF, plus, income tax, plus, food, plus,
medical, plus, food, plus, medical, plus, food, plus,
medical, plus, GPF,
gross salary.
So I write net salary.
Now you have to add
You have to deduct minus from your gross salary, then you
will get a net amount, so I put an equation here, I wrote
gross salary and here I put a minus sign and deducted the
total, and I did enter, so I got to know that there was a
salary of Rs. 60,000 and a salary of Rs. 47,000, how much
did I get by deducting it, I got 51,952, now it will vary
for everyone, see.
If I talk about the net salary of Raghav, the net salary of
Raghav is Rs. 64,799, if I talk about the salary of Hari
Om, the net salary of Hari Om is Rs. 45,821.
So, this is the net salary of Hari Om, but when you get a
salary in hand, it is very less, so there are many things
in it that the company spends on you, plus there are some
deductions that you get facility, so its money gets
spoiled, so we understood how to calculate the net salary
from the basic salary.
Now, we have to prepare the basic salary, so I will take
you to sheet number 2, and you will see one thing here that
we have already made it, now what to do in this, increase
the size a little, here it is written that the serial
number, employee name, account number, and then on basic,
D, H, R, A, P, F, G, P, F, income tax, these are all the
same things that we have seen there, so what you have to do
now, like you have to put the name of the employee here or
put the serial number, like it is
here is the serial number of the employee, we want that we
have to work on one employee, the rest we will change the
serial number, so all those things will be done, so now
what you have to do, do you remember there was a formula
for Vlookup, so now we are going to apply the same formula
here, Vlookup formula, now how will it work here, let's see
this thing, so our employee name, what we have to do is
that we have to get the name of the employee from there,
which is the list we have, so what
have to do is, like here you will change the serial number,
so the employee's name will also change, the account number
will also change, this is the only thing we have to do, so
first of all we put here, we put equals to Vlookup, it came
here, we double clicked it, it is saying, tell me the
lookup value, we put the serial number in the lookup value,
we want to change this thing, so the rest of the things
will change, it will be easy for us, it is easier than the
name, we know who is on serial
20, serial number 15, serial number 12, after that it asks
me the table array, now what is the table array, we have to
select it completely, we go to sheet number 1 from here,
whatever data I have, I have selected all this data, and
after selecting it, it came to sheet number 2, now one
thing to remember is that when you go to sheet number 2,
it will take it by default, so what you have to do from
sheet 2, it will do sheet 1, because we are taking data
from sheet 1, so I back it and write sheet 1, after this it
is asking for the column index number, now we have to tell
what will be the column index number, so what we do here
is, because we want the name, so we see which is the name
number, 1, 2, 3, 4, it was called 1 for A, 2 for B, 3 for
C, 4 for D,
so we have to go back to sheet number 2, now when it comes
by default, you have to remove it, and what you will put
here, you will put 4, sorry, not sheet number 2, sheet
number 1, and here I have put the index number, column
index number 4, so I put 4 here, and after that you have to
close it,
so I put 4 here, and enter it, so here it was taking by
default, so we have to remember that we do not have to give
sheet 1, but N is still coming, so if I put 1 here, then
what happened here, Rahul Rohila came, and if I change from
here, so here Jishan Khan happened.
I am trying to see column number 4 of sheet 1.
So many times this thing happens.
If you do not take, then you need to put it.
By the way, most of the cases you do not need to put
So look here, two put Jishan Khan
What we want
Now what is it that one employee's name is changing that I
put two, so Jishan Khan showed below, okay, put three then
see, someone's name will also come like I put three, then
the mark came
I say that when I change the serial number here then all
the other details also change
Now to change all the other details what do we have to do
that this formula of our VLOOKUP will have to be applied to
everyone and what do we have to do with it we only have to
change the column number there okay So let's see here, I
copy it from here copy from here and paste it in everyone
like I have it here one second
Let's not do it from here
Let's pick it up from here
Let's copy it from here and I pasted it here too
No, he is taking it wrong one second what is he doing it is
fine here it happened here, after that what did we do one
second
if we change it here four Okay, something went wrong in the
formula because this E4 came it will have to be removed
from here
Raghav is fine and now what did we do we copied it
and after copying we went here and we pasted it and we
pasted it everywhere
Now I will tell you what we have to do like in HRA
PF and in gross salary we are going to do it everywhere
Now what you have to do here you just have to change the
column number and all this work will be done now you feel
that I don't know what I am doing but I will tell you what
I am doing now you have to do this you have to see in the
column name which column is the account number you saw the
account number is on the second column so you have to go
here and you have to go to the account number section and
put 2 here, enter it, if you change 3 here, the name will
also change, the account number
also change, it is done 166, in the same way you have to
see which are the columns of basic pay, DA, HRA and PF, so
you just have to see that thing, so I go here and go to
this sheet, I saw which is the basic pay, it is 5, which is
DA, 7, 8 for HRA, 9 for PF, 5, 7, 8, 9, so I go here, I
wrote it here 5, enter, here I wrote