Forum Discussion

fft6_aloysius's avatar
fft6_aloysius
Copper Contributor
Nov 28, 2019
Solved

Excel Formula

Hi, I would like to ask if there’s a formula to highlight every nth time a specific value pops up. for example; apple, orange, apple, banana, strawberry, apple when every third ‘apple’ shows up it w...
Formulas and Functions
fft6_aloysius's avatar
fft6_aloysius
Copper Contributor
Nov 29, 2019
yes thank you so much this is what i meant, but is it okay if you could post a tutorial or something because im kinda beginner with this stuff and my situation is affection columns if that will change anything. thank you once again!
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 29, 2019

    fft6_aloysius 

    To apply conditional formatting select you range, Home->Conditional Formatting->New rule and select this type of rule

    Here add formula and desired format. Conditional Formatting iterates cells in your range one by one and triggers the format if the formula for the cell returns TRUE. That's important to start formula from the first (top left) cell of the range. For the each of next cells formula will be applied as you drag it on this cell, depend on how to use absolute and relative references. You may imitate such behavior applying formula to cells in other range:

    COUNTIF() calculates how many times the value of the current cell appeared from the beginning of row. With next multiplier we ignore blank cells.

     

    If apply Conditional Formatting to another range

    the formula for conditional formatting rule will be

    =(COUNTIF(M$2:M2,M2)>=3)*(M2<>"")

    Here we take cell M2 as starting one and shift relative reference from rows on columns.

Resources