From the Home tab in Excel, under Styles, click Conditional Formatting. Select New Rule, and click on Format Only Unique or Duplicate Values. From the Format All menu, select Duplicate from the drop-down menu, and click Format to select formatting.
This will allow you to format duplicate values automatically.
If you want different formatting for specific cells, click Format Only Cells that Contain, and select formatting for your choice of results.
I hope that helps.
I am having a 100 column spreadsheet with different sentences. I want to highlight the similar words or change the color of the repeating words. is there any formula or vb code which will help me to find the solution.