Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:01
This is the instruction video for labs meant to be done at the end of Model six,
00:07
and in these labs you're given this table, so it's a big one.
00:14
It contains
00:16
monthly quotas for three salespeople for entire year. So from January to December,
00:23
and if you look at the details, you see that there is a quota and there is a revenue,
00:30
and your task would be to sort this
00:35
Ah, Purcell's person
00:38
and then to, um,
00:42
for months,
00:43
this as a table.
00:46
And then, after you have formative, there's a table
00:49
you should form at the conditional formatting off cells in Revenue column in such a way that if it's if the revenue is higher than quota, it should be green background, dark green background with light green letters.
01:07
If it's, um,
01:08
a lower then, um,
01:11
revenues lower than quota. It should be dark red background with pink letters.
01:19
And if ah, the revenue is exactly the same as the Pota, it should be just Aah! Aah! Black bold letters on a white background
01:32
s. So, um, uh, I again recommend you
01:36
to do it by yourself and use this video or step by step instructions in pdf file that is also in resource is tab of this
01:46
course on Lee as help. If you get stuck in, you don't know how to continue.
01:53
I don't recommend that you go through the motions off the
01:57
step by step instructions or to go through this video first and then just do it by yourself the same way I did it. So try to find a way, if you can, to do it by yourself.
02:12
Uh so, uh,
02:14
whatever you have decided, let's go on with instructions. So first, we're going to do the sorting, and we're going to go to date a tab and sort things.
02:24
So it has Heather's, and we're going to sort by cells person from a to Zed.
02:30
And here it is.
02:31
And now we're going to go to home and we say Former stable and we choose whatever we want. So let's say we choose this one. The light one without alternating colors.
02:47
Uh, this is the range. Yeah, Row 37.
02:53
It has Heather's. So it's like this.
02:57
And now we have to form it, this one.
03:00
So let's go again to home
03:02
Tab
03:04
and we go to conditional formatting
03:07
and we're going to create some rules here.
03:10
So we're going to use a formula to determine which sells informant,
03:16
and we're going to use that if
03:20
function. So we're going to say
03:22
if
03:23
this cell
03:25
is
03:28
I'm sorry.
03:32
So if this cell
03:37
is
03:40
ah greater,
03:44
then this one. So if revenue is greater than quota,
03:49
then true otherwise fools.
03:53
Why this? Because when it's
03:55
that this condition is met,
03:58
then we're going to execute the format. Now what? We're going to be lead Here. Are these strings
04:06
from the
04:08
so references? Why? Because we're going to copy this all the way down to the end of this column.
04:15
Just the former thing. And we don't want this to refer to the same cell every time.
04:21
Also, when you want to
04:26
remove these strings, you have to actually use your mouths and click there and do the backspace shortly regarding on where you click. Because if you use the keys on your keyboard is going to insert the reference to the cells you have selected with keys. So you have to use this,
04:44
and now we have formula, and now we're going to do the four month. So we said that the film is going to be dark rain and the front is going to be
04:55
light green
04:57
and let's say Came
05:02
and, uh,
05:03
Just do it together.
05:09
Now
05:10
we go again to conditional formatting
05:13
and at the new rule, tow this cell
05:15
again using a formula,
05:17
and we're going toe go again with if
05:23
open brackets
05:25
and say this is equal
05:28
to this
05:30
and again
05:31
true
05:32
fools, clothes, records
05:35
and the format should be on Lee
05:39
Bold.
05:41
So we're not going to do anything else except balding this
05:46
text, because this is what we want to do.
05:50
And again, we're going to delete these dollar signs
05:57
because what they do is they
05:59
point the reference on Lee, too.
06:03
And of course, we're going to correct the typo.
06:09
So it's okay.
06:11
And now the third
06:15
rule
06:16
off conditional former thing
06:18
is going to be if
06:21
this cell
06:25
is smaller
06:28
than this one.
06:32
True
06:33
falls
06:35
clothes, and again we delete
06:39
these.
06:42
It's a bit
06:45
tedious.
06:46
You have to do four times every for every rule,
06:50
and now we go to former TTE and the front is going to be
06:55
some kind of pink. So let's let's true something like
07:00
this, for example,
07:02
and the Phil is going to be their HQ red
07:08
and is going to look like this.
07:11
No, we click. Okay. And for this one, this is the one we were waiting for. So this one has done what we wanted.
07:17
Now we go to this marker
07:19
we selected with right miles button
07:24
and we drag you told the way down
07:28
and we choose fill form thing only,
07:31
and we got what we needed. So we have these
07:35
where they failed on revenue
07:39
and those were day overshot. And they're somewhere. They made absolutely the same result as the one
07:47
given to them by the management.
07:49
So at the end, this is how they should look.
07:54
And if you have ah,
07:57
skipped to the end, you can see how
08:01
it should be formatted. So now it's up to you. Do it yourself. Thank you.

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