Conditional Formatting - Comparing two columns

Lacey Augustine

Please share with me your best advice.

I am sharing an inventory report. If the low level is greater than the actual # of units we have, I want the column to be highlighted. While reviewing conditional formatting I do not see how I can compare the two columns to create the formatting.

Each inventory item has a different low level established so I can not do an across the board "if less than" for the # of actual units.

Brian W

You will need to create a “helper” column that compares the two numbers and gives a result like a flag. You can then create a conditional format that says “If Helper column is a flag then apply this format to Inventory column.”

If you don’t want to see the helper column, you can always hide it.

Lacey Augustine

That worked perfectly. Thank you!

Jose Munoz

Can you please share the formula that you will create to compare both values and turn the flag on or Off

Paul Newcome

@Josem29 What are your column names that you want to compare, and how are you comparing them?

ArthurC

i understand using a helper column and using If formulas to give results, then conditional format based on the result.

ie. =If[John sales]>[Target],1,0. Then conditional format, if format column = 1, highlight row/cell red.

BUT does that mean that I need a helper column for every single column? if i have 50 staff (and 50 columns) and I want to highlight each cell, then i have to have a helper for each?

Paul Newcome

@ArthurC Not necessarily. You could join the names of each person that meets your requirement into one cell and base your Conditional formatting off of whether that one cell contains a specific text string. You would however still need to create 50 individual Conditional Formatting rows to be able to format each of the 50 columns individually.