Categorising Data – By Colour – In Excel (Using an IF Statement)

By Geoff Griffiths @mmatraining1980

Had to categorise some keywords, if there’s a quicker way of doing it – please let me know

  • Paste keywords into column A
  • In column D – Paste all relevant words for a category.
    For example – I pasted all the words I could think of relating to gardening – such as “tree”, “fruit”, “garden”, “bush”, etc. – There were 18 words in total
  • In cell E2 paste the formula:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$19,A2)))>0, "Contains value from range", "Does not contain value from range")
  • Now drag the formula down
  • Filter column E to “contains value for range”
  • Colour in the keywords green – or a chosen, relevant colour

Add another list of relevant words – for example, above I have added sports, to highlight sports netting keywords.

Drag formula down and repeat

netting-usa-categoriesDownload