Highlight non-duplicate numbers from 2 columns

mccaffhm

Hi, I have 2 columns- 1 with an Invoice amount and another with a formula adding all of the line items to return the Invoice amount. I want to conditionally format one of these columns to highlight the cell if the 2 columns do not match. For example if the Invoice amount is $200 and the formula column is returning $205 it would highlight that cell red.

Every time I try doing this it just highlights everything in the column, even though the numbers are identical.

Kelly Moore

Hey @mccaffhm

You will need a helper column to flag the non matches. You can set your conditional formatting against the helper column. As with most helper columns, you can slide it all the way to the right and/or hide it.

A checkbox helper column will do. Change the name of Column1 and Column2 in the formula below to match your column names in the sheet.

=IF(AND([Column1]@row<>"", [Column2]@row<>"", [Column1]@row<>[Column2]@row), 1)

Will this work for you?
Kelly

mccaffhm

Hi Kelly, I am not sure what column 1 and column 2 are referencing in your formula. I have the Invoice total column, the formulated invoice total column, and the helper column. Just want to make sure I am inputting everything correctly!

Kelly Moore

hey @mccaffhm

Since I wasn't sure of the exact name of your columns, I referred to them as Column1 and Column2.

This formula goes into the new checkbox helper column you created. If the column names in the formula below aren't correct, change them in your formula to match your sheet.

=IF(AND([Invoice Total]@row<>"", [Formulated Invoice Total]@row<>"", [Invoice Total]@row<>[Formulated Invoice Total]@row), 1)

If this needs tweaking or if you have questions, be sure to @mention me to make sure I get the email.

Will this work for you?

Kelly