How to apply custom formulas to conditional formatting. (Real world example from my work)…

Good tools to make life easier. You’re welcome :)

I ran across an issue a few weeks ago at work while building a custom Google Sheet for a new vendor project.

Part of the sheet was a table of item details that would grow over time.

The nature of what I was doing required that sometimes duplicate item numbers might get appended to the table.

I wanted a quick visual flag if this ever happened, and I landed on conditional formatting.

I needed the entire row to get highlighted red if there were ever duplicate item numbers in column A.

In case you didn’t know, there are a host of different rules you can set up for conditional formatting.

Many of the built in rules suffice for simple use cases.

But you can also select “Custom formula is” down at the bottom of the list if you need to homebrew a solution.

That’s what we’re doing here.

And the solution is pretty simple: COUNTIF()

We simply want to count the number of occurrences of the item numbers in column A and make sure it’s always only 1.

And just like that, we’re done.

By using an absolute at the start of the range $A$2, we lock the start of the equation and then look all the way through whatever row is being evaluated (this goes through all the rows one at a time because we’ve instructed the conditional formatting to go from A2:A500).

It will highlight the duplicate row if there is one because the COUNTIF() formula will return 2 instead of 1.

If we wanted to highlight all occurrences of the duplicated row, we could change the syntax just a hair and write this instead:

Now it’s looking at the entire column every time it evaluates the conditional formatting.

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.