Conditional Formatting Based on Another Cell

Yes. In Excel and Google Sheets, conditional formatting can use the value in a different cell to decide whether to format a cell or range. The key is to create a rule with a formula that refers to the comparison cell, using relative or absolute references depending on how the rule should move across the range.

How conditional formatting uses another cell

A conditional-formatting rule evaluates a condition for each cell it applies to. A formula-based rule can inspect a different cell, then apply a fill, text color, border, or other available style when the formula evaluates to TRUE. The cell being checked does not have to be part of the range that gets formatted.

For example, suppose column A contains employee names and column B contains task status. To highlight a name when its status is “Overdue,” select the name cells and use a formula that checks the corresponding status cell. The format appears on the name cell, even though the rule’s condition reads column B.

This is different from a built-in rule such as “Greater Than,” which generally compares the selected cells with a fixed value. A custom formula provides more control: it can compare cells, check text, combine conditions, or refer to a separate control cell.

Set up a rule in Excel

In desktop Excel, select the cells that should change appearance, then open Home > Conditional Formatting > New Rule. Choose the option to use a formula to determine which cells to format. Enter a formula that returns TRUE or FALSE, choose a format, and confirm the rule.

For the example where A2:A100 contains names and B2:B100 contains statuses, select A2:A100 and enter:

=$B2="Overdue"

The dollar sign fixes the status column at B, while the row number remains relative. As Excel evaluates the rule for A3, it checks B3; for A4, it checks B4. Select the range before creating the rule so its starting row matches the formula’s row reference.

To highlight a whole row based on the status in column B, select the rows to format, such as A2:D100, and use the same formula. Each row’s status controls formatting across that row. Check the rule’s “Applies to” range in the Conditional Formatting Rules Manager if the result is shifted or incomplete.

Set up a rule in Google Sheets

In Google Sheets, select the target range and choose Format > Conditional formatting. In the side panel, select Custom formula is under the format-rule options, enter the formula, choose a style, and click Done.

For example, to color A2:A100 when the corresponding cell in B says “Overdue,” use:

=$B2="Overdue"

For a whole-row rule, set the range to A2:D100 and use the same formula. In both spreadsheet applications, formulas begin with an equals sign and should be tested against the first row of the selected range.

Relative and absolute references

References determine how a formula changes as a rule is applied to other cells. A reference without dollar signs is relative: B2 can shift by row and column. A reference with a dollar sign is absolute in that dimension: $B$2 always points to B2. Mixed references lock only one part:

• $B2 fixes column B but lets the row change. This is common when one column controls formatting down a list.
• B$2 fixes row 2 but lets the column change. This can help when a row of values controls formatting across columns.
• $B$2 always checks one fixed cell, useful when a single setting or threshold controls an entire range.

For instance, to highlight values in C2:C50 when they exceed the threshold stored in F1, select C2:C50 and use =C2>$F$1. The changing C2 reference tests each cell in the target range, while the fixed F1 reference keeps the threshold constant.

Useful examples and variations

Formula rules can test numbers, text, dates, and combinations of conditions. These examples assume the selected range starts on row 2; adjust the row references to match your actual range.

• Highlight a due date that has passed: for dates in C2:C100, use =AND(C2<TODAY(),C2<>""). The second condition avoids treating blank cells as overdue.
• Format a row when a checkbox is checked: if checkboxes are in D2:D100, select the row range and use =$D2=TRUE.
• Highlight a value when another cell is blank: to format A2:A100 based on B, use =$B2="".
• Require two conditions: use =AND($B2="Open",$C2<TODAY()) to flag rows marked “Open” whose date in column C is before today.

Functions such as AND and OR let a rule combine tests. For more complex conditions, make sure the formula returns a logical result and confirm that text is enclosed in quotation marks.

Common problems and how to fix them

A rule that highlights the wrong row is often using a row reference that does not match the first row in the selected range. If the range starts at row 2, a formula referencing B1 will test the preceding row. Correct the starting reference, or change the range so the formula and range align.

Another frequent issue is locking too much or too little. Using $B$2 when each row should check its own status makes every row depend on one cell. Using B2 when the rule must always inspect column B can cause the checked column to shift as the formatting moves across the row.

Also check that the data matches the test. “Overdue” is not the same as “overdue,” and dates stored as text may not behave like actual dates. In Excel, use the Rules Manager to inspect the formula and applied range. In Sheets, review both the selected range and the custom formula in the conditional-formatting panel.

Conditional formatting changes presentation, not the underlying cell value. A red fill does not change a date, mark a task as overdue in the data, or affect calculations unless a formula separately uses the cell’s contents.

Frequently Asked Questions

Can conditional formatting check a cell on another sheet?
Yes, but the reference syntax depends on the spreadsheet application. In Excel, direct references to another worksheet may require the sheet name, such as =Sheet2!$B$2="Yes"; names containing spaces need apostrophes around the sheet name. Google Sheets may require a direct reference or a function such as INDIRECT in custom-formula cases. Check the application’s accepted formula syntax if a direct reference is rejected.

Can one cell control the formatting of an entire row?
Yes. Select the row range, then write a formula that fixes the controlling column but leaves the row relative. For example, =$B2="Complete" formats each selected row according to its column B value.

Can I compare two cells directly?
Yes. To highlight C2:C100 when each value is greater than the corresponding value in D, select C2:C100 and use =C2>D2. Keep both row references relative so each pair is compared.

Why is every row using the same result?
The formula may contain an absolute row reference, such as $B$2, so every row checks the same cell. If the rule should check the matching row, use a relative row reference such as $B2.

Can a rule format one cell based on whether another cell contains text?
Yes. A formula such as =$B2="Ready" checks for an exact match. For a partial text match, a function such as SEARCH can be used in a formula, but behavior can differ slightly between spreadsheet applications.

Related

• Fix “The File Is Corrupt and Cannot Be Opened” in Office
• Line Transfer to a New Phone: Steps and Troubleshooting
• Federal Contractor Minimum Wage: Is It Rising to $14.05?