The situation - I have rows of data where similar names are grouped and merged to show all those rows are for the same individual like so:

Now I'd like to have conditional formatting where say if the last row is a certain value (these are merged too) say "Highlight2" in this case then fill columns 4-6 with blue, and if it has the value of "Highlight" then fill columns 1-3 with blue. Like so:

But when I try the formula I get this output because merged cells only seem to have values in the top left cell:

The conditional formatting looks like this for say column 5:

Is there a way to get it to register the values in merges cells without separating them and copying the value as that looks a lot more messy.

1 Answer 1


As you already know, merged cells only have values in the top left cell.
That is why you will need a helper column eg. in column P having all cells un-merged.

You can then use a formula like: =$P3="Highlight"

Conditional formatting on merged cells

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.