To highlight an entire row in Excel based on a single cell value, use conditional formatting with a formula. 1) Select the data range like A2:F100. 2) Go to Conditional Formatting > New Rule > "Use a formula". 3) Enter =$C2="Complete" (adjusting column and value as needed). 4) Set the format and click OK. The formula uses mixed references—$C locks the column while 2 adjusts per row. Customize the condition using operators or functions like 100. Ensure the rule is applied correctly across sheets or tables.

If you want to highlight an entire row in Excel based on the value of a single cell, you're probably looking for a way to apply conditional formatting across that row dynamically. This is super useful when managing data like task lists, schedules, or inventory — where seeing important rows stand out visually can save time and reduce errors.

Let’s walk through how to do it step by step.

Set up Conditional Formatting with a Formula

Excel's built-in conditional formatting rules usually work on a per-cell basis. But if you want to highlight an entire row based on one cell (like highlighting all columns in row 5 because cell C5 says "Complete"), you need to use a formula-based rule.

Here’s how:

1. Select the range you want to highlight — for example, A2:F100 (assuming you have six columns and 99 rows of data).
2. Go to the Home tab > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter the formula:

   =$C2="Complete"

   (Change C2 to the column and row of your trigger cell.)

5. Click Format, choose your highlight color, then click OK.

This tells Excel to check if cell C in each row equals "Complete", and if so, highlight the whole selected row.

How the Formula Works (and Why Absolute References Matter)

The key part of this trick is using mixed references — like $C2. The dollar sign before the column letter ($C) keeps the reference locked to column C no matter where the formatting applies. But the row number (2) is relative, so it adjusts for each row.

Without the $, dragging or applying across multiple cells would shift both the column and row, and the rule wouldn’t work as intended.

So if you're starting from A2 and go to F100, Excel will automatically evaluate:

• Row 2: $C2="Complete"
• Row 3: $C3="Complete"
• Row 4: $C4="Complete"
  ...and so on.

This makes the rule dynamic and reusable across many rows.

Customizing the Rule for Different Values or Conditions

You don’t have to use "Complete" — you can adjust the formula to match any condition.

Examples:

• Highlight rows where the status is "Pending":
  =$D2="Pending"
• Highlight rows where a date is today or earlier:
  =$E2
• Highlight rows where a number is greater than 100:
  =$F2>100

Just remember to:

• Always lock the column with a $ if you’re checking only one specific column
• Make sure the starting row matches your data range
• Use proper operators like =, <code>, <code>>, <code>, <code> depending on your logic

Apply It Across Multiple Sheets or Tables

Once you’ve created the rule, you can reuse it in other areas or even copy the formatted cells to another sheet and adjust the formula accordingly.

Also, if you're working with Excel tables, the formatting should carry over automatically when new rows are added — assuming your formula uses structured references correctly (like [@Status] instead of $C2).

But for most users, sticking with basic cell references is simpler and works just fine.

That’s basically how it works. Once you understand how to mix absolute and relative references in conditional formatting formulas, this becomes a powerful tool in your Excel toolkit.

The above is the detailed content of excel formula to highlight a row based on a cell value. For more information, please follow other related articles on the PHP Chinese website!