Highlighting an entire row

1. Highlighting an entire row

Now that you have had some exposure to using custom formulas to highlight cells, we are going to extend this highlighting to an entire row.

2. Example: sales detail

In this example, the data you are working with is a report with each row representing a sale by a particular sales rep. Let's highlight each row that corresponds to a sale made by Joan.

3. Select all columns

First, you need to select all of the columns in the range instead of just the column of interest, here, the rep column. You are highlighting all of the columns and rows because the custom conditional formatting rule will need to be evaluated for every one of these cells in order to highlight the entire row if the formula is TRUE.

4. Order of evaluation

To conceptualize how the formula will be applied, think of it working in the same direction that you read a book. Left to right and then down one row. Left to right again, and so on. When an entire row is highlighted, it is being highlighted one cell at a time instead of the entire row at once.

5. Fixing column references

Another important difference from previous formulas is that we are going to be fixing the column reference. You fix a column reference by appending a dollar sign before the column letter. In this table, row three contains relative column and row references and row four contains fixed column references and relative row references. As the formula is applied from left to right, the column's references move in the formulas in row three but they stay the same in row four. Row 4 is similar to what we will be doing with our custom rule. Custom formatting moves cell references in the same way that they move when they are copied and pasted in your spreadsheets. The references move unless they are fixed with dollar signs. This is why it is helpful to think of your custom rule as moving left to right when it applies its formatting. If you fix a column's references, it will be looking within the same column every time it evaluates the rule.

6. Fixing your formula

Here, the custom formula applies formatting to entire rows based only on the values in column B because we have fixed the column reference to column B, but this rule is applied to the range of A2 through F13. When this rule gets to cell A4, it is evaluating whether or not cell B4 is equal to Joan. It is doing the same evaluation for each cell in row 4 of columns A through F. This is TRUE for every cell in row 4, so the rule is triggered in every cell in row 4 gets highlighted. In row 5, the cell B5 does not equal Joan, so the rule is not triggered for any of the cells and none of them are highlighted.

7. Time to try it out!

And there you have it! We have learned how to highlight an entire row based on the values of just one cell in the row. Let's apply what we have learned in a few exercises.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.