Welcome to Highline BI348 class video number 14.
If you want to download this file, BI348Chapter02Start
or the finished version, click on the link below the video.
Hey, we've got a great video here.
We want to remind ourselves about logical formulas
and conditional formatting to visualize data.
Now, here we would like to format data
when actual exceeds budget.
And we actually have three examples here
and then we have one another cool conditional formatting
visualizing data example.
Here's budget, here's actual.
And we have our difference here.
We would like to highlight when we're above our budget.
So here, we were budgeted 195,000.5
and we ended up at more than 206,000.
So whenever we see a minus or something below 0,
we'd like to format.
Now there's some built-in features up here-- home,
conditional formatting-- that'll allow
us to do some things, but not all things when it comes
to conditional formatting.
Now, the idea behind conditional formatting is very easy.
Every cell in the highlighted range
has to have some indication of true, we want the formatting,
or false, we do not want the formatting.
Now here, anytime we have a number less than 0,
we want a true, please give me the formatting.
When it's not less than 0, we want a false,
so the formatting won't be applied.
We simply highlight the range, and there's a built-in feature
for this one.
We go up to our conditional formatting,
and there's a bunch of cool built-in features.
We want to highlight cell rule and say less than.
Now, this is pretty much going to be a hard coded number.
Notice we do have a collapse dialog box,
so we could link it to criteria in the cell.
But this is never going to change,
so I'm simply going to type a 0 in.
I am not limited to this light red fill,
I can click the drop down.
And here's is the power, custom format.
I can pick anything I want from number, font, border, and fill.
I'm going to, on font, say bold, font color, let's say red.
Click OK, click OK.
And we can already see it in action.
And the beautiful thing about this is it's dynamic.
Hey, we made a mistake here.
This is really 180,000.
Instantly, it changes.
Control Z.
Now sometimes when you have data like this,
it would be nice to highlight the whole row.
Well, there's no built-in feature for that.
But if we know logical true/false formulas,
we can build a formula that will do exactly what we want.
Now we've got to think about this-- not only that,
but you have to remind yourself about all the different types
of cell references that we know how to use.
We're going to have to use mixed cell references here.
Now here's the question.
Is that less than 0?
But guess what?
If we're going to highlight this whole table,
and have the whole row turn red, that means this cell actually
needs to be looking at that number and asking,
are you less than 0?
Same with this cell, and this cell, and this one.
That means if we were building a formula,
we'd have to have the whole row point there.
But guess what?
If the whole row looks at that one when you copy down
to the next row, the logical formula,
it's then got to be looking at that cell reference.
No problem, mix cell references will do this fine.
Now notice, when we're highlighting
to do our conditional formatting,
there's an active cell.
That active cell is very important
because we'll build the formula from the point of view
of that cell.
And then in memory, it'll actually
copy the formula over and down.
All right, I'm going to go up to conditional formatting,
new rule.
Or I'm going to use the keyboard, Alt, H, L,
N. Now this dialogue box has a bunch of options,
and we can click here.
Or if I'm already selected at the top when I did Alt, H, L,
N, I can do page down to jump.
Page down actually goes in the reverse and then hits up there.
And notice this is the option we want.
Use a formula to determine which cells to format.
Tab to move to the text box that asks format values
where this formula is true.
Now remember, and I'll move this out
of the way, that's the active cell.
So the formula we build here, even though it's not
in the cell, because it would replace all the data--
that cell needs to look there.
And the default is both the column and the row is locked.
So I'm going to hit the F4 key once and twice.
That'll lock the D. So as I copy the formula over here,
notice it'll be locked on D. But since the 17 isn't locked,
when the formula is copied down, it'll automatically move to 18.
So now I ask the question-- are you less than 0?
That's our formula.
Now we click format, and I'll do the same thing.
How about bold, font color red, click OK, click OK.
And there it is, the whole row.
Now if I were to change this to 180,000, instantly it updates.
Control Z.
Now let's look at another awesome feature.
Notice we're going to see three different ways
to indicate that we have an actual that's
bigger than budget or a difference that is negative.
Now in this case, I'd like to build what's called a cell
chart off to the side.
And I would like to point to this cell
here as a relative cell reference.
So with the active cell at the top,
I'm going to say equals left arrow
to get a relative cell reference and Ctrl Enter
to populate that formula all the way down.
All it did is it was a quick way to put the formula in one cell
to my left and copy it down.
Now with that highlighted, I'd like to use an awesome feature,
conditional formatting data bars.
And watch this.
We actually have a choice.
We can use a translucent one, down here
there's some solid ones.
I think I'll use this translucent one right here.
And look at that!
It's actually like creating a bar chart in our cells.
And it's totally dynamic.
Now, I would like to hide the numbers.
And so I want to go back up to conditional formatting and not
new rule, I want to manage rule.
And you can click on that or use the keyboard Alt O D.
And now it's highlighting our rule, so I can click edit.
And there's a bunch of things we can change here,
but here's what we want.
Show bar only.
And when I click show bar only and click OK, click OK,
I have a bar chart in my cells.
And if I change this, the rent went up to $16,000.
Instantly it updates.
Ctrl Z.
So we did a formula in the cell and hid the formula
but used the data bars.
Here we used a logical formula with mix cell references,
and here we used the built-in feature to ask the question,
are you less than 0?
Now I want to scroll over here and look
at yet another example.
And all of these examples come straight from our business 218
class, our prerequisite.
Now here we have a data set.
And again, I made this small so we could
see how everything's working.
And here's our criteria, Pham and East.
What I'd like to do is highlight only
when there's a region East and a sales rep Pham.
So I need to highlight or conditionally format
that entire row.
I would literally have to go through matching these two
conditions down to another East Pham
that I get a true-true that our conditional formatting
for the entire row should come into play.
Not only that, but if I change the inputs over here,
I want the conditional formatting to update.
Now in this case, we're going to use
a logical formula using the And function and mix cell
references.
Now remember when we actually go up
to our conditional formatting dialog box,
we have to highlight and then in the active cell
build a formula from that point of view.
Sometimes for a complicated formula
it's easier to build it in the cells.
Once you see the patterns of trues and falses working,
then you can copy the formula from that top active cell,
open the dialog box, and paste it.
Now remember, we're pretending that this whole row
is this whole row right here.
So if I'm starting here, I need every single cell
in this row to look at region and sales rep, meaning the L12
and M12.
But when it goes down, it needs to move to M13 and N13.
All right, you ready?
And there's two conditions, 1, 2.
So I'm going to use Equals And.
And is a logical function.
We could put as many logical tests as we want in here.
Only when every logical test comes out true will the
And deliver a true.
Remember, we had situations where
we had a false and a true.
So internally in here, and we'll see a false and a true.
But because they're both not true,
and will not deliver a true.
So you ready?
The first question is region.
And that's a relative cell reference.
Notice when you're in the cells, it
works differently than when you're
in the conditional formatting dialog box.
I'm going to hit the F4 key to lock it, and then again, and
again.
I need to lock the L, but not the 12.
And I need to ask the question, hey, region in this row,
are you equal to region F4 locked in all directions.
Now there's a comparative operator,
right there, equals sign.
Any time you use a comparative operator in a formula,
it comes out true or false.
So we're good for logical 1, logical 2.
The second question is, sales rep in this row,
and I need to lock it.
F4 one, two, three times, lock the M but not the 12.
Are you equal to our sales rep criteria up here?
And that needs to be locked in all directions, so F4.
That's our formula.
Close parentheses.
Control Enter.
Copy it over.
Whoa, look at that.
A bunch of trues, there's our pattern
of trues and falses, all the way down, all false.
And it doesn't happen again until we get a bunch of trues.
Now, remember that's a mix cell reference, so this entire row
is locked on L16 and M16.
Any one of these cells, you can see it's locked.
If I come down here in F2, locked on the region
and the sales rep for this row.
Now we can simply F2 and copy this, Ctrl C,
top active corner, Esc, so when I highlight.
And be sure and don't do this because that's
the wrong active cell.
You want to highlight all the way up in the corner.
And now we can use our keyboard Alt H L
N. This is new rule, page down, Tab,
Ctrl V. You do not have to do those keyboards,
but at least go up to new rule and use formula to determine
which cells to format.
There's our formula, formatting, and you
can do whatever you want.
In this case, I'm just going to do fill yellow.
Click OK, click OK.
And that is beautiful.
If I change this to Smith, instantly the row
that matches East and Smith is highlighted yellow, Control Z.
Now another thing that's nice about this
is you might have a total and you want a visualization
to show you where those records are
so you can look through them.
So here you would do sum ifs.
The sum range, I'm going to highlight sales,
the criteria range.
I have one, two columns.
It doesn't matter in which order you do it.
I'm going to do region first.
Notice it says criteria range.
That's the whole range, comma, the criteria.
Region, comma, criteria range 2, the sales rep column,
comma, criteria 2, right there.
We don't need to lock them because we're not copying this
anywhere.
Ctrl Enter, and there's the total.
Now I could change and types south.
And instantly I get the single amount
for that particular transaction.
Change this to Sue.
Instantly I get my visualization and my total.
All right, so in this video we talked
about conditional formatting with logical formulas and mix
cell references.
We saw this data set example with two conditions
for highlighting our particular records.
We also saw three examples for highlighting
when actual exceeds budget.
All right, next video, we'll talk
about creating frequency distribution
in charts for categorical data.
All right, we'll see you next video.