Excel Conditional Formatting Based on Another Cell Value

Sharing is caring!

Conditional Formatting in Microsoft Excel allows users to highlight data automatically based on specific rules. One powerful feature is formatting a cell based on the value of another cell. This is especially useful in dashboards, data tracking sheets, and when comparing datasets.

In this guide, we will explain how to use Conditional Formatting based on another cell, with step-by-step instructions, practical examples, and Excel best practices.

Why Use Conditional Formatting Based on Another Cell?

Formatting based on another cell is valuable for:

• Tracking status updates (e.g., project done or pending)
• Comparing values between columns
• Highlighting mismatched data
• Conditional row formatting
• Creating interactive data reports

This feature uses formulas to control how formatting is applied, which adds flexibility and power.

Basic Steps to Format a Cell Based on Another Cell

Let’s start with the standard process:

Step 1: Select the Range to Format

1. Open your Excel sheet.
2. Highlight the range of cells you want to format.

Example: If you’re working with rows A2:A10 but want the formatting to be triggered by values in B2:B10, select A2:A10.

Step 2: Open Conditional Formatting

1. Go to the Home tab on the ribbon.
2. Click on Conditional Formatting.
3. Choose New Rule.

Step 3: Choose “Use a Formula to Determine Which Cells to Format”

This is the key step where Excel lets you control formatting using custom formulas.

Step 4: Enter a Formula

Use a formula that refers to the other cell you want to base your condition on.

Example:

=$B2="Complete"

This formula means: format the cell in Column A if the value in the same row of Column B is “Complete”.

Step 5: Set the Formatting Style

Click Format, then choose your desired style:

• Font color
• Fill color
• Bold, Italic, Borders

Click OK to apply the rule.

Practical Examples of Conditional Formatting Based on Another Cell

Here are some common use cases with formulas:

Example 1: Highlight Row if Status is “Overdue”

To highlight the full row when Status is “Overdue”:

1. Select A2:C4.
2. Use formula: =$C2="Overdue"
3. Choose red fill or bold font.

This helps visually identify overdue tasks in a list.

Example 2: Color Code Based on Priority

To apply different colors for each priority level:

Use multiple rules to assign different colors to each level of task priority.

Example 3: Compare Values Between Two Columns

To highlight cells where Actual > Planned:

1. Select B2:B4.
2. Use formula: =B2>A2

This helps identify where actual work exceeded the estimate.

Using Absolute and Relative References in Formulas

Understanding cell references is essential when applying formatting rules.

Tip: When using conditional formatting for each row, use mixed references like $B2 to fix the column but allow the row to change.

Apply Formatting to Entire Row Based on One Cell

To highlight the entire row based on one column:

1. Select the entire range of rows (e.g., A2:D100).
2. Use formula like: =$C2="Pending"

This helps with formatting full rows for tracking tasks, inventory, or attendance.

Conditional Formatting for Dates Based on Another Cell

Suppose you want to highlight dates that are past a deadline listed in another column.

To highlight where the Start Date > Deadline:

1. Select A2:A4.
2. Use formula: =A2>B2

Now only rows with missed deadlines will be highlighted.

Using Conditional Formatting with AND and OR

You can combine multiple conditions using logical functions:

Example: Highlight if “Status = Pending” and “Priority = High”

Formula:

=AND($B2="High", $C2="Pending")

Only the first row will be highlighted.

Example: Highlight if “Status is Overdue” OR “Priority is High”

=OR($B2="High", $C2="Overdue")

This captures multiple alert conditions in one rule.

Color Scales, Icon Sets, and Data Bars with Referenced Cells

Although Color Scales and Icon Sets typically use the values within the selected range, you can manipulate results by using helper columns.

For example, calculate difference in a helper column:

Then apply a Color Scale to the Difference column to visually show negative or positive performance.

Best Practices for Conditional Formatting Based on Another Cell

1. Keep formulas simple: Avoid overly complex formulas that are hard to maintain.
2. Use named ranges: Helps in understanding and managing rules better.
3. Document your rules: Keep a note in your sheet explaining what the formatting does.
4. Avoid overlapping rules: Too many conditions can create conflicts.
5. Use helper columns: For advanced logic, calculate results in hidden columns first.

Limitations to Consider

• Conditional Formatting rules do not support cross-sheet references.
• Too many rules can slow down large spreadsheets.
• If copying formatted cells, Excel may adjust formulas in unexpected ways.
• Does not support merged cells well.

Summary of Common Conditional Formatting Formulas

Final Thoughts

Using Conditional Formatting based on another cell is a powerful Excel feature that improves how you present and understand data.

With the right formulas, you can highlight tasks, flag issues, and create more meaningful reports. Whether you’re tracking project statuses, comparing values, or organizing your spreadsheet, mastering these techniques helps you get more value from Excel.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.