27 slides (22 if you skip the big example at the end)
Click the right arrow to go to the next slide.
Let's test a range of blank cells and highlight them yellow if they are indeed blank. I've placed spaces in B3 and A5, so they're not blank. Our rule should not highlight those.
Our range is selected and rectangular.
We go to the Home tab of the ribbon and click Conditional Formatting. At the bottom of the menu, select New Rule.
Our formula will consist only of the ISBLANK function, which returns TRUE if a cell is blank. Our formula should be the test you want to place in the first cell, A1. That test is to see if the cell itself is blank, so it references itself:
=ISBLANK(A1)
Then we set our formatting to highlight yellow.
The rule now applies your formula to all cells in the range as if it were copying the formula. So in B1 the formula is =ISBLANK(B1). In A2 it's =ISBLANK(A2) and so forth.
Because I put spaces in cells B3 and A5, they are not blank. So they return FALSE, and, therefore, the rule does not format them.
Now let's format cells in a column, A1:A10, that are greater than the value in cell C1.
Our formula will be a simple logical test using greater than (>). We'll test the first cell in the range, A1, against the value in cell C1. C1 will have to be a fixed reference so that when the rule "copies" (applies) the formula to each cell, it always compares the cell's value to C1.
=A1>$C$1
Actually, we could have written C$1, but it's a good practice to fully fix cell references when all formulas are referencing the same cell or same range. Here's how the rule is applied.
And here's the result.
Likewise, you would use fixed cell references for the range in aggregate functions when used in logical tests like this:
=A1>AVERAGE($A$1:$A$10)
Because all of these AVERAGE functions need the same range
You write your formula for the first cell.
For example, let's say you want to highlight the name of anyone in this table who has more than $100,000 in their account.
So we test the Balance column but apply the formatting to the Name column. Begin by selecting the Name column.
The first cell is A2. That's the first candidate for formatting. But the formula for A2 will not have a reference to A2. Instead it will need to reference (test) C2 to see if it's greater than $100,000.
So the formula for the first cell, A2, is:
=C2>100000
So we select A2:A6 to identify the candidate cells for formatting. Then apply our formula to those cells.
The rest of this slide set is an example.
If you're not interested in how to highlight protected and unprotected cells, you can skip to the recap.
Obviously, there are easier ways to conditionally format cells without writing formulas. But sometimes you need something for which there is no preset or new rule type.
For example, what if I had spent hours unlocking individual cells in multiple worksheets in a workbook, so that I could protect (lock) the worksheets and prevent users from editing the wrong cells.
But I've lost track of which cells are locked and want to quickly find them. This is a pretty obscure problem for which there is no preset or regular new-rule functionality.
No worries. We can use a formula, specifically the CELL function.
The CELL function can check the cell's many properties for matches and return a TRUE or FALSE outcome. (Go here for a list of those properties.)
For our situation, we're checking to see if the cell has the "protect" property set. This means the cell is locked.
We'll start with selecting an entire worksheet by clicking the triangle above and to the left (northwest) of cell A1.
Then go make a new rule with a formula and type the following:
=CELL("protect", A1)
Select your formatting (I'll use yellow highlighting again) and apply the rule to every cell in the worksheet.
The rule highlights all the cells that are locked.
It'd be better to highlight the cells that aren't locked. To do that, nest the CELL function inside a NOT function*.
=NOT(CELL("protect", A1))
* The NOT function turns TRUEs into FALSEs and FALSEs into TRUEs.
You can pretest your formula's effect on each cell in the range by typing out and copying the formula across a blank area the size of your range. Or just test part of it.
your formula
Copy here
This shows you which cells will test TRUE and which test FALSE. Did the formula find all cells >300? Looks like it.
A big muy appreciado to Microsoft MVP and Excel genius/beast Mike Girvin for this tip. Check him out at ExcelisFun.