Conditional formatting based on another column lets you highlight cells in one column using rules that reference values in a different column, such as flagging sales rows where the target column is below a threshold. You enter the rule with a formula that points to the other column, and the formatting applies to the selected range. This works in Excel, Google Sheets, and similar spreadsheet apps, and it is one of the most powerful ways to make data patterns visible at a glance.
What This Feature Does
Standard conditional formatting compares a cell to its own value—for example, turning a cell red if it is less than 10. But when you base the rule on another column, you can highlight an entire row, a name, a date, or any other cell based on a condition that lives elsewhere. For instance, you might have a list of projects with a “Status” column and a “Due Date” column. You can format the project name in column A to turn red when the due date in column C has passed. The formatting is driven by the value in column C, not by column A itself.
This is useful for dashboards, tracking sheets, inventory lists, and any report where you want to draw attention to records that meet a condition, rather than just individual cells. It also helps avoid clutter: instead of coloring every cell in a row, you can highlight just the key identifier, making the sheet easier to scan.
How It Works in Excel and Google Sheets
Both major spreadsheet applications use the same core idea: you write a formula that returns TRUE or FALSE, and the formatting applies to the cells where the formula is true. The critical detail is the use of absolute and relative references. When you select a range like A2:A100 and write a formula such as =$C2>DATE(2025,1,1), the dollar sign locks the column (C) but lets the row number change as the formula is applied to each row. This is what makes the rule “look across” to the other column for the same row.
In Excel, go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. In Google Sheets, go to Format → Conditional formatting, then under “Format cells if” choose “Custom formula is.” In both, you enter the formula and set the fill or text color.
Here is a step-by-step for Excel:
- Select the range you want to format (for example,
A2:A100). - Click Home → Conditional Formatting → New Rule.
- Choose Use a formula to determine which cells to format.
- Enter a formula that references the other column, like
=$C2>100. - Click Format, pick a fill color, and press OK.
For Google Sheets, the steps are similar: select the range, open the conditional formatting panel, choose “Custom formula is,” and enter the same formula. The formula must be relative to the top-left cell of the selected range. If your range starts at A2, the formula should reference row 2, even if the rule applies to all rows.
Choosing the Right Formula
The formula you write depends on the condition you want to check. Common patterns include:
- Numeric comparison:
=$B2>=100highlights rows where column B is at least 100. - Text match:
=$C2="Overdue"highlights rows where column C contains the exact text “Overdue”. - Date before today:
=$D2 - Blank or not blank:
=$E2=""highlights rows where column E is empty;=$E2<>""highlights non-empty cells. - Using AND/OR:
=AND($B2>100,$C2="Yes")highlights rows that meet both conditions.
When using text, be careful with case sensitivity. In Excel, the default comparison is case-insensitive, so “overdue” and “Overdue” are treated the same. In Google Sheets, the default is also case-insensitive for most functions, but if you need exact case, use EXACT() in Sheets or EXACT() in Excel as well.
Common Mistakes and How to Avoid Them
The most frequent mistake is forgetting the dollar sign on the column reference. If you write =C2>100 without the dollar sign, the formula will shift when applied across the selected range. For example, if your range is A2:A100, the rule for cell A2 will check C2, but for cell A3 it will check C3, which is fine—but for cell B2 (if you had selected a multi-column range) it would check D2, which is probably not what you want. Always lock the column with a dollar sign if the condition always refers to the same column.
Another common issue is using a relative row reference when the selected range does not start at row 1. If you select A2:A100, your formula must reference row 2, not row 1. For example, =$C2>100 is correct; =$C1>100 would check the wrong row for every cell.
Also, be aware that conditional formatting formulas are evaluated as if they were entered in the top-left cell of the selected range. If you select an entire column (A:A), the top-left cell is A1, so your formula should reference row 1. If you select A2:A100, the top-left is A2, so reference row 2.
Using the Feature for Row Highlighting
One of the most popular uses is highlighting entire rows based on a condition in one column. To do this, you select the entire data range (for example, A2:F100) and enter a formula that references the first row of that range. For instance, =$C2="Overdue" will highlight all cells in rows where column C says “Overdue.” The dollar sign locks the column, and the row number changes relative to each row in the selection.
This works because the formula is applied to every cell in the selected range, and the row reference adjusts accordingly. For cell A2, the formula checks C2; for cell B2, it still checks C2 (because the column is locked); for cell A3, it checks C3, and so on. The result is that the entire row gets the formatting when the condition is true.
Performance and Limitations
Conditional formatting with formulas can slow down large sheets if you use many rules or apply them to entire columns. Each rule is evaluated for every cell in the range, so a rule applied to A:A on a sheet with 100,000 rows means 100,000 calculations. To keep things fast, limit the range to the actual data area (e.g., A2:A1000) rather than the entire column. Also, avoid using volatile functions like TODAY() or NOW() in thousands of rules, as they recalculate every time the sheet changes.
Another limitation is that you cannot reference another sheet directly in a conditional formatting formula in Excel unless you use a named range. In Google Sheets, you can reference another sheet with Sheet2!$A$1, but in Excel you would need to define a name for the range on the other sheet and use that name in the formula. This is a common point of confusion, so it is worth knowing.
Frequently Asked Questions
Can I use conditional formatting based on another column in Google Sheets? Yes. Google Sheets supports custom formulas in conditional formatting. Select the range, go to Format → Conditional formatting, choose “Custom formula is,” and enter a formula like =$B2>100. The same relative/absolute reference rules apply.
Why is my conditional formatting not working when I reference another column? The most likely cause is a wrong relative reference. Check that the formula references the correct row for the top-left cell of your selected range. Also, ensure the formula returns TRUE or FALSE—if you use a text string without quotes, it will be interpreted as a named range and fail.
Can I highlight an entire row based on a cell in another column? Yes. Select the entire data range (e.g., A2:F100) and use a formula with a locked column reference, like =$C2="Overdue". The formatting will apply to all cells in the row when the condition is met.
How do I use conditional formatting with dates from another column? Use a formula like =$D2=AND($D2>=DATE(2025,1,1),$D2<=DATE(2025,12,31)).
Can I reference a cell on another sheet in the formula? In Google Sheets, yes, you can write =Sheet2!$A$1. In Excel, direct references to other sheets are not allowed in conditional formatting, but you can define a named range on the other sheet and use that name in the formula.
Does conditional formatting based on another column work with text that contains partial matches? Yes, use ISNUMBER(SEARCH("text",$B2)) for a case-insensitive partial match in both Excel and Google Sheets. For case-sensitive, use FIND() in Excel or SEARCH() in Sheets (which is case-insensitive) and combine with EXACT() if needed.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Campus Tech Innovation Hubs: What They Are
- Poco C51 Airtel Prepaid Locked Variant India?
- Jenkins vs GitHub Actions: Which CI/CD Fits Your Team?
- Turn Off Backup Encryption in iTunes?
- Shared VPS Hosting: Is It Better?
- Disk Defrag Pro: What It Does and Whether You Need It