6 hours 3 minutes
This is the instruction video for labs meant to be done at the end of Model six,
and in these labs you're given this table, so it's a big one.
monthly quotas for three salespeople for entire year. So from January to December,
and if you look at the details, you see that there is a quota and there is a revenue,
and your task would be to sort this
Ah, Purcell's person
and then to, um,
this as a table.
And then, after you have formative, there's a table
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.
If it's, um,
a lower then, um,
revenues lower than quota. It should be dark red background with pink letters.
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
s. So, um, uh, I again recommend you
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
course on Lee as help. If you get stuck in, you don't know how to continue.
I don't recommend that you go through the motions off the
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.
Uh so, uh,
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.
So it has Heather's, and we're going to sort by cells person from a to Zed.
And here it is.
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.
Uh, this is the range. Yeah, Row 37.
It has Heather's. So it's like this.
And now we have to form it, this one.
So let's go again to home
and we go to conditional formatting
and we're going to create some rules here.
So we're going to use a formula to determine which sells informant,
and we're going to use that if
function. So we're going to say
So if this cell
then this one. So if revenue is greater than quota,
then true otherwise fools.
Why this? Because when it's
that this condition is met,
then we're going to execute the format. Now what? We're going to be lead Here. Are these strings
so references? Why? Because we're going to copy this all the way down to the end of this column.
Just the former thing. And we don't want this to refer to the same cell every time.
Also, when you want to
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,
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
and let's say Came
Just do it together.
we go again to conditional formatting
and at the new rule, tow this cell
again using a formula,
and we're going toe go again with if
and say this is equal
fools, clothes, records
and the format should be on Lee
So we're not going to do anything else except balding this
text, because this is what we want to do.
And again, we're going to delete these dollar signs
because what they do is they
point the reference on Lee, too.
And of course, we're going to correct the typo.
So it's okay.
And now the third
off conditional former thing
is going to be if
than this one.
clothes, and again we delete
It's a bit
You have to do four times every for every rule,
and now we go to former TTE and the front is going to be
some kind of pink. So let's let's true something like
this, for example,
and the Phil is going to be their HQ red
and is going to look like this.
No, we click. Okay. And for this one, this is the one we were waiting for. So this one has done what we wanted.
Now we go to this marker
we selected with right miles button
and we drag you told the way down
and we choose fill form thing only,
and we got what we needed. So we have these
where they failed on revenue
and those were day overshot. And they're somewhere. They made absolutely the same result as the one
given to them by the management.
So at the end, this is how they should look.
And if you have ah,
skipped to the end, you can see how
it should be formatted. So now it's up to you. Do it yourself. Thank you.
LPI Linux Essentials
LPI Linux Essentials practice exam helps to prepare for the LPI Linux Essentials 010-160 certification ...
Being an intermediate level user of Excel can help save a user hours of work ...
9 CEU/CPE Hours Available
Certificate of Completion Offered