Conditional Formatting


Excel Conditional Formatting

Basics

The first thing you should know is that conditional formatting in Excel takes priority over standard formatting of a cell when the two formats contradict. For instance:

changes to

without any adjustments aside from adding a conditional formatting rule.

You may be wondering, “When will I learn how to create conditional format rules in Excel?” If so, you are already one step ahead of the game. Read on to create your first basic rule.

Step 1. Select the range you would like to format.
Step 2. On the Home tab, click Conditional Formatting.
Step 3. Choose a Rule Type.

The preset rules have a lot to offer, but they don’t account for every need in Excel. Let’s look at them briefly.

As you can see, apart from offering range of value-conditions, Microsoft Excel also offers above and below the average, top 10%, and a few other useful criteria. Selecting any of these will bring up a menu for further options.

Fortunately, we have not been confined to preset conditional formatting rules; but before we address the custom conditional formatting rules, I want to mention data bars and color scales.

Data bars (pink) are drawn within a cell, while color scales (white to black gradient) actually change the fill color of the cell. Both of them are very useful for showing the magnitude of the data in relationship to the rest of the data contained within the rule , especially if the data is not sorted already sorted. (Each cell is compared to the range you selected when you created the rule)

To achieve this particular effect, I used a white font, the default pink data bars and added a conditional formatting rule to format all cells based on their values as follows:

You can probably figure out how to work the other preset rules within the New Formatting Rule Menu. If not, feel free to leave a comment, and I will do my best to elaborate.

Also, don’t forget to play around with the icon sets. They are pretty neat, albeit a little over the top for my taste.

Advanced Techniques

In my opinion, the most useful thing about Conditional Formatting is the ability to apply conditional formatting based on a formula. This was difficult for me to grasp until I learned what is actually going on inside the formula, so I will attempt to take my time with this explanation.

The general idea:

Cells are formatted when the expression you write evaluates to TRUE.

First, lets look at what it means for an expression to be TRUE:

You may already know that computers associate 1 with TRUE and 0 with FALSE. But did you know that computers would evaluate any of the following as TRUE? 1, 3, -5, 76, 875354, .03

This means that when we are creating a formula to format the cells, the result of the formula can be anything except 0, an error, or text.

An exception to this is the Boolean word TRUE, as this word has a value of 1. The Boolean word FALSE has a value of 0. Try typing =(TRUE + 1)*12 in a cell. Now try =(TRUE + 1)*12*FALSE.

A typical conditional formatting equation for range A1:A5 (your selected range) will look as follows, note that the function evaluates to a number (A.K.A. TRUE), if the evaluated cell has the value 3 in it:

=Countif(A1, 3)

**Note that this is distinctly different than

=Countif($A$1, 3)

In the first example, the relative reference (A1) will allow the conditional formatting to evaluate each cell relative to itself. In the second example, since the cell reference is constrained by the dollar signs ($A$1), every cell checks if cell A1 is = 3.

The following image shows an example of the difference.

Microsoft Excel automatically puts dollar signs in the conditional formatting when you click a cell to get the reference, so make sure to delete them if need be. For more on Dollar signs in Excel, including keyboard shortcuts for them, check out the formula page (coming soon)

Now, if you understand the math behind TRUE and FALSE, you will have a better grasp on the next more advanced concept:

Conditional Formatting “AND” Function

Ever run into a situation where the AND function in Microsoft Excel’s Conditional Formatting doesn’t work? Don’t Stress!
You typically are allowed to use the “AND” function with conditional formatting, though sometimes it can be a little bit picky.

The other day, I was attempting to build a formula to answer a question regarding conditional formatting. Unfortunately, the formula I wrote, combined with the “AND” function it was contained in, was too complicated for the Excel conditional formatting engine.

Fortunately, now that we understand the math behind TRUE and FALSE, we can use a back door to achieve the same effect as the “AND” function.

