Up next in 10
The excel tutorial every HR employee should watch! | Excel Tips for HR Professionals
Struggling to manage data, streamline tasks, or analyze reports in Excel? 🚀 This tutorial is designed specifically for HR professionals! Discover the essential Excel skills every HR employee needs to save time, stay organized, and elevate their workflow.
In this video, you'll learn:
How to manage employee data like a pro with sorting and filtering.
Tips for creating attendance trackers, salary sheets, and performance dashboards.
Powerful Excel formulas every HR professional should master.
Automation tricks to simplify repetitive HR tasks and improve efficiency.
📂 Download Excel template: https://drive.google.com/drive/folders/1MIlqVDSkhFqOInMhptU_weoABgyv9NRM?usp=sharing
📘 Ready to become an Excel expert? Check out our comprehensive course: https://bit.ly/38Jyu3p
Whether you’re new to Excel or looking to refine your skills, this tutorial is tailored to help HR professionals boost their productivity and accuracy.
Excel for HR, HR Excel tutorial, Excel tips for HR professionals, Excel tricks for HR, managing HR data in Excel, employee data in Excel, Excel formulas for HR, HR dashboards, Excel automation for HR, time-saving Excel tips for HR
#ExcelForHR #ExcelTutorial #HRTools #ExcelTips #ExcelSkills #BoostProductivity #LearnExcel
Show More Show Less View Video Transcript
0:00
hello and welcome to tutorials point now
0:02
this video is specifically designed for
0:05
HR professionals managers or just anyone
0:08
working with employee data in this video
0:10
we will explore how to efficiently
0:13
analyze employee data sets using Excel
0:15
from calculating the number of employees
0:17
in specific departments to determining
0:20
total and average salaries dynamically
0:22
we will cover the essential formulas
0:24
that can save you time and enhance your
0:27
workflow so without wasting time let's
0:29
let get started so here we have this
0:32
example data set of employee ID first
0:35
name last name email gender Department
0:38
salary and Country now this is a table
0:42
right here and if we click on table
0:45
design you will see I have named this
0:47
table as data so that we can use it in
0:50
our functions all right so I click on
0:53
this sheet here sheet number one and
0:56
this is what we have to do now first we
0:58
have to list all the departments and
1:00
here I have written the name of the
1:02
functions that we are going to use so to
1:05
list all the Departments first way is to
1:08
Simply go back to the table and just
1:11
copy all of this column and then go back
1:14
to the sheet number one and paste it
1:16
here but that is something we do not
1:19
want because we only want the name of
1:21
the department once and if we go back to
1:23
the table as you can see the department
1:25
name is repeating training is many times
1:27
and so are the other departments so
1:30
instead of copying and pasting here we
1:32
will use the unique function so I will
1:35
write here equals unique parenthesis now
1:40
inside the unique function we will write
1:42
the name of our table which we have
1:44
already written that is data now inside
1:47
the data we have a column as you can see
1:50
we have all the names of the column and
1:52
we'll be needing this department column
1:54
and I'll just close the parenthesis and
1:58
hit on enter
2:00
this will fetch all the names of all the
2:03
Departments and we'll make sure that no
2:06
name is
2:07
repeated so we have listed all the
2:10
department names here right but it'll be
2:12
much better if we write this in a sorted
2:16
way for example we want the department
2:19
starting with letter A first then B and
2:21
then so on so the accounting should be
2:23
actually here right to do this we can
2:26
simply use the sort function so I'm
2:28
going to enter entirely wrap our this
2:31
unique function inside the sort function
2:33
so here right after this equals I'll
2:35
write sort and then open parenthesis and
2:39
then here I'll just close this
2:41
parenthesis and hit enter and you can
2:44
name and you can see that the department
2:47
names have been sorted so we have
2:49
completed this first task of listing all
2:52
the Departments right so let me just
2:54
change that color to Green so that we
2:56
can identify what we have just done so
2:58
we have done the first one now we have
3:01
the second one here we have to list the
3:03
number of employees in each department
3:06
and we are going to use the count ifs
3:08
function for this so in this column we
3:11
want to specify how many employees are
3:14
in the accounting department then how
3:16
many employees are in the business
3:19
development department and so on so here
3:22
I'll write the formula
3:26
equals count ifs and then parentheses is
3:30
now here I'll write the name of my table
3:32
which is the data table now inside the
3:35
data table we have the department column
3:38
and we want to calculate for each
3:40
department so I'll write here a comma
3:43
and then I'll write the name of the
3:44
department which is this one so I'll
3:46
click on this cell if I close the
3:49
parentheses and hit enter it says that
3:52
the accounting has number of employees
3:54
97 we can just drag and drop this to
3:57
apply the same formula for all the
3:59
Departments
4:00
and we have number of employees in all
4:02
the Departments right but a more shorter
4:05
way is to just go back and here in this
4:07
formula instead of just specifying one
4:11
Department I'll just delete it and I
4:13
will specify this all department so I'll
4:16
select this range as you can see it has
4:19
selected this range and if I click on
4:21
enter as you can see it gives us the
4:24
number of employees in each of these
4:27
departments so we have
4:30
done this one also let me just change
4:33
its color now is the number three one we
4:36
want to list the total salary of each
4:39
department for this we are going to use
4:42
the sum ifs function so here in this
4:45
total salary column I will write this
4:47
formula I'll write
4:50
equals sum ifs and then parentheses then
4:54
I'll write the name of the table now
4:56
inside the name of the table we are
4:58
looking for the salary column and after
5:02
that for that salary column we are going
5:05
to look for the criteria range which is
5:07
going to be again the name of our table
5:09
and in our table we are going to look
5:11
for the Department column all right and
5:14
then comma criteria one is going to be
5:17
we are looking for basically all of
5:20
these departments so instead of
5:21
selecting just one we can select all of
5:24
these add ones and then close the
5:26
parenthesis and hit enter and as you you
5:29
can see we have the total salaries of
5:31
each department you can just simply
5:34
select all of them and quickly change
5:36
their currency something like this so
5:38
they are in dollars so this is the total
5:40
salary of the accounting department this
5:42
is the total salary of Business
5:44
Development Department and so on so we
5:47
have done the third one as well let me
5:49
just take it out then we have the fourth
5:52
one now here we want to list the average
5:55
salary of each department and for this
5:58
we are going to use the the average ifs
6:01
function so here in the average salary
6:03
column I'm going to write the formula
6:06
equals average fs and then parenthesis
6:12
now inside this we are going to write
6:14
the average range so we are looking for
6:17
our data table and inside the data table
6:20
we have the salary column right and then
6:24
comma for the criteria range we are
6:26
looking again at our our data table and
6:29
inside the data table we have the
6:32
department column all right so after
6:35
that we are going to just select the
6:37
names of department so instead of
6:39
selecting one I will select all of them
6:41
and then I will just close the
6:43
parenthesis and hit enter and as you can
6:46
see this calculates the average salary
6:49
for each department we can just select
6:51
these and change them to the currency as
6:54
well also we can change the decimal
6:57
places if we want let's say I'll just
6:59
decrease them and let's have whole
7:00
numbers here so this is the average
7:03
salary for accounting department this is
7:05
the average salary for business
7:06
development department and so on and we
7:09
have calculated the fourth here as well
7:12
so I'll just take it out so we have
7:15
completed our sheet one tasks now let's
7:18
go to this sheet number two here now
7:21
here we have to do this one so we have
7:23
to list all the employees with greater
7:27
than 100,000 salary
7:30
all right so $100,000 we have to list
7:32
all the employees right so first thing
7:34
is we want all the headers all right so
7:38
I'm just going to get into the data and
7:39
we can just simply copy paste but again
7:42
you are an you are an employee you're
7:44
are a professional you don't want to
7:45
copy and paste things right so instead
7:47
of copy and pasting I'll just go back to
7:48
the sheet and we will just import all of
7:50
our headers let's say here to import all
7:53
of our table headers we'll simply use
7:55
the formula equals and then our data
7:58
table inside our data table we have the
8:01
headers so if we scroll here as you can
8:03
see we have this header option I'll just
8:06
double click on it and if we just close
8:09
the bracket and hit enter as you can see
8:12
we have all of the headers so let me
8:15
just adjust
8:16
them something like this now after
8:20
getting all of this what we want is we
8:23
want to list all the employees that have
8:25
greater than
8:26
$100,000 salary and for this we are
8:28
going to use the filter function so here
8:32
I'm going to write equals and then I'm
8:35
going to write the formula which is
8:36
filter and then parenthesis now inside
8:39
the filter the first is the array so for
8:42
the array we will pass our whole data so
8:44
which is the data table of course so we
8:46
have named it as data so just data and
8:49
then column and then comma and in the
8:51
include we are looking for our data
8:54
table inside our data table we are
8:57
looking for the salary column and in the
9:00
salary column we are looking for those
9:03
only that are greater than
9:07
$100,000 so let me just close the
9:09
parenthesis and hit enter and as you can
9:12
see this is the list of all the
9:15
employees that have a salary of greater
9:18
than or that have a salary of greater
9:21
than $100,000 so here you can just
9:23
select this column and then you can
9:25
quickly format it in the currency and as
9:27
you can see all of these employees which
9:30
are about if we count this these are
9:33
about 251 employees and all of these 251
9:37
employees have a salary of more than
9:42
$100,000 so we have done this one let me
9:44
just quickly green this out so this
9:47
means we have completed another dusk now
9:49
let's go to the third sheet here we have
9:53
another small to what is it it says list
9:57
all the female employees with greater
9:59
than
10:00
$100,000 of salary so here we have
10:03
listed all the employees irrespective of
10:05
their agenda that have greater than
10:07
$100,000 but in the sheet number three
10:10
we want to list out the female employees
10:12
only with greater than
10:14
$100,000 so again first we want to have
10:17
the headers remember earlier how we
10:19
imported all the headers simply equals
10:21
name of our table inside the table we
10:24
have hash and then headers and then just
10:27
close the bracket and hit enter and we
10:30
have all the headers next what we want
10:33
is we want all the female employees with
10:36
greater than
10:37
$100,000 of salary here we are going to
10:40
again use the filter function and the
10:42
star and I'll show you how to use it so
10:44
here I'm going to write the formula
10:47
equals filter and then parenthesis now
10:50
first we have to pass the array which is
10:52
our entire table so I'll just write the
10:54
name of our table which is data and then
10:56
comma and then we have to look for
10:58
include now here what we want is earlier
11:02
what we did was we wrote data and inside
11:05
the data table we have the salary column
11:09
and inside the salary column we are
11:11
looking for those people that have a
11:13
greater than salary of
11:15
$100,000 and if we hit enter we have all
11:18
the employees irrespective of gender but
11:20
here we want only the female employees
11:22
so we need to
11:24
basically modify our formula so here
11:28
just simply writing this one we have to
11:30
write multiple includes so I'm just
11:33
going to include a parenthesis here
11:36
something like this before this data
11:39
what I'll do is I'll write parenthesis
11:42
and then here again one parenthesis and
11:44
then we have to write another include
11:46
for this formula as well so to write
11:49
multiple conditions in the formula like
11:51
this we need to use the star sign here
11:54
and then again we need to use more
11:56
brackets now inside these parentheses we
11:59
we are going to write our another
12:01
criteria of include which is that for in
12:04
this our data table we are looking for
12:07
the gender so we are looking for the
12:10
gender and that gender should be equal
12:13
to female so I'll write here female and
12:17
then as you can see this is the entire
12:19
formula if I hit on enter as you can see
12:22
we have all the female employees that
12:24
have a salary of greater than $100,000
12:27
you can quickly just modify this and
12:31
select the currency and as you can see
12:33
this is the list of all the employees
12:36
that have a salary of greater than
12:39
$100,000 and these employees are females
12:42
so if we count those employees these are
12:44
about 122 female employees that have a
12:46
salary of greater than
12:50
$100,000 so that is enough for this
12:53
video now in this video you have learned
12:55
various formulas uh like salt unique
12:59
count FS average FS sum fs and filter
13:02
and how to use the star to combine
13:05
multiple criterias all right now the
13:08
link to download this practice sheet is
13:11
available in the description below I
13:13
hope you enjoyed and learned from this
13:15
video make sure to like this one share
13:18
it with your friends and I will see you
13:20
in the next video
#Business Education
#Software
#Education