Creating Custom Conditional Formatting Rules in Excel

Course: Advanced Excel

Topic: Creating Custom Conditional Formatting Rules

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Custom Conditional Formatting

Conditional Formatting is an Excel feature that automatically changes the appearance of cells when specific conditions are met. It helps users identify important values, detect errors, highlight trends, compare results, and analyze large datasets without manually formatting every cell.

Custom Conditional Formatting rules allow users to define their own conditions instead of relying only on Excel's built-in options. These rules can use numerical comparisons, text conditions, dates, duplicate values, formulas, and combinations of multiple criteria.

For example, a sales manager can highlight sales above ₹50,000 in green, overdue payments in red, duplicate invoice numbers in orange, and low inventory in yellow. These formats update when the underlying values change.

2. Learning Objectives

• Understand the purpose and benefits of Conditional Formatting.
• Create custom rules using numerical and text conditions.
• Apply formula-based Conditional Formatting to individual cells and entire rows.
• Combine conditions using AND, OR, IF, COUNTIF, and other functions.
• Manage rule priority, overlapping rules, and Stop If True behavior.
• Use Conditional Formatting for sales reports, attendance sheets, financial records, and inventory management.
• Troubleshoot incorrect formatting and build maintainable spreadsheets.

3. What Is Conditional Formatting?

Conditional Formatting applies a visual format to cells when a defined condition is true. Depending on the rule, Excel can change the font color, fill color, borders, or other supported formatting attributes.

Common Conditional Formatting Options

4. Understanding Custom Rules

A custom Conditional Formatting rule consists of three main elements:

1. Condition: The test Excel performs, such as checking whether a value is below 100.
2. Applies To range: The cells that the rule can format.
3. Format: The visual appearance applied when the condition is true.

For example, a rule can check whether a stock quantity is less than 10, apply to the Inventory Quantity column, and display qualifying cells with a red fill and bold text.

5. Preparing Sample Data

Use the following sample dataset to practice creating custom rules.

Assume the headers are in row 1 and the data is in rows 2 through 6. The formulas in the following examples refer to these positions. Adjust the cell references if your worksheet uses a different layout.

6. Creating a Custom Rule Using Cell Values

Example: Highlight Sales Above ₹50,000

1. Select the Sales cells, such as C2:C6.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
3. Enter 50000.
4. Choose a suitable format or select Custom Format.
5. Choose a fill color, font color, or border.
6. Click OK to apply the rule.

Excel highlights all sales values greater than ₹50,000. If a sales amount changes later, Excel recalculates the condition and updates the formatting.

Using the New Rule Dialog

For more flexibility, select the range and choose Home > Conditional Formatting > New Rule. Select the rule type that matches your requirement, configure the condition, and define the format.

7. Creating a Formula-Based Conditional Formatting Rule

Formula-based rules are among the most powerful Conditional Formatting options. They allow you to define custom logic using a formula that returns TRUE or FALSE.

Example: Highlight Sales Above Target

Suppose Sales are in column C and Target is in column D. To highlight sales values that exceed their targets:

1. Select C2:C6.
2. Open Home > Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the formula below.

=C2>D2

5. Click Format and select the desired formatting.
6. Click OK to save the rule.

Excel evaluates the formula for each cell in the selected range. The relative row references adjust automatically, so C3 is compared with D3, C4 with D4, and so on.

8. Relative, Absolute, and Mixed References

Correct cell references are essential when applying a formula to a range. Excel adjusts relative references as the rule evaluates different cells.

Example: Compare Sales with a Fixed Target

If cell H1 contains a target value and the sales range is C2:C6, use:

=C2>$H$1

The sales cell changes as the rule evaluates each row, while the reference to H1 stays fixed.

9. Highlighting an Entire Row Based on a Condition

Custom rules can format entire records instead of only the cell that contains the condition.

Example: Highlight Rows Where Status Is Overdue

Assume the Status column is E and the data occupies A2:F6.

1. Select the full data range A2:F6.
2. Create a new formula-based Conditional Formatting rule.
3. Enter the following formula.

=$E2="Overdue"

4. Choose a light red fill and readable font color.
5. Confirm that the Applies To range is =$A$2:$F$6.
6. Click OK.

The dollar sign before E fixes the status column, while the row number remains relative. As a result, each row is formatted according to its own Status value.

10. Using AND and OR in Custom Rules

AND and OR allow multiple conditions to be evaluated in a single rule.