I will show you what that looks like; keep in mind that you don’t have to understand the formula to grasp the concept I am trying to show you. (I will explain the formula lower down in the page [use your browser's find function to search for "Indirect" or "Address")

The following formula will not work in conditional formatting, even though both parts of the "AND" expression evaluate to TRUE.

=AND((INDIRECT(ADDRESS(ROW(),1))>3),(INDIRECT(ADDRESS(ROW(),1))

If you don't believe me, Enter a number between 4 and 9 into a cell in the first column, and paste this formula anywhere else in the same row. If you did this correctly, you will see that the formula evaluates to TRUE.

So, how do we tell if two expressions are true in Excel without an "AND" function? The answer is in the math. An expression that evaluates to FALSE is always equal to 0 and an expression that evaluates to 0 is always FALSE; we can use this to our formatting advantage.

TRUE * TRUE = TRUE (1 * 1 = 1)
TRUE * FALSE = FALSE (1 * 0 = 0)
FALSE * TRUE = FALSE (0 * 1 = 0)
FALSE * FALSE = FALSE (0 * 0 = 0)

Understanding this, we can create an adapted formula that now works for conditional formatting.

=(INDIRECT(ADDRESS(ROW(),1))>3) * (INDIRECT(ADDRESS(ROW(),1))

If the expressions on the both sides of the multiplication sign evaluate to TRUE, then the entire expression evaluates to TRUE. If either side evaluates to FALSE, then the entire expression is FALSE.

This version can be used in Conditional Formatting even though the version using the "AND" function wouldn't work.

Try pasting this one in the same row as your previous number in Column 1. If the number is within the correct range, the formula will evaluate to TRUE. (TRUE*TRUE = TRUE). Can you think of how to create the back-door to the "OR" function?

A back-door to the "OR" function can be created using a simple addition problem, since any number not equal to 0 evaluates to TRUE:

TRUE + TRUE = TRUE (1 + 1 = 2)
TRUE + FALSE = TRUE (1 + 0 = 1)
FALSE + TRUE = TRUE (0 + 1 = 1)
FALSE + FALSE = FALSE (0 + 0 = 0)

**Important note: -1 evaluates to TRUE, just like +1. The following situation can arise: -1 (TRUE) + 1 (TRUE) = 0 (FALSE). If your formula might encounter such an exception, use the absolute value function around each expression: ABS(#).

ABS(-1) + ABS(1) = 2 [TRUE]

Excel Functions Referenced

Here I will give a brief overview on formulas as it relates to this discussion.

1. Every formula in Microsoft Excel starts with the equals(=) sign.
2. Formulas are evaluated with the typical order of operations in math.

Try a Google search for PEMDAS. PEMDAS stands for Parenthesis, Exponents, Multiplication, Division, Addition, Subtraction. Multiplication has the same Order Weight as Division, and Addition has the same Order Weight as Subtraction.

In other words, due to the associative property, it doesn’t matter if you multiply first or divide first; in the same way, it doesn’t matter if you add first or subtract first. For a deeper explanation, Google the Associative Property of Multiplication or the Associative Property of Addition.

3. When describing a formula, any parameters surrounded by “[]” are optional and have default values.

For those of you interested in the Indirect and Address function used above, I will explain it here, as well as how it applies to conditional formatting in Excel.

Since formulas are evaluated from the innermost parenthesis first, lets start with Microsoft Excel’s Address() function.

Syntax: Address(Row_Number, Column_Number, [abs_ref], [return_type], [sheet_reference])

The Address function outputs the Cell Address of the cell at a specific row and column intersection. Here are some examples to give you an idea of how you can use it.

Address(1,1) = $A$1
Address(1,2) = $B$1
Address(2,1) = $A$2

With Optional Arguments:
Address(1,1,2) = A$1
Address(1,1,4) = A1
Address(3,5,4,”R1C1″) = R3C5
Address(3,5,1,”A1″,”Sheet1″) = Sheet1!$A$1
Address(3,5,1,”A1″,”[Book1]Sheet1″) = [Book1]Sheet1!$A$1

Note that all the arguments after row and column are Completely Optional. See Microsoft Excel’s Help for more information on this function.

Indirect() Function

Syntax: Indirect(Cell_address, [a1])

The Indirect function gives the value of a cell located at a specific cell address. Here are some examples to show you.
Lets assume the value in Cell A1 is the text “B1″, the value of cell A2 is the text “R1C1″ and the value of Cell B2 is the text “Over Here”
Indirect(“A1″) = “B1″
Indirect(A1) = Indirect(“B1″) = “Over Here”

Advanced Technique
Indirect(Row(),1): returns the address of the cell in the current row in column 1.
Indirect(1,Column()): returns the address of the cell in row 1 in the current column.

With Optional Argument
Indirect(R2C1,”R1C1″) = Indirect(“R1C1″,”R1C1″) = “B1″

Recap: Address() returns a Cell address using a row and column number; Indirect() uses an address to get a cell value.
We can use these 2 in combination with the Row() or column() function to create useful formatting techniques in Excel.

Now lets look at the function referenced above in the context of Conditional formatting. Here is the setup for this example. (IMG)

=(INDIRECT(ADDRESS(ROW(),1))>3) * (INDIRECT(ADDRESS(ROW(),1))

When conditional formatting evaluates each cell, the formula will read from left to right, innermost parenthesis to outermost parenthesis.

Checking the formatting for cell A1 first:

1. Row() will be evaluated. This will grab the row of the current cell being checked for conditional formatting.
2. Address(Row(),1) will then be evaluated. For A1 and B1, this will give us “A1″.
3. Indirect(Address()) will be evaluated. This will give us the value of 1 for A1 and B1.
4. Result of Indirect() will be compared to 3. This will give us FALSE for A1 and B1.
5. Repeat steps 1-3 for the right side of the multiplication sign.
6. Result of Indirect() will be compared to 10. This will give us FALSE for A1 and B1.
7. FALSE*FALSE = FALSE (no formatting is applied to Cell A1). [read up on the "AND" function in conditional formatting above]
8. Steps 1-7 will be evaluated individually for all cells each row.

The overall result of this is that you can use Excel conditional formatting based on another cell value to format a cell. You could adapt this to organize by column, or much much more; use your imagination.

Leave a Reply


six + 8 =