howto.org.in

How to Apply Conditional Formatting in Excel

Highlight values, duplicates, dates or formula-based conditions in Excel and manage rule order and cell references.

Excel & Google SheetsUpdated 2026-08-08Edited by Pramod TiwariReviewed by Pramod TiwariMicrosoft Excel; verified 8 August 2026
2 min read
Process overview for Apply Conditional Formatting in Excel
A concise overview of the main steps in this guide.

Quick Answer

Select the target range, open Conditional Formatting, choose a built-in rule or New Rule, define the condition and format, confirm the Applies to range, then test boundary values and review rule order in Manage Rules.

What You Need

  • Excel worksheet
  • Target data range
  • A condition and visual format

Safety Precautions

  • Use color with labels or icons when meaning is important.
  • Avoid too many competing rules.
  • Check that formulas use the correct relative row.

Step-by-Step Instructions

  1. Step 1

    Select the complete range

    Include all rows that should receive the rule.

  2. Step 2

    Choose a rule type

    Use highlight, top/bottom, data bars, color scales, icons or a formula.

  3. Step 3

    Define the condition

    Enter the value, comparison or true/false formula.

  4. Step 4

    Choose an accessible format

    Use restrained color plus another cue when possible.

  5. Step 5

    Check Applies to

    Confirm the rule covers the intended cells.

  6. Step 6

    Manage and test rules

    Review priority, Stop If True behavior and boundary examples.

Practical Example

To flag duplicate IDs in A2:A400, create a formula rule using =COUNTIF($A$2:$A$400,A2)>1 and apply it to the full range.

Common Mistakes

  • Applying the rule to only one cell
  • Using the wrong relative reference
  • Creating conflicting rule order

Troubleshooting

Formatting shifts by one row

Write the formula relative to the top-left cell of the Applies to range.

A rule never appears

Check priority, Stop If True and whether another rule overrides the format.

Sources and Review

Reviewed by Pramod Tiwari, Editor; official-source verification on 2026-08-08.

FAQs

Can conditional formatting detect duplicates?

Yes, use the built-in duplicate rule or a COUNTIF formula.

Can a formula drive the rule?

Yes, the formula must evaluate to true or false for each relevant cell.

Why does copied formatting change?

Relative references move; lock only the row or column that should stay fixed.

Help us improve this guide

Tell us about an outdated instruction, unclear step, broken link or safety concern. Do not include passwords, account numbers, medical records or other sensitive information.

Email a correction or suggestion