Ravindra BagaleCourses & study guides मराठी Track your progress

How to Highlight an Entire Row in Excel with Conditional Formatting (Formula Rule)

By Last updated:

The built-in rules from the Conditional Formatting chapter colour only the cell that meets the rule. Very often you want more: if the Result is Fail, colour the whole row, name and marks included, so nobody can miss it. For that you write the rule yourself, as a small formula. It takes one line, but two details decide whether it works: where the dollar sign goes, and which row the formula talks about.

What you'll learn in this chapter

  • New Rule > Use a formula to determine which cells to format
  • Why =$F2="Fail" has a dollar on the column and not on the row
  • The first-row rule: the formula must match the first row of the selection
  • Fixing a rule that colours the row above
  • A second rule for high percentages, and rule order
  • Manage Rules, Edit Rule, Applies to and Clear Rules

Practice file: excel-highlight-entire-row-practice.xlsx: three sheets with the same marks: Right rule (the working rules plus TRUE/FALSE check columns), Shifted rule (the one-row-off mistake) and No dollar (the rule without $).

Made-up data: all names and marks are made up for practice.

1. The table

The same marks sheet as the Conditional Formatting chapter: headers in row 1, ten people in rows 2 to 11, columns A Name, B Science, C Maths, D English, E Total, F Result, G Percentage. Three results are Fail: Shahrukh (row 5), Salman (row 7) and Raja (row 10).

2. The formula rule, step by step

  1. Select the data rows A2:G11: start at A2 and drag to G11, so A2 is the active cell (the white one in the selection). Do not include the header row.
  2. Home > Conditional Formatting > New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. In Format values where this formula is true, type:

=$F2="Fail"

  1. Click Format..., open the Fill tab, pick a light red, OK.
  2. Click OK again.

Rows 5, 7 and 10 turn red from column A to column G.

Classroom line

When you want to highlight the whole row, you have to select the whole table.

3. How Excel reads the formula

Excel writes the formula for the first cell of the selection, A2, and then copies it to every other cell, exactly as if you had dragged it, with the usual relative and absolute reference rules:

  1. $F: the column is locked. Whether the cell is in column A, B or G, it always looks at column F, the Result.
  2. 2 without a dollar: the row moves. In row 3 the formula becomes =$F3="Fail", in row 10 =$F10="Fail".

So every cell of row 10 asks "is F10 Fail?", gets TRUE, and the whole row is coloured. In row 9, every cell asks about F9 (Pass) and gets FALSE.

The practice file shows these TRUE/FALSE answers in column I: =$F2="Fail" dragged down gives TRUE on exactly rows 5, 7 and 10.

=$F2="Fail" on A2:G11: whole rows A B C D E F G 1 Name Science Maths English Total Result % 2 Ravindra Bagale 88 92 79 259 Pass 86.33 3 Shraddha Bagale 76 85 90 251 Pass 83.67 4 Ruhi Bagale 91 95 84 270 Pass 90.00 5 Shahrukh 45 30 52 127 Fail 42.33 6 Amir 62 75 58 195 Pass 65.00 7 Salman 33 41 38 112 Fail 37.33 8 Zoya 70 68 81 219 Pass 73.00 9 Ravina 55 35 60 150 Pass 50.00 10 Raja 28 47 25 100 Fail 33.33 11 Rani 80 72 77 229 Pass 76.33 Row 10, any column: =$F10="Fail" → TRUE Row 9, any column: =$F9="Fail" → FALSE $F: always the Result column 2 (no $): becomes 3, 4 ... 11 Red rows: 5, 7, 10

Figure 1. The formula is written for A2 and copied: $F stays on column F, the row number follows the row. Rows 5, 7 and 10 give TRUE and turn red end to end.

4. The first-row rule, and the row-above mistake

The row number in the formula must be the first row of your selection. You selected from row 2, so the formula says F2.

A common slip: select A2:G11 but type =$F3="Fail". Excel now asks, for row 2, "is F3 Fail?". Every row looks one row down, so the colour appears one row above each Fail: rows 4, 6 and 9 (Ruhi Bagale, Amir and Ravina), and the real Fail rows stay white. The Shifted rule sheet shows this.

The same thing happens the other way round if you select from row 1 (the headers) and type F2: then every colour lands one row below. Two ways to fix it:

  1. Select the data rows starting at row 2 and use $F2, or
  2. If you selected from row 3, use $F3. Whatever the first row is, the formula uses that row.
The formula row must be the first row of the selection A F 1 Name Result 2 Ravindra Bagale Pass 3 Shraddha Bagale Pass 4 Ruhi Bagale Pass 5 Shahrukh Fail 6 Amir Pass 7 Salman Fail 8 Zoya Pass 9 Ravina Pass 10 Raja Fail 11 Rani Pass =$F2="Fail" ✓ A F 1 Name Result 2 Ravindra Bagale Pass 3 Shraddha Bagale Pass 4 Ruhi Bagale Pass 5 Shahrukh Fail 6 Amir Pass 7 Salman Fail 8 Zoya Pass 9 Ravina Pass 10 Raja Fail 11 Rani Pass =$F3="Fail" ✗ one row too high Rows 4, 6, 9 (Ruhi Bagale, Amir, Ravina) are red; the Fail rows stay white

