Using Custom Formulas in Conditional Formatting0%

Text Functions (LEFT, RIGHT, MID, TRIM, LEN)

Using Custom Formulas in Conditional Formatting

Beginner12 min read•Updated: Jul 11, 2026

Using Custom Formulas in Conditional Formatting

While preset conditional formatting rules inspect each cell individually, Formula-Based Conditional Formatting unlocks total analytical freedom. By writing a formula that evaluates to TRUE or FALSE, you can highlight entire rows, cross-reference external cells, check multi-criteria conditions, and highlight weekend dates automatically.

1. The Core Rule: Formulas Must Evaluate to TRUE or FALSE

When you choose Use a formula to determine which cells to format, Excel tests each cell against your expression. If the expression returns TRUE (or any non-zero number), the format is applied; if it returns FALSE (or 0), formatting is skipped.

2. Highlighting an Entire Row Based on One Column's Value

The most requested conditional formatting feature in enterprise reporting is highlighting the entire table row when a specific condition is met (e.g., highlighting all rows where Status is 'Overdue'):

Step-by-Step Instructions:

1
Select your entire data range excluding headers (e.g., 'A2:G100'). Ensure active cell focus is on A2.
2
Click Home > Conditional Formatting > New Rule.
3
Select Use a formula to determine which cells to format.
4
In the formula box, type:

1   =$E2="Overdue"

1
Notice the Dollar Sign Placement:

'$E2' locks Column E with the dollar sign so that cells in columns A, B, C, D, E, F, and G all look at Column E to decide whether to color themselves.
Row '2' has no dollar sign, allowing Excel to inspect row 2 for row 2, row 3 for row 3, and so forth down the table.

1
Click Format..., choose your fill color (e.g., soft red), and click OK.

3. Highlighting Rows Between Two Dates or Weekends

Highlighting Weekends (Saturdays and Sundays):

To shade date columns when a date falls on a weekend:

1=WEEKDAY(A2, 2) > 5

(In return type 2, Monday = 1 through Saturday = 6 and Sunday = 7, so values > 5 represent weekend days).

Multi-Condition Highlighting with AND / OR:

Highlight orders from the 'North' region that exceed 10,000 in revenue:

1=AND($B2="North", $D2>10000)

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In formula-based conditional formatting, what value must your formula return for the formatting to be applied?

Question 2

To highlight an entire row from A2:G100 if the Status in column E is 'Completed', which formula must be entered?

Question 3

What happens if you accidentally use '=$E$2="Completed"' when applying a row-highlighting rule across A2:G100?

Question 4

Which formula highlights rows where the delivery date in column C is earlier than today's date?

Question 5

Why must the formula in the Conditional Formatting dialog be written relative to the very first top-left cell of the selected range?

Next Lesson

Text Functions (LEFT, RIGHT, MID, TRIM, LEN)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Data Bars, Color Scales, and Icon Sets	Text Functions (LEFT, RIGHT, MID, TRIM, LEN)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Text Functions (LEFT, RIGHT, MID, TRIM, LEN)

Using Custom Formulas in Conditional Formatting

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Using Custom Formulas in Conditional Formatting

1. The Core Rule: Formulas Must Evaluate to TRUE or FALSE

2. Highlighting an Entire Row Based on One Column's Value

Step-by-Step Instructions:

1
Select your entire data range excluding headers (e.g., 'A2:G100'). Ensure active cell focus is on A2.
2
Click Home > Conditional Formatting > New Rule.
3
Select Use a formula to determine which cells to format.
4
In the formula box, type:

1   =$E2="Overdue"

1
Notice the Dollar Sign Placement:

'$E2' locks Column E with the dollar sign so that cells in columns A, B, C, D, E, F, and G all look at Column E to decide whether to color themselves.
Row '2' has no dollar sign, allowing Excel to inspect row 2 for row 2, row 3 for row 3, and so forth down the table.

1
Click Format..., choose your fill color (e.g., soft red), and click OK.

3. Highlighting Rows Between Two Dates or Weekends

Highlighting Weekends (Saturdays and Sundays):

To shade date columns when a date falls on a weekend:

1=WEEKDAY(A2, 2) > 5

(In return type 2, Monday = 1 through Saturday = 6 and Sunday = 7, so values > 5 represent weekend days).

Multi-Condition Highlighting with AND / OR:

Highlight orders from the 'North' region that exceed 10,000 in revenue:

1=AND($B2="North", $D2>10000)

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In formula-based conditional formatting, what value must your formula return for the formatting to be applied?

Question 2

To highlight an entire row from A2:G100 if the Status in column E is 'Completed', which formula must be entered?

Question 3

What happens if you accidentally use '=$E$2="Completed"' when applying a row-highlighting rule across A2:G100?

Question 4

Which formula highlights rows where the delivery date in column C is earlier than today's date?

Question 5

Why must the formula in the Conditional Formatting dialog be written relative to the very first top-left cell of the selected range?

Next Lesson

Text Functions (LEFT, RIGHT, MID, TRIM, LEN)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Data Bars, Color Scales, and Icon Sets	Text Functions (LEFT, RIGHT, MID, TRIM, LEN)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next