Excel: Formatting an Entire Row Based on One Value

Have you ever gone through a list row by row to highlight rows based on one value in the row? Here is one example: you have a list of students, and you want to highlight their entire entry if they have an “A” in your class… so you hand-select an entire row, fill in the desired color, and repeat this process 100 or so times per semester. Never do that again! Let’s check out another versatility of Conditional Formatting: the ability to highlight an entire row based on one value in a specific column.

Before we jump in… big thank you to Samantha and Amanda for inspiring this Byte with this excellent question in (two different) Excel Advanced Formulas sessions!

Exercises

If you would like to follow along, here is a fictional class list to work with. Our goal with this exercise is to make an entire row yellow if a student has an A in the class.

A Word About Conditional Formatting

In my Advanced Formulas training we touch on formatting specific cells based on their value with Conditional Formatting, but this will be a bit different… in this case, we would like the entire row to be highlighted based on the value of one cell in the row (the grade).

If you haven’t already, I hope you will consider checking out an Excel Advanced Formulas session… for one, we cover Conditional Formatting in more detail, and for two we talk about concepts like absolute references, which I reference later in this Byte.

Create a New Rule: Crafting a Formula

1. Start by selecting all the data you would like the formatting to apply to: A2 through H77.

2. In the center of the Home tab, select Conditional Formatting, New Rule.

3. In the popup screen, select Use a formula to determine which cells to format.

4. Click into the box underneath the text Format values where this formula is true.

• In your data, click on the cell with the first grade, in H2.
• Excel will bring in its own language for this cell “$H$2”.
• Type an equals sign ( = ) and (with quotes) “A”.
• Your formula at this point will look like : =$H$2=”A”

Sidebar: We hit this in more depth in Excel Advanced, but as a reminder…

• The “$” means an absolute reference, so a stagnant location in Excel. We see an example of this in class when we try to use autofill to carry down a formula.
• The A has to have “” around it, since it is text.
• If you want to learn more about these two points, please come to one of my Excel Advanced Formulas trainings.

Okay, we are not done with the formula yet… if we leave the formula like this, it will highlight the entire selection range (A2 through H77) if H2 has an “A” in it… this is not what we had in mind. To correct this, we are going to remove one dollar sign…

5. Since we realized that the row should not remain absolute, let’s delete one dollar sign (absolute reference). We are going to delete the dollar sign in front of the number 2. This will tell Excel that the row is relative; but the column will remain absolute.

• Here is what your formula should look like now:

=$H2=”A”

We are not done yet, though… we still have to set up formatting…

Creating a New Rule: Formatting

It is easy to get so caught up in the formula that you forget to set a formatting rule. Right now the formula looks great, but Excel won’t make it look any different until we pick some formatting.

1. Select the Format button in the New Formatting Rule popup underneath where you placed your formula.

2. In the popup that appears, select the Fill tab. Select a color, then press OK.

3. Alright, this looks pretty good! We have a formula and a formatting. Press OK to see the results.

If all goes well, you should have something like this.

To sum up,Excel is looking through column H (which remained absolute with the “$”) for an “A”, and if it finds an “A” there, the entire row is highlighted all the way down (because the “2” was not absolute, thanks to removing the “$”)

Managing Conditional Formatting Rules

A few more things that might be helpful:

• If you want to see the conditional formatting rules you have created, click on the Conditional Formatting dropdown, and select Manage Rules.

• The default view shows you the rules present for any cells selected. So if you would like to see all the rules for a sheet, you can alter the Show formatting rules for dropdown accordingly.

• Here is where you can also make adjustments to your rules: edit, delete, alter the cells it applies to (helpful if you selected too many or too few cells to begin with).

Thoughts?

What do you think, will you try out this type of conditional formatting in your documents? I would love to hear how you use this!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

• Kaylee Nungesser

• Andrea Glessner