Figure 2. Left: =$F2 with A2:G11 selected colours the Fail rows 5, 7 and 10. Right: =$F3 with the same selection colours rows 4, 6 and 9, one row above each Fail.

Classroom line

Let's see how to do it; pay attention.

5. Without the dollar

What if you type =F2="Fail", without the dollar? Now the column moves too. A2 checks F2, but B2 checks G2, C2 checks H2, and so on. Only the cells in column A look at the Result column, so only the names turn red and the rest of the row stays white (see the No dollar sheet). Press F4 while the cursor is on F2 until it shows $F2.

6. A second rule: 80% and above in green

Rules can be stacked. With A2:G11 still selected:

  1. Home > Conditional Formatting > New Rule > Use a formula.
  2. Type =$G2>=80.
  3. Format... > Fill, light green, OK, OK.

Rows 2, 3 and 4 (86.33, 83.67 and 90.00) turn green. The formula can use any comparison: >=, <=, <> (not equal), or even functions, such as =AND($C2>=35,$D2>=35).

When two rules both try to set the same thing, such as the fill colour, the rule higher in the Manage Rules list wins. Use the up and down arrows in Manage Rules to change the order. Here no row is both Fail and above 80, so there is no conflict.

Ravindra Bagale's Tip

Not sure what your rule will colour? Type the same formula in an empty column, such as =$F2="Fail" in I2, and drag it down. Every TRUE is a row that will be coloured. When the TRUE rows are right, copy the formula into the rule.

7. Manage, edit and clear rules

  1. Home > Conditional Formatting > Manage Rules lists the rules. Choose This Worksheet at the top to see all of them.
  2. Edit Rule opens the formula and the format, so you can change "Fail" to "Pass" or the red to orange.
  3. Applies to shows the range, such as =$A$2:$G$11. If you add rows 12 to 20, change it to =$A$2:$G$20.
  4. Delete Rule removes one rule.
  5. Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells or from Entire Sheet removes them all.

One trap: if your formula appears in Edit Rule as ="=$F2=""Fail""" with extra quotes around it, Excel has stored it as text, and it will never be TRUE. Delete the outer quotes so that it reads =$F2="Fail" and click OK.

8. The whole chapter on one page

You want Formula (A2:G11 selected) Rows coloured
Fail rows red =$F2="Fail" 5, 7, 10
80% and above green =$G2>=80 2, 3, 4
Wrong: one row too far =$F3="Fail" 4, 6, 9 (above each Fail)
Wrong: no dollar =F2="Fail" only column A of the Fail rows
  1. Select the data rows (no header), with the first row's cell active.
  2. Use the formula option of New Rule.
  3. Dollar on the column, no dollar on the row: $F2.
  4. The row in the formula equals the first row of the selection.
  5. Higher rules win when two rules set the same format.

9. Try at home

Try at home

Task 1

  1. Colour the whole row orange where Maths is below 35. Which row? (Row 5, Shahrukh, Maths 30.)

Task 2

  1. Colour the whole row where Total is 250 or more. How many rows? (3: Ravindra Bagale, Shraddha Bagale and Ruhi Bagale.)

Task 3

  1. On the Shifted rule sheet, open Manage Rules and fix the formula so that the Fail rows turn red.

Recap

In short

  1. Built-in rules colour one cell; a formula rule can colour the whole row.
  2. Select A2:G11 and use =$F2="Fail".
  3. $F keeps the column fixed; the row number moves.
  4. The formula's row must be the selection's first row.
  5. Manage Rules edits, reorders and fixes the range.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: Highlight a whole row with a formula rule. Example data table with What to select: A2:G4; Rule: colour rows where Result is Fail: row 3 coloured; Why $F2 and not $F$2?: row moves, column fixed.
Card 1 of 2: Highlight a whole row with a formula rule · Download PNG
Revision card: Formula rules: percent, date and text. Example data table with Highlight 80% and above: row 2; Highlight past due dates: row 2; Highlight Fail: row 3.
Card 2 of 2: Formula rules: percent, date and text · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Pass or fail (Easy): "Pass" if B2 is at least 40, else "Fail".
  2. Total (Easy): B2 plus C2.
  3. Passed score 1 (Easy): How many in B2:B11 are 40 or more?
  4. Passed both (Medium): TRUE if B3 and C3 are both at least 40.
  5. Passed either (Medium): TRUE if B4 or C4 is at least 75.
  6. Grade (Medium): Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible (Medium): "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both (Hard): How many have both scores at least 40?
  9. Pass percentage (Hard): Share passing score 1, rounded to 2 decimals.
  10. Nested IF band (Hard): "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer (Hard): Name with the highest score 1.

See all practice tasks in the Task Hub →