10.1 AND: Meet All Conditions

Highlight sales above ₹50,000 only when the status is Completed:

=AND($C2>50000,$E2="Completed")

Both conditions must be true for the row to be formatted.

10.2 OR: Meet Any Condition

Highlight records when sales are below ₹40,000 or the status is Overdue:

=OR($C2<40000,$E2="Overdue")

The row is highlighted when either condition is true.

10.3 Combining Multiple Conditions

Highlight sales above target when the employee belongs to the Sales department:

=AND($B2="Sales",$C2>$D2)

Apply this rule to the required record range, such as A2:F6, to highlight the complete row.

11. Creating Rules for Text Values

Text-based Conditional Formatting is useful for tracking workflow stages, employee categories, product types, and approval statuses.

11.1 Highlight Cells Containing a Word

1. Select the Status range, such as E2:E6.
2. Choose Home > Conditional Formatting > Highlight Cells Rules > Text That Contains.
3. Enter Pending.
4. Choose a format and confirm.

11.2 Use a Formula for Exact Text Matching

To highlight cells in E2:E6 that exactly match Completed, use:

=E2="Completed"

Apply this formula to E2:E6. For an entire-row rule, select the full data range and use:

=$E2="Completed"

11.3 Highlight Cells That Are Not Blank

To format cells in A2:A100 that contain values, use:

=A2<>""

This is useful for identifying populated records. If cells contain formulas that return an empty string, test the rule against the actual behavior of those formulas.

12. Highlighting Duplicate and Unique Values

Excel can identify repeated values using built-in rules or formula-based rules.

12.1 Built-In Duplicate Rule

1. Select the range containing invoice numbers, product IDs, or email addresses.
2. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose Duplicate or Unique.
4. Select a format and click OK.

12.2 Formula-Based Duplicate Detection

To highlight repeated values in A2:A100, use:

=COUNTIF($A$2:$A$100,A2)>1

The formula counts how many times the current value appears in the specified range. If the count is greater than one, the value is highlighted.

12.3 Highlight Only the Second and Later Occurrences

To leave the first occurrence unformatted and highlight subsequent occurrences, use:

=COUNTIF($A$2:A2,A2)>1

This formula counts occurrences from the first data row through the current row. It is useful when reviewing repeated invoice numbers or transaction IDs.

13. Conditional Formatting for Dates

Date-based rules can identify overdue tasks, upcoming deadlines, expired contracts, and records that fall within a reporting period.

13.1 Highlight Overdue Dates

Suppose due dates are in column F and task status is in column E. To highlight overdue rows that are not completed, select A2:F100 and use:

=AND($F2<TODAY(),$E2<>"Completed",$F2<>"")

The rule highlights rows with a due date before today, excluding completed tasks and blank due dates.

13.2 Highlight Dates Due Within Seven Days

To highlight upcoming deadlines from today through the next seven days, use:

=AND($F2>=TODAY(),$F2<=TODAY()+7)

Apply the rule to the required date range or entire records. The formula updates as the current date changes.

13.3 Highlight Expired Dates

To highlight dates that are earlier than today's date:

=AND(F2<TODAY(),F2<>"")

Apply this formula to the date cells. Ensure that the cells contain real Excel dates rather than text that only looks like a date.

14. Conditional Formatting with COUNTIF and COUNTIFS

COUNTIF and COUNTIFS are useful for detecting repeated values and records that meet one or more criteria.

14.1 Highlight Products with Low Stock

Suppose product names are in A2:A100. To highlight a product name if it appears more than five times, use:

=COUNTIF($A$2:$A$100,A2)>5

This example detects frequently repeated names. For actual low-stock monitoring, apply a numeric threshold to the stock quantity column instead.

14.2 Highlight Rows Matching Multiple Conditions

Suppose column B contains the department and column E contains the status. To highlight rows where the department is Finance and status is Pending, use:

=COUNTIFS($B2,"Finance",$E2,"Pending")>0

Alternatively, for a straightforward row-level test, use:

=AND($B2="Finance",$E2="Pending")

Both formulas can identify rows matching the two criteria. The AND version is often simpler when testing fixed values in the current row.

15. Using Conditional Formatting for Numerical Thresholds

Custom numerical rules can classify performance, monitor budgets, and identify values that fall outside acceptable limits.

Choose formatting colors consistently so users can understand the report without needing to inspect every value.

16. Applying Multiple Conditional Formatting Rules

