When you want to format a cell based on the value of a different cell, for example to format a report row based on a single column’s value, you can use the conditional formatting feature to create a formatting formula. This post explores the details of formatting a cell or range based on the value in another cell.
Here’s an example that will allow us to put this feature into context. Let’s say that you have an invoice listing and your objective is to identify the open invoices. Here is a screenshot of our sample invoice listing:
Since this is Excel, there are many ways to accomplish any given task. One way to identify the open invoices is to simply sort the list by the Status column so that the open invoices appear in a group. Another way is to filter the listing to show only the open invoices. These techniques are fairly straightforward, so, let’s explore another method. We’ll highlight the transaction rows with cell formatting…or, more precisely, a conditional formatting formula.
Using conditional formatting, it would be pretty easy to highlight just the Status column. It would be simple because the cells we are formatting are the same cells that have the values to evaluate. That is, we would be formatting a cell based on the value within that cell. To perform this, we could simply highlight the Status column, and the use the following Ribbon command:
- Home > Conditional Formatting > Cell Rules > Equal To
In the Equal To dialog box, we could enter the word “Open” and pick the desired formatting and click OK. Excel would then apply the formatting to the cells within the Status column that are equal to Open. While this technique is easy, it does not meet our goal which is to highlight the entire transaction row, not just the Status column.
To highlight the entire transaction row requires us to format a cell based on the value in another cell. That is, we want to format the TID, Date, Status, CustID, and Amount columns based on the value in the Status column. Considering a single cell for a moment, we want to format B7 based on the value in D7. This means that we want to format a cell, B7, based on the value in a different cell, D7. Expanding this to the entire row, we want to format B7:F7 based on the value in D7.
Excel makes it easy for users to format a cell based on the value of that cell, and the built-in conditional formatting rules use this logic. When we want to format a cell based on the value in a different cell, we’ll need to use a formula to define the conditional formatting rule. Fortunately, it is not very difficult to set up such a formatting formula.
Let’s highlight the entire transaction listing (B7:F36) first, and then open the conditional formatting dialog box using the following Ribbon icon:
- Home > Conditional Formatting > New Rule
The New Formatting Rule dialog box has many choices, allowing you to, for example, format a cell based on the value, if it contains a value, the top or bottom ranked values, values that are above or below average, and unique or duplicate values. At the bottom of the list we find the option we need. We want to use a formula to determine which cells to format.
The formatting formula needs to be set up so that it returns a true or false value. If the formula returns true, then the desired formatting is applied. If the formula returns false, the formatting is not applied.
The key thing to understand about writing the formula is that the active cell is the reference point for the formula. Since this concept is absolutely critical, I don’t want to just skip through it, I want to unpack it for a moment.
You want to write the formatting formula as if you are writing it into the active cell and use the appropriate cell references and reference styles, such as absolute, relative, and mixed. If you can visualize the idea that you are writing the formula into the active cell, and the formula will be filled through the selected range, then writing the formula becomes easier. The formula you write will not be used to compute the cell value, rather, it will be used only for formatting.
Let’s assume that when we selected the entire transaction range, B7:F36 that the active cell is B7. When you select a range, there is still a single active cell. Check out the screenshot below to see that the range is selected, yet, B7 remains the active cell:
With this idea in mind, it is easy to write the conditional formatting formula now. We need to write the following formula in the New Formatting Rule dialog, as if we were writing it into cell B7:
This simple comparison formula returns true when D7 is equal to Open, and thus, the desired format will be applied. Let’s take a quick look at the cell reference for a moment. We used a mixed cell reference, $D7, where the column part (D) is absolute and the row part (7) is relative. Here’s why. Remember we want to pretend that we are writing the formula into the active cell, in this case, B7. If we were only formatting B7, then, we could have used a relative reference D7, or an absolute reference $D$7, or a mixed reference. The reference style wouldn’t matter because the formula was used in a single cell only and was not filled anywhere.
However, the moment that we fill a formula down or to the right, we need to be careful to use the proper cell reference styles. In our case, as the formatting formula is filled right, we don’t want the column reference D to change. That is, for all cells, we want to reference the Status column, column D. To prevent Excel from changing the column reference as the formula is filed to the right, we lock it down with the dollar sign, resulting in $D. As the formatting formula is filled down throughout the selected range, we want to ensure that the row reference is updated accordingly. When formatting B7, we want to look at the Status column within the same row, or D7. However, when the formula is filled down to row 8, we want to format B8 based on the value in D8. Since we want Excel to update the row reference, we’ll leave it relative and not use the dollar sign to lock it down.
Here is a screenshot of the formatting dialog box with the formula:
Once the formula is entered, you simply use the Format button to specify the desired format.
Once the conditional formatting is applied to the range, the resulting worksheet is shown below:
Bam…we got it!
In the formula above, we hard-coded the value, “Open” however, we could have easily placed this value into a cell, and then referenced the cell either by name or A1-style notation. In addition, besides just determining if the cell value is equal to a value, other comparison operators are supported, for example, greater than (>) and less than (<). We could have highlighted all rows where the value is greater than $5,000 by using the following comparison formula:
Since the formatting is applied based on a formula, we can get very creative and use worksheet functions. We could format alternating worksheet rows by using the MOD and ROW functions. Or, we could highlight old transactions by computing the difference between the transaction date and today’s date with the TODAY function. Indeed, this formatting formula provides many fun options.
We can also use the logical AND and OR functions in case we want to consider multiple conditions. For example, formatting those rows where the status is open and the amount is greater than 5000 by using the following formula:
Since the AND function returns true when all of its arguments are true, the formatting is applied only when the status is open and the amount is greater than 5,000.
If we wanted to format the row if either condition was met, then we’d want to use the OR function instead of the AND function since it returns true if any single argument is true.
Having the ability to format a cell based on the value of another cell is quite handy. The key is to imagine that you are writing a formula into the active cell. The formula needs to use the appropriate cell reference styles (absolute, relative, mixed) so that as the formatting formula is filled throughout the selected range, the proper cells are considered. The sample file below has the conditional formatting rules included, so feel free to check it out.
Hope this helps, and remember, Excel rules!
Here is the sample file in case you’d like to open it up and reference it.