Conditional Formatting - formulas for selecting cells

An area where more flexibility is allowed in conditional formatting is the use of formulas for selecting which cells that must be formatted. In this section we will see examples of what kind of usages we can have with this option.

• Formula to highlight alternate rows or columns
• Formula to highlight specific day of the week
• Formula to highlight errors, omissions, repetitions
• Formula to highlight cells that contains a search value

-------------------------------------------------------------------------------------------------------------------------

But first let's see how a formula for conditional formatting is introduced.


1. First you have to go to Conditional Formatting> New Rule

2. Next we select the option Use a formula to determine which cells to format> Format values where this formula is true.

3. Now the formula is entered in the box where I put 'Insert formula here'. For example, I will introduce a formula that highlights the cells greater than 4.

My list is in the range A1:A12. And they are all values.

Now selecting our range A1:A12, we will do step 1 & 2.

With the following result:

It is important that in this case the formula is introduced without $ on both sides of the cell, so that neither the row nor the column are fixed and Excel can automatically drag the corresponding cells.

4. To ensure that the cells to be formatted are the correct ones we can do the following. Go to Conditional Formatting> Manage Rules.

Here everything can be edited, the formula, format or which cells should be formatted.

To see more of conditional formatting click here.