Conditional Formatting Based on Another Sheet's Value
Excel's Conditional Formatting dialog won't let you reference another sheet directly — here's the named-range trick that gets around it.
Try to set up a conditional formatting rule like =Sheet2!B2>100 while you're on Sheet1, and Excel will often refuse or silently fail to apply it correctly across rows. The dialog is built around same-sheet references, so cross-sheet conditions need a small workaround: a named range.
The problem in practice
Say you want to highlight a row on your Sales sheet red whenever that row's value is below the matching target stored on your Targets sheet.
The fix: name the other sheet's range first
- Go to the Targets sheet, select the range you need to compare against (e.g. B2:B50)
- Formulas tab → Define Name → call it something like TargetValues
- Go back to the sheet you want to format (Sales), select the range to format
- Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format"
=B2<INDEX(TargetValues, ROW()-1)Adjust ROW()-1 to match how your data lines up between the two sheets.
The key idea: a named range is treated as a single reusable object, so Conditional Formatting can look through it the same way a formula like VLOOKUP or INDEX can — even though it physically lives on a different sheet.
Try it yourself
Drag the target to see the formatting react live
=IF(C2>=12, "Above target", "Below target")
| Rep | Region | Deals closed | Status |
|---|---|---|---|
| Priya Nair | North | 18 | Above target |
| Marcus Lee | South | 9 | Below target |
| Sofia Reyes | East | 14 | Above target |
| Tom Becker | West | 6 | Below target |
| Aisha Khan | North | 22 | Above target |
| Dan Ortiz | South | 11 | Below target |
A more robust version with a lookup key
If the two sheets aren't in the same row order, don't rely on ROW() — match on a shared key (like a product code or employee ID) instead:
=B2<XLOOKUP($A2, TargetKeys, TargetValues)Named ranges TargetKeys and TargetValues both live on the Targets sheet.
Want the full worked example?
Automate Your Reports: Power Query for Beginners
A complete, practical ebook covering the Folder connector, query folding, modular queries, and a full monthly-report walkthrough.
Get the eBook — ₹199