English Subtitles for Basic Excel Business Analytics #14: Logical Formulas & Conditional Formatting to Visualizing Data



Subtitles / Closed Captions - English

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.



Video Description

Download file from “Highline BI 348 Class” section: https://people.highline.edu/mgirvin/excelisfun.htm
Review how to use Logical Formulas to add Conditional Formatting to Data Sets:
1) (00:11) Introduction
2) (00:33) Built-in Feature: Conditionally Format Budget Data when Actual Exceeds Budgeted Amounts.
3) (02:26) Logical Formula & Mixed Cell References to Format Row: Conditionally Format Budget Data when Actual Exceeds Budgeted Amounts:
4) (05:24) Cell Chart with Data Bars: Conditionally Format Budget Data when Actual Exceeds Budgeted Amounts:
5) (07:29) AND Function and Mixed Cell References to Conditionally Format Records that math two Criteria.

Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.