Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
Okay. Um, what you see here is something that I have created before
00:06
and this is a good example Off monthly traveling report that being pleased of a company that are using company car
00:15
I have to fill but the end of every month. So this is something that has is pretty much standard form. You have this part here where you enter your mileage. So you have a date. You have a day. You have starting time.
00:31
You have the number on your dashboard which shows how many kilometers was on their support when you started Then when you ended it at the end of the day also starting time in the morning and in time in the evening.
00:48
And then you have ah, kill me too has traveled.
00:52
Ah, and this is pretty fine formula. And then you have ah special column here that in which simply enters how many of those kilometres have been used for private use. For example, typical thing in companies is that when you're traveling the morning to the office
01:07
or traveling back from the office home, those kilometres,
01:12
this mileage is your private mileage. So you entered there and then the total time of usage is something that you enter.
01:21
It's not ending time, minor starting time. It's probably if you have spent on in your car, like two hours or two and 1/2 hours, just enter there.
01:30
And then if you have been traveling with the company car and you have paid
01:36
purchase gas and paid with the company car, do you have to enter hell, the volume in leaders. If you're in Europe, then how much did you pay?
01:46
And if you're throwing from country to country, it can be in different currencies of terrorism.
01:51
Comb for currency and so on and so on. And here there is some calculations, so let's go back to the sheet one and let's recreate this report
02:04
and we're going to do this by copying the text, and I'm going to show you, in a way, how to do this. So let's say that we just copy this cell here, and then we moved to Sheet one and say, OK, let's put it here
02:21
because if you have noticed the comedy was empty, we're just going to put well years. We're not going to put the formal thing
02:28
and then let's go here on Dhe selected this cell
02:34
and noticing that I'm not selecting all these cells together. And this is why some of these cells emerged. And I'm going to get back to that immediately. So let's go here
02:45
now. We didn't want to do that. We wanted to paste just the failures.
02:51
And then we go to
02:55
sell T womb
02:58
and copy
03:02
here. Well, you as well.
03:07
And then we can go again to the sheet final
03:12
and because the mileage
03:16
we go back here
03:19
So here's where we put mileage
03:24
and then let's go here every copy fuel
03:38
and let's not go to the right anymore.
03:42
Just, for example, copy all of these cells because here we don't have any
03:47
merging. So we control, see,
03:52
and then we click here on repress again values.
03:59
And, uh,
04:00
now let's do some form of thing, which is useful thing for you to learn.
04:06
So,
04:08
for example, this
04:11
this is something like
04:13
this. Let's not write too much fully the colors. Let's say that this was the text was wide.
04:20
And then let's look at the side that we're just going to do it like this,
04:27
and then it's not going to be this fund, but, uh,
04:31
this one
04:33
and you see it it goes like this informant, just this cell on and nothing else is visible.
04:40
So what we want to do is select all of these cells
04:45
and then you have here at the home tab. You have deception emerging center. We don't want to center it. We're going to Toby aligned to the left,
04:54
and we click on months traveling report and we get this.
04:59
And let's do now the same thing for
05:01
the company name.
05:03
So let's again changed fund.
05:09
And let's say it's white and the background waas
05:14
like this
05:15
on. Let's make it bigger,
05:20
Okay? And now it's still emerge in center
05:27
on Dhe. Then let's do the same thing with
05:31
this SL. Here
05:35
go. Let's say there's let's make it.
05:46
Then we select these two cells.
05:49
Sorry, and we merged them.
05:55
Yeah, this is how it's working.
05:57
So we got this
06:00
heather off this.
06:02
So let's, uh, it's
06:04
make this comb smaller because it's here just for the cosmetic purposes. It doesn't obtained anything
06:13
and look here,
06:15
but
06:15
and the same thing is happening here
06:19
and now
06:21
let's click here. Double click here. So we moved to this ruler part of the
06:30
please plea.
06:32
And we go where the comedy ends. We go here and the way to look for the change in the Pointer and it looks like this. We double click here
06:44
and it automatically sets the wit off this column to match the text in it.
06:49
And we do it for a while
06:53
off these
06:59
and
07:00
here, a cz well
07:14
and so on and so on. So this is how it looks.
07:17
And let's go with former thing of the rest of the
07:21
So
07:23
we have this mileage and the fuel as well. So okay, I made a mistake here.
07:30
First of all, I made this too small, So let's make them
07:35
bigger.
07:36
And then this one was supposed to be dark green.
07:42
So something like this
07:45
and then this one is also in a different fund,
07:51
so but it's not so big in terms. Off
07:56
size.
07:57
Let's say it's 24
08:00
and now it's, uh, the blue and the text is white
08:05
and again, we're going to select these cells and we're going to just merge them
08:11
here. We're going to do the same thing, so it's going to be merged. Then we're going to click on this cell, we're going to click informant, painter and click on this cell, and we get this and then we just have to change the background
08:26
and we get it in orange like it's supposed to be.
08:31
And, uh,
08:33
And now let's look at what we can't else conduce. So we then
08:39
so, like, these cells make this bold,
08:43
changed the fund
08:48
and, uh,
08:50
make it, uh,
08:54
like this. Let's see what we did here. No, it should be
08:58
a little bit
09:00
less dark. So, like this. And then form a painter. Quicken this.
09:07
And then, uh,
09:09
I was just change.
09:13
No, let's not do it like this. Let's select thes cells.
09:18
Let's go here
09:20
on Dhe again. Change
09:24
this. So you see, these things take a little time,
09:28
but it's worth the trouble, because now it looks much, much nicer.
09:35
You again
09:37
change the wit of cons because you have changed the typeface too different typeface, and we headed bald. So it's now
09:46
slightly more space. Taking here is well,
09:52
so we fix this
10:01
now. We can't do this. We have to make it bigger. So
10:07
it's like this. So this is pretty much how it looks.
10:11
And then we have
10:13
these gray
10:15
areas Richard there to make
10:18
the whole ting look more like
10:24
a table. So let's click, for example, here
10:28
and here is well
10:31
And then let's select the cells and then we copy them and then paste them
10:37
and then paste them and then lets you like them again all three together
10:43
because we are going to waste less dying like this.
10:48
And now
10:50
just one more, I think.
10:54
And that's pretty much it. So let's look here where are ending in 37. So
11:01
we have to select just one more
11:05
and put it here. So now it looks nice
11:09
and it has 31. So you select oldies you Sorry we have to press escape first
11:16
and we see like these cells
11:18
and yeah, there is 31 of them and
11:22
that's what we needed.
11:24
So
11:26
let's go. Here s o kilometres traveled
11:31
equals
11:33
two
11:33
ah
11:35
ending kilometers minus starting kilometers. We present there
11:41
We go to that cell again. We go here, wait for the pointer to change,
11:46
then drag this
11:50
and then we just say Phil without formal thing, we want tohave this alternating gray and white
11:56
rose.
11:58
So we get this.
12:00
Nothing's changed here,
12:01
and what we have here is a calculation. Here is amount in local currency,
12:07
which is basically equals
12:11
amount paid,
12:13
multiplied by exchange rate.
12:18
And again we're going to drag this,
12:22
but with the right mouse button click like we did before
12:28
and then Phil without formal thing.
12:31
And here we are. So let's, um let's un's umed this a little so you can see everything
12:39
and here in the month.
12:41
So what we have here a month way have month report
12:46
that this is actually a mistake. So, uh, here we should have
12:52
monthly
12:54
report.
12:58
And then here we have a month.
13:05
So let's, uh
13:07
it's reduced the font size
13:09
a little.
13:13
And then let's, uh, merged these cells.
13:18
Let's click here,
13:20
copied the former thing
13:22
here,
13:26
and then
13:28
what we have here
13:30
here. We have total kilometers,
13:33
so we're going to have here
13:37
ah, equals some off.
13:41
And then this year,
13:46
and because we wanted to be slightly bigger, we're going to merge these cells,
13:54
and then we're going to change this again to a different formed
14:00
and make bold and make it
14:05
something like this
14:07
a little less and we're going to century vertically,
14:11
then sell
14:11
and yeah,
14:13
okay. We forgot to merge it with these cells, but it doesn't matter. This is big enough.
14:20
And then what? We have total time on the road.
14:24
So
14:26
I just copied this so we don't have to
14:31
re type it. So
14:33
based values.
14:37
And then we copied. This is well,
14:45
so we have the student when empty. So it goes here
14:50
and we have one more. Sorry.
14:56
Yeah, money spent
15:00
on DDE.
15:03
We copy this
15:07
here
15:09
and be paced Alia's.
15:13
And then we're going to merge these cells
15:18
to merge these cells
15:24
left here,
15:28
maybe to select these cells and merge them.
15:33
And then we're going to cycle of these cells and merged them well.
15:39
And then total time of the road is going to be equal to some
15:45
off
15:48
here
15:50
and again. We're going to merge these cells,
15:56
and it's going to be equal to
16:00
fuel purchased. So some off
16:03
these
16:08
and again, we do some merging,
16:14
and we had
16:15
your formula, which is going to be some off
16:21
money spent,
16:25
and this is it. And now we're going to just pick the former thing of this one and copied here. Now we can do that because they're not merged. So
16:34
we're going to say that this is
16:38
assistant. It's going to be bald. We're going to increase the size.
16:47
We're going to center it like this. And now we're going to just
16:52
compete this
16:55
format.
16:57
Yeah.
17:00
And, um,
17:03
you're going to
17:06
Yeah, pretty much weakened. But we can do
17:08
is, uh, just make this. So what was
17:12
here? It was, like, 11. It was bold.
17:17
And, uh,
17:19
just
17:22
sorry we can do this on. We can come here
17:27
on dhe. Uh,
17:30
quick. This way
17:32
and so on and so on. So let's not spend more time. Just just copy this foreman to make it look nice
17:42
and then changed color.
17:48
This one is well
17:51
and thes two cells. They're going to be,
17:55
for example,
17:56
arc red.
17:59
And that's it. So we have our
18:03
complete
18:07
It took us quite a lot of time to create it.
18:11
And now we're going to just save as
18:15
let's say that we're saying in a year
18:19
and here we're going to select
18:22
find where is the tape template.
18:26
So Excel Template XL, T X.
18:30
And we're going to say that this is named
18:34
Ah,
18:37
mileage,
18:44
then plate,
18:45
and we're going to save it.
18:48
And now, whenever somebody wants to use it, they can just open it,
18:56
enter the
18:56
name of the month here
18:59
and entered the will use in it. And when they finish, they save it and it's for the current month.
19:06
So this is how the template is created.

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor