Conditional formatting according to number of mentions

%3CLINGO-SUB%20id%3D%22lingo-sub-1340666%22%20slang%3D%22de-DE%22%3EConditional%20formatting%20according%20to%20number%20of%20mentions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1340666%22%20slang%3D%22de-DE%22%3E%3CP%3EDear%20Excel%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20several%20names%20in%20it%20and%20every%20name%20can%20occur%20multiple%20times.%20I%20want%20each%20name%20to%20be%20marked%20red%20in%20the%20background%2C%20if%20it's%20mentioned%2010%20times%20or%20more.%20But%20not%20every%20mention%20of%20the%20name%20should%20be%20red.%20Just%20the%2011th%2C%2012th%20etc.%20Mention.%20Is%20that%20possible%3F%20I%20would%20appreciate%20your%20help%20very%20much.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3ELea%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3ELea%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3ELea%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3ELea%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EChild%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3ELea%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1340666%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Dear Excel community,

 

I have a table with several names in it and every name can occur multiple times. I want each name to be marked red in the background, if it's mentioned 10 times or more. But not every mention of the name should be red. Just the 11th, 12th etc. mention. Is that possible? I would appreciate your help very much.

 

For example:

Lea

Lea

Lea

Lea

Lea

Lea

Lea

Lea

Lea

Lea

Lea

Lea

Lea

 

Kind regards

Lea

1 Reply
Highlighted

@leamk 

You may apply conditional formatting rule with formula like here

image.png

just be careful with starting cell as well as with using of absolute and relative references.