How to sort cells containing specific text in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.86 out of 5)
March 15, 2011

The merits of access are endless. Excel is undoubtedly a very powerful tool for organizing data and filtering them. There are many tools that help you sort and filter data in Excel. You must have run across times when you need to sort cells containing specific words and then pile them up together. For example, you may sometimes need to sort a list of people by their last names. If you wonder how to do it, here’s an easy trick.
Excel Conditional Formatting
Sorting cells in by specific words contained in them can be done easily through conditional formatting and the filter option in Microsoft Excel 2007. First, select the column that you want to sort the data for. Now click on Conditional Formatting under the “Home” tab. You will see a handful of options for formatting cells. Under conditional formatting, go to Highlight Cell Rules>Text That Contains. Type the word or phrase that you want to sort the column by. As you type, you’ll see the cells containing the word get highlighted. Choose a color(Red by default). Finally click on OK. Quickly scroll upto the bottom of your  sheet and you’ll see that the cells that contain the word have been highlighted.

Excel Sort by color
Now select the first cell of the column and click on the Data tab. Next, click on the Filter button. You will now see a dropdown on the first cell of the column. Click on the dropdown and select Sort by Color>Sort by Cell Color and choose the select the color that you used for highlighting. You can also select the sort by font color option. Now, all the cells containing the specific word get piled up on top of the rest. If you want to sort other cells in a similar way, you can proceed as above. Once you have sorted, you can clear the formatting rules from the Conditional formatting dropdown.

You may also like...

  • This is very helpful. 🙂

  • KeriG

    Thank you! You just saved me hours of work!!!

  • Very helpful thanks a lot!

  • JanetC

    Great!  Really easy to follow – with pictures no more searching around to find out where the  box is they are talking about !I will be back to this site!  

  • nasiiim

    Its very helpfull.. thanks..

  • Darren

    Perfect – just what I was looking for. Thought I was gonna have to install MySQL and go the linux route for a little while before stumbling on this post. Saved me a lot of hassle – thanks a bunch.

  • Thanks a lot , very useful tip 🙂

  • Sashen


  • Mike

    Your screenshot of the filter menu includes “Text Filters” – why not just use that?

  • guest63

    Worked a treat 🙂 From 34,000 docs to trawl through to just over 100. Thanks.

  • precogtyrant

    glad it helped.

  • Salar Lotfee

    perfect, just have wasted a day in the factory by the scanning machine in 38′ centigrade standing with no results, but this blew it up. super thanks

  • Tayyab

    Thanks . . saved my whole day.

  • Jeremiah Houle

    Is it possible to highlight a specific word within the cell – not the cell itself, just the word??