Thread: Conditional formatting according to frequency

Hi,

I have a table in Excel 2007 made up of 7 columns (A through G) and 722 lines. Each line comprises 7 different (always different among themselves) values. Each one of these values is a word. Most of these words are recurring in the table and yet they do so at different rates.

So far I'm able to highlight through "Conditional Formatting" any words that occur more than once (Find Duplicates). However, this method only allows me to equally highlight all words occurring more than once regardless of their frequency. In my case, since I chose to highlight the cells with a red background, the result is a bunch of red cells regardless of frequency.

In spite of researching the issue for a while, I have been unable to figure out a way in which I can highlight in a different shade of red (for example) words that occurr at different frequencies. This means that I'd like to find out how to format the background color in cells containg words occurring twice, three times, four times, and so forth instead of just formatting ANY word occurring more than twice regardless of its frequency.

Finally, and this would be an amazing help, I'd like to know if there's a way I could tag every word that has occurred on a previous line in the table by adding a number indicating how far back (how many lines prior) the word repeated itself for the last time. Say that the word "ruthless" occurs in lines 6, 9, and 15: This would mean that I'd like for the word in line 15 to be appended with a number "7", the word in line 9 with a number "3" and the word in line "6" with an "N" for "new".

Thanks in advance for any help you'll provide.

Use a formula in the CF, such as

=COUNTIF($F:$F,$F5)>n

where n is whatever value you wish to test for

Thanks for your help.

I tried CF with a new rule but it didn't work. The formula I used was =COUNTIF($FA1:$G722,$F5)>2 but nothing was highlighted. Any suggestions?