FormulasBeginner6 min read

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

  1. Go to the Targets sheet, select the range you need to compare against (e.g. B2:B50)
  2. Formulas tab → Define Name → call it something like TargetValues
  3. Go back to the sheet you want to format (Sales), select the range to format
  4. 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

Target: deals closed this month12

=IF(C2>=12, "Above target", "Below target")

RepRegionDeals closedStatus
Priya NairNorth18Above target
Marcus LeeSouth9Below target
Sofia ReyesEast14Above target
Tom BeckerWest6Below target
Aisha KhanNorth22Above target
Dan OrtizSouth11Below 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.

Conditional formatting rules using named ranges still recalculate live — if the Targets sheet changes, the highlighting on Sales updates immediately on the next recalculation, no refresh needed.

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