Search code examples
excelexcel-2007conditional-formatting

Highlight cells in a row where the value of a particular cell is different to that of the previous row

I'd like to apply some conditional formatting where each cell of a row is compared to the cell in the previous row. If it differs then the row is highlighted.

Example of desired output

I'm using Excel 2007. Is this even possible? If so could someone provide me with the steps to apply this to an entire table of data?

Solution

  • Yes, it is possible. It was possible in previous versions of Excel, too.

    The condition is very simple.

    Select the data, starting from the second row of data (the third row counting from the header), bring up the condition formatting dialog, select "Formula" and enter =A3<>A2, =A3<>OFFSET(A3,-1,0) where A3 is the top-left cell of the selection.
    Note the absence of dollar signs - Excel will automatically suggest them, so delete accordingly.

    Because the reference is not absolute, the formatting will properly apply to the whole table.