A range can have multiple rules. For example, the same sales column may have one rule for values below target, another for values above target, and a third for exceptionally high sales.

Example Rules

To manage these rules, select the affected cells and open Home > Conditional Formatting > Manage Rules. Review the rule order and the Applies To range. If rules use overlapping formats, the order can affect the final appearance.

17. Understanding Rule Priority and Stop If True

When several rules apply to the same cell, Excel evaluates them according to their priority in the Rules Manager. Higher-priority rules appear toward the top of the list.

• Rule priority: Determines the order in which rules are evaluated.
• Move Up/Move Down: Changes the position of a selected rule.
• Stop If True: Prevents lower-priority rules from being evaluated for a cell when the selected rule is true, where this option is available.
• Applies To: Defines the cells affected by the rule.

For example, a critical overdue rule may need priority over a general status-color rule. Place the overdue rule higher in the list and configure Stop If True when the intended behavior requires lower-priority rules to be skipped.

Be careful when using Stop If True. It can prevent other useful formatting from appearing, so test overlapping rules after changing their priority.

18. Managing and Editing Existing Rules

1. Select a cell or range affected by the rules.
2. Go to Home > Conditional Formatting > Manage Rules.
3. Choose whether to view rules for the current selection or the entire worksheet, depending on the available options.
4. Select a rule and click Edit Rule to modify the condition or format.
5. Check the Applies To range to ensure that the correct cells are included.
6. Use Move Up or Move Down to adjust rule priority.
7. Delete rules that are no longer required.
8. Click Apply and then OK to confirm changes.

When a rule appears not to work, inspect the Applies To range first. A correct formula will not produce the expected result if it is applied to the wrong cells.

19. Using Conditional Formatting with Excel Tables

Conditional Formatting works with Excel Tables and can be useful for datasets that grow over time. When you apply a rule to a table column, confirm that newly added rows are included in the Applies To range. Excel often expands formatting with a table, but this behavior should be verified in the workbook.

Example: Highlight Low Inventory

Suppose an Inventory table has a column named Stock Quantity. Select the quantity cells and create a rule using:

=A2<10

Here, A2 must be replaced with the first data cell of the selected quantity range. Alternatively, if the table's stock quantity data begins in D2, use =D2<10 for the formula-based rule.

For an Excel Table, structured references can make formulas readable in ordinary worksheet calculations. However, formula-based Conditional Formatting rules generally use conventional cell references in their formulas for dependable relative-reference behavior. Always test the rule in your Excel version and confirm its Applies To range.

20. Common Errors and Troubleshooting

21. Best Practices for Custom Conditional Formatting

• Use formula-based rules when built-in options cannot express the required condition.
• Start with a clearly defined data range and confirm the first cell used in the formula.
• Use absolute references for fixed criteria and relative references for row-by-row calculations.
• Use descriptive worksheet labels to explain thresholds and status categories.
• Keep color choices consistent across worksheets and reports.
• Avoid applying too many rules to very large ranges when simpler rules can achieve the same result.
• Test rules using values that should pass and values that should fail.
• Review overlapping rules and priority when a cell displays an unexpected format.
• Exclude blanks from date and numerical rules when blanks should not be highlighted.
• Use formulas that are easy to maintain and document complex business logic.
• Do not rely on color alone to communicate critical information; include text labels or status indicators when appropriate.

22. Practical Exercises

Exercise 1: Sales Performance

• Highlight sales above ₹50,000 in green.
• Highlight sales below target in red.
• Highlight sales equal to the target in blue.
• Use the Sales and Target columns to create formula-based rules.

Exercise 2: Attendance Monitoring

Create an attendance sheet with Employee Name, Attendance Date, and Attendance Status. Highlight Absent records in red and Late records in yellow.

Example formula for a status column in C, starting at row 2:

=$C2="Absent"

Exercise 3: Overdue Payments

Create a payment tracker with Invoice Number, Due Date, Amount, and Payment Status. Highlight rows where the due date has passed and the invoice is not paid.

=AND($B2<TODAY(),$D2<>"Paid",$B2<>"")

Exercise 4: Duplicate Invoice Numbers

Enter invoice numbers in column A and highlight duplicates using:

=COUNTIF($A$2:$A$100,A2)>1

Exercise 5: Conditional Formatting Dashboard

