Search code examples
excelconditional-formatting

How do I copy conditional rendering in Excel to multiple rows quickly?


I have an Excel Spreadsheet with the following data:

Spreadsheet

The 1st column is the value I want to compare against. On a row by row basis I want to check to see if the other values in that row match the 1st columns value, if it doesn't then I want to highlight the cell red.

I have figured out how to do this by going through each row 1-by-1 and adding the conditional rendering rule but the file is about 200 rows long so will take me some time.

I was just wondering whether this was possible to do quicker?

An example below for further understanding:

If A2:A10 does not equal A1 - fill the cell red
If B2:B10 does not equal B1 - fill the cell red
If C2:C10 does not equal C1 - fill the cell red

EDIT: Trying the solutions in the comments renders the following: Conditional Formatting

Expected Output:

This is what I want it to do using just 1 row as an example. I want the value in Column A to be the value that the rest of the row is compared against. And when the rows numbers do not match those in Column A they should be filled/highlighted to show that they don't match. Much like L1 on my example - It doesn't match that of A1 so it is highlighted. I want to do this but for every row and compare the values as stated above. Expected Output


Solution

  • Well, this is what you need to do use the formula in conditional formatting as shown below

    =AND($A1<>A1,A1<>"")
    

    CONDITIONAL FORMATTING COLUMN WISE