A common task when analysing data is to highlight values to show the data.

To highlight a row based on the value of one cell you need to use the INDIRECT function in Excel.

This allows you to use conditional formatting to highlight a whole ROW based on the value of one of the cells in the row.  (Useful if you have used a count function to highlight duplicates)


Lets say you have numbers in column B, and you want to highlight all the rows where the value in the column equals “2”.

You can use the formula below in the conditional formatting rule to create the entry:

=INDIRECT("B" & ROW())=2


You can amend how you want to format the values as normal.

This is my site Written by Tim Jolley on February 9, 2011 – 2:19 pm