How to Create Professional Attendance Tracker in Excel (Free Template)
937 views
Feb 21, 2025
Learn how to create a professional attendance tracker in Excel with this step-by-step tutorial! Easily track employee or student attendance with automated formulas and a user-friendly design. Download the free template and customize it to fit your needs. Perfect for HR, teachers, and managers! Download Template: https://excelweez.com/professional-excel-attendance-tracker-template/
View Video Transcript
0:00
hey everyone welcome back to our Channel
0:03
today I'm showing you how to create a
0:05
professional H tracker in Exel like this
0:09
the one that has a percentage attendance
0:13
that's going to change in as you as you
0:18
tick in your check
0:21
boxes stick around as I walk you through
0:23
each step from setting up the layout to
0:26
adding Dynamic formulas and conditional
0:28
formatting let's get get
0:32
started we will first begin by creating
0:35
another a new sheet here we can rename
0:39
it
0:40
to attendance
0:43
tracker since we are trying
0:46
to we are trying to look uh to actually
0:49
create a professional attendance tracker
0:52
so the first thing that we need to do is
0:54
ensure that we import uh some data you
0:58
could type in or
1:01
just uh import it from somewhere else
1:05
that you have and then let's uh start
1:08
with our day from
1:11
Sunday to Monday
1:15
T
1:16
Tuesday Wednesday Thursday Friday
1:20
Saturday and
1:23
Saturday we can have
1:27
this in green
1:31
uh once we have this we can
1:33
actually add borders into
1:41
it just like
1:44
this and then since this is just a whole
1:47
week we could have we could insert we
1:51
can insert a text box you can actually
1:54
use any other type of formatting but for
1:57
this particular
2:03
for this
2:05
particular video I'm going to be
2:07
inserting our Tex text box text
2:18
box so we'll be inserting a text box
2:38
let's add
2:41
another row here to make it uh more
2:55
visible yes once that's done we cut can
3:00
type in this is week
3:05
one and we can
3:08
have
3:10
yes so once that's
3:16
done we can resize all this
3:19
[Music]
3:24
to just to make it more presentable
3:28
and place it into our
3:35
eyes so once we have that it's easy
3:38
since we'll
3:39
just
3:41
uh select
3:46
this no
3:49
actually we're just going to select the
3:55
cells
3:57
copy and duplicate it
4:02
for three more
4:28
times for
5:09
let insert another text box
5:13
here for now we we
5:23
two and we can now have this is as we
5:28
to
5:58
for
6:28
e
6:58
e
7:28
e e
8:20
so that's it with uh the layout of our
8:23
attendance tracker now we'll move on to
8:26
adding our attendance here
8:30
and our percentage in
8:54
attendance this will have our totals
8:57
here since since this is where we'll
8:59
have put the percentages and just the
9:02
normal
9:11
number then let's select this and add
9:14
borders just like the the other
9:24
columns so once that's done we the next
9:28
thing that we going to do we're just
9:29
going to add check boxes on
9:34
our on our week one to week
9:39
four select
9:44
it go to insert and click on check boxes
9:48
and you'll have your check boxes like
9:51
this you can you can check a few
9:59
okay once once that's done what the next
10:03
thing that you're going to do we'll try
10:06
[Music]
10:08
and calculate the attendance we are
10:10
going to use the count if formula
10:14
clicking your equal
10:18
sign go with count
10:23
if and then now select the range of your
10:26
weeks
10:31
comma and then if this is true that we
10:35
want the number that's going to be
10:36
counted and this gives you four so what
10:40
you need to do is just uh drag to fill
10:43
to every
10:45
other to every other row and this is
10:48
what will happen so if you keep checking
10:50
in other boxes this will ensure that you
10:54
have everything calculated automatically
10:57
then let's calculate theend
11:00
the percentage in attendance this
11:07
will equal sign and then now we have to
11:11
select the first cell of attendance and
11:14
then divide it by count
11:20
a count a and then now select the value
11:23
the value will be the number of days in
11:27
the weeks
11:30
and then close your parentheses and hit
11:33
enter you'll have a decimal number but
11:38
this time we'll just have to change it
11:40
into percentages under the Home tab and
11:44
then over the number just click on the
11:46
percentage and you'll have it as a
11:48
percentage just like we did in the
11:50
attendance click and
11:53
drag to
11:56
fill and there you go
11:59
now what's left is
12:01
to to do a little bit of
12:05
formatting select
12:09
your your check boxes go to conditional
12:13
formatting highlight cells and click on
12:17
equal
12:19
to say when it's true you want to fill
12:23
this with green green fill with dark
12:26
green text click on okay
12:30
move on to condition of formatting once
12:32
again equal to when now it's
12:37
false yes fill it with the red fill
12:42
light red yes and click on okay so when
12:46
you
12:47
keep this is what is going to
12:50
happen once that's done come
12:55
here
12:57
select your percentage in
13:00
attendance go to conditional formatting
13:04
go to datab and click on more
13:08
rules click here on the maximum Ure that
13:11
it's a number and make sure it's one
13:14
number move on to solid fi gradient
13:19
fi choose choose your color to be you
13:22
can choose whatever color you wish for
13:24
this particular video we go with color
13:27
green and then click on okay
13:30
okay okay so you can see your attendance
13:34
is in
13:37
percentages as
13:39
you as you click on it so this is how
13:43
you you create an attendant a
13:45
professional attendance uh tracker you
13:47
only need two formulas that's the count
13:50
a and count if to just uh work on your
13:54
attendance and the percentage in
13:56
attendance and that's it so if you like
13:59
this video
14:01
please uh like And subscribe and we'll
14:05
see you again in the next
14:08
video bye
#Teaching & Classroom Resources