highlighting first non zero in row

[jencha]

Hello All, I'm hoping to highlight the first non-zero in the row of a large spreadsheet. I used =MATCH(TRUE,Q2:AO2<>0,0) to find the row number, but cannot understand how to use the row number to do conditional formatting. Any help or questions you have would be appreciated.

Jencha

 

[Eric W]

Welcome to the board.

Select your range, Q2:AO2. Click Conditional Formatting > New Rule > Use a Formula > and enter:

=COUNTIF($Q2:Q2,"<>0")=1

choose a fill color and click OK. If you want to apply this to multiple rows, just select Q2:AO100 (where 100 is your bottom row) and use the same formula.

Notice that an empty cell is not the same thing as a zero, so that can possibly cause unexpected results. If that matters, we can adjust the formula.

 

[jencha]

Thanks so much Eric! I'm a brand new analyst who's been in a sales role for 10+ years, so I have lots to learn. I really do appreciate the help with this!

This formula seems to mostly work, but does highlight any zeros that come after the first non zero number.

 

[Eric W]

Nuts! I was hoisted upon my own petard!

I specifically said that empty cells could cause a problem, and then I didn't catch that issue because I had an empty cell on my test sheet. Try this:

=AND(Q2<>0,COUNTIF($Q2:Q2,"<>0")=1)

Good luck with your new position!

 

[jencha]

Thank you so much Eric, the second formula worked perfectly! If you have a moment, could you please describe the difference between the two formulas? I'm not sure I understand why one counted the trailing zeros and the other did not.

Jenny

 

[Eric W]

Hi Jenny,

The first formula, with just the COUNTIF, counts all non-zero cells starting from Q2 up to the current cell. Remember that Excel adjust the formula based on the relative position, so the range goes from $Q2:Q2 in Q2, to $Q2:R2 in R2, $Q2:S2 in S2, etc. So when the count of non-zero cells is 1, then we've found the first non-zero cell.

The problem with that is, if the count of non-zero cells is one, and the next cell is 0, then the sum of non-zero cells for that cell will also be 1, and thus be highlighted. So to avoid that, I added another condition, Q2<>0. Since your range starts at Q2, that's the equivalent of saying "The current cell must not be zero." So the logic of the first condition is the same, but that condition just prevents extra 0 cells from being highlighted.

Glad it works for you!

 

[jencha]

I get it! Thanks so much for the explaination! This has been very helpful.

 

[Tvalli]

Nuts! I was hoisted upon my own petard!

I specifically said that empty cells could cause a problem, and then I didn't catch that issue because I had an empty cell on my test sheet. Try this:

=AND(Q2<>0,COUNTIF($Q2:Q2,"<>0")=1)

Good luck with your new position!

How can this be applies to also include the second non-zero value? So I use this for the first non-zero in the row, how can I change to the second non-zero? We use this to track customer purchases on their first two purchases.

 

[Eric W]

Welcome to the Forum!

Assuming no empty cells:

=AND(Q2<>0,COUNTIF($Q2:Q2,"<>0")>=1,COUNTIF($Q2:Q2,"<>0")<=2)

 

[Tvalli]

Welcome to the Forum!

Assuming no empty cells:

=AND(Q2<>0,COUNTIF($Q2:Q2,"<>0")>=1,COUNTIF($Q2:Q2,"<>0")<=2)

Thank you!