• Create a sales dataset with at least 50 records.
• Highlight high-value transactions.
• Highlight overdue orders.
• Identify duplicate transaction IDs.
• Apply a color scale to sales values.
• Review the rules in Manage Rules and test them after changing data.

23. Mini Project: Employee Performance and Deadline Tracker

Project Goal: Build an Excel worksheet that uses custom Conditional Formatting rules to make performance and deadline issues immediately visible.

Dataset Fields

• Employee ID
• Employee Name
• Department
• Sales Achieved
• Sales Target
• Task Due Date
• Task Status

Required Rules

1. Highlight Sales Achieved above Sales Target in green.
2. Highlight sales below target in red.
3. Highlight complete rows for overdue tasks that are not completed.
4. Highlight tasks due within the next seven days.
5. Highlight duplicate Employee IDs.
6. Arrange rule priorities so overdue-task formatting is not unintentionally overridden.

Suggested Formulas

Sales above target, with Sales Achieved in D and Sales Target in E:

=$D2>$E2

Sales below target:

=$D2<$E2

Overdue task, with Task Due Date in F and Task Status in G:

=AND($F2<TODAY(),$G2<>"Completed",$F2<>"")

Task due within seven days:

=AND($F2>=TODAY(),$F2<=TODAY()+7,$G2<>"Completed")

Duplicate Employee ID in column A:

=COUNTIF($A$2:$A$100,A2)>1

Expected Outcome: The worksheet should make successful performance, below-target sales, duplicate IDs, and approaching or overdue deadlines easy to identify. Validate each rule with sample records before using the tracker for actual reporting.

24. Interview Questions and Answers

Q1. What is Conditional Formatting in Excel?

Answer: Conditional Formatting automatically applies visual formatting to cells when specified conditions are satisfied.

Q2. What is a custom Conditional Formatting rule?

Answer: It is a user-defined rule that applies formatting based on a numerical comparison, text condition, date condition, or formula.

Q3. What is the purpose of formula-based Conditional Formatting?

Answer: It enables complex conditions, including comparisons across columns, multiple logical tests, duplicate detection, and whole-row highlighting.

Q4. How can you highlight an entire row based on a status?

Answer: Select the entire data range and create a formula-based rule such as =$E2="Overdue", assuming column E contains the status and row 2 is the first data row.

Q5. Why are dollar signs used in Conditional Formatting formulas?

Answer: Dollar signs lock a column or row reference so it does not shift when Excel evaluates the rule across a range.

Q6. How do you highlight duplicate values?

Answer: Use the built-in Duplicate Values rule or a formula such as =COUNTIF($A$2:$A$100,A2)>1.

Q7. What is the difference between AND and OR?

Answer: AND returns TRUE when all conditions are true, while OR returns TRUE when at least one condition is true.

Q8. How do you manage overlapping rules?

Answer: Use Manage Rules to review rule order, Applies To ranges, formatting, and the Stop If True setting.

Q9. How can you highlight overdue tasks?

Answer: Use a date comparison with TODAY(), and combine it with a status check to exclude completed tasks.

Q10. Why might a Conditional Formatting rule fail?

Answer: Common causes include an incorrect formula, wrong cell references, a mismatched Applies To range, text-formatted dates, conflicting rules, or incorrect criteria.

25. Quick Revision Checklist

• Understand the purpose of custom Conditional Formatting rules.
• Create rules through Home > Conditional Formatting > New Rule.
• Use formula-based rules for custom logic.
• Understand relative, absolute, and mixed cell references.
• Use AND and OR to combine multiple conditions.
• Highlight duplicates with COUNTIF.
• Use TODAY() for date-based conditions.
• Apply formulas to entire rows using fixed columns and relative row numbers.
• Manage rule order, Applies To ranges, and Stop If True settings.
• Test rules with both matching and nonmatching values.

26. Conclusion

Creating Custom Conditional Formatting Rules is an essential Advanced Excel skill for building clear, reliable, and interactive reports. Built-in rules are useful for common formatting needs, while formula-based rules allow users to handle more complex business conditions.

By mastering cell references, logical functions, date conditions, duplicate detection, and rule priority, you can automatically identify important records, monitor performance, and improve the readability of large datasets. Practice these techniques with sales reports, attendance sheets, inventory lists, and deadline trackers to strengthen your Excel skills.

27. Course Resources

Continue learning Advanced Excel with these JustAcademy resources:

• Explore the Advanced Excel Course
• Register for a Course Demo
• Visit the JustAcademy Website