- [Instructor] So let's apply Conditional Formatting
so that we can identify project trends and risks.
So if we look at our project data here,
we have a certain amount we're spending
in that year to date spend.
So if we wanted to figure out,
well, are we over or under budget?
Let's take a look first
and see if we can create a column
that will help us with that.
So in cell I1, I'm going to type in delta,
referring to the delta differences
between these two columns.
And then in cell I2, I'm going to type in equals,
I'm going to select budget here, less year two date spend.
So if a value is less than zero, that's not a good thing,
that means we've overspent.
Now as we look at this other data,
there are other things we can measure.
For instance, what if a project is not completed
but it's past its end date?
And what if it's not completed at all?
That presents its own risk.
So let's see if we can score each project one through three.
And I'm going to show you how I'm going to do that.
So in cell J1, I'm going to type in project risk.
That's going to create a new column.
Then in cell J2, we're going to create the structure
to help score each project.
So the first thing I want to find out
is if today's date is greater than the project end date,
and it most likely is,
so I'm going to type in today like that,
and I'm going to type in greater than
and then select project end date.
And I will wrap these in parentheses just to stay organized.
So when dealing with inequalities,
Excel treats trues as ones and falses as zeros.
So this allows us to score up points
based off of whether something passes a true or false test.
Let's keep going.
So next thing we want to find out
is if the status hasn't been completed yet
'cause that's going to present its own risk just inherently.
So we're going to select status here,
and then we're going to do the does not equal,
that's those two brackets like that.
And then in quotes, we're going to type in completed.
So if it isn't equal to completed,
it is going to get another point.
And then finally,
we're going to do another plus here
and we want to find out if we're over budget.
So in this case, we're going to deal with the delta.
If the delta is less than zero,
that's also going to be another point.
So we'll go ahead and hit Enter.
You can see we get scoring one through three here.
So now that we have our data,
let's add in some visualizations
to help quickly identify the trends and risks.
Now as we go through this,
you're going to see there's multiple ways
to show the same thing.
I want you to follow along
just so you know the possibilities,
but, of course, it is up to you to decide
which ones you want to use,
and I would not recommend using them all at once.
All right, so let's start with cell G2.
I'm going to highlight all the way through H8.
I'm going to go to Conditional Formatting
and we're going to add data bars here
to help us understand spend.
So I'll click on this blue one right here.
We can see that we've spent more
than the associated values to the left.
So that's one way to visualize the data.
Another may be to actually just focus
on the calculated column itself.
So I'm going to highlight the data here from I2 through I8.
I will go up to Conditional Formatting.
I'm going to go to Icon Sets,
and I'm going to select this set of icons right here.
Now I've just selected that,
but it doesn't tell me how it's coming up with the answers.
So I'm actually going to go back into Conditional Formatting.
I'm going to click Rules here, Manage Rules,
and then I'm going to click Edit.
Having this icon set selected, I'll click Edit Rule here,
and here's where we're going to just double check
it makes sense, so I look at this,
and I see that it assigned green above the 67th percentile.
So that doesn't quite make sense.
So we're in the green
if we are less than $100,000 away from being over budget.
So in this case I'm going to type in 100,000,
I'm going to select Number here,
and then if it clears that out,
you can just retype it again.
And then over here I'm going to select Number.
In this case, I'm going to make it zero.
So basically if we have at least $100,000 left to spend,
we're going to be in the green.
Now if we have between zero and 100,000,
we're going to be in the yellow.
Otherwise, if we have less than that,
if we've overspent, we're going to be in the red.
So I'll go ahead and click OK on that.
I'll click OK again.
And we can see
that this does help us identify projects that are at risk,
at least in terms of spending.
Now, one final way we can use Conditional Formatting
is just simply by color-coding projects
we want people to pay attention to.
So for instance,
if we were most interested in understanding which projects
had the highest risk,
what we could do
is we could highlight the project descriptions over here,
and then we can go to Conditional Formatting
and we could create a new rule.
In this case, I'm going to say use a formula
to determine which cells to format.
So in this text box where it says format values
where this formula is true,
what we're going to do is we are going to type in equals,
and I'm going to select sell J2 here.
And then I'm going to get rid of the two
because we actually want it to go down the rows.
But we're going to keep the J locked in.
And the reason that this is going to work
is first, it's going to compare whether J2 equals three,
and if it doesn't, it's going to move to the next line
and it's going to work that way
because we got rid of the dollar sign.
So we're going to say dollar sign J2 equals three.
If this returns a true,
note this is format values where this formula is true,
it's going to change the formatting based off of what we set.
So let's just think about this real quick.
We're starting in cell J2 does not equal three,
doesn't do anything.
We're going to move to cell J3,
which follows along with the entire range that we selected.
So it's going to follow along with B2 here,
so it does equal three.
So it's going to change this to whatever color we set.
So let's go to Format here.
I'm just going to make this simple.
We'll just make it kind of a peach color
just to draw our user's eyes into it.
So I'll go ahead and click OK on that.
And you can see it automatically highlights that.
Well, we've reviewed multiple ways
to visualize project trends using Conditional Formatting.
In our next video,
we're going to talk about how to use pivot tables
to create high level summaries.