MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Sort and Filter by Color or Icon


April 11, 2019 - by Bill Jelen

Excel Sort and Filter by Color or Icon. Photo Credit: Mat Reding at Unsplash.com

Conditional formatting got a lot of new features in Excel 2007, including icon sets and more than three levels of rules. This allows for some pretty interesting formatting over a large range. But once you format the cells, you might want to quickly see all the ones that are formatted a particular way. In Excel 2007, sorting and filtering were also updated to help you do just that!


This book analysis table has some highlighted rows to flag interesting books and an icon next to the price if the book is in the top 25% of prices in the list.

In a Book Analysis data set, two rows have been highlighted in yellow. One row has an icon applied.

If you want to quickly view all the highlighted rows or cells that have icons, just drop down the filter for the column and choose Filter by Color (or Sort by Color to bubble them to the top).

Then you can pick the formatting you want to sort or filter by! This doesn’t just work for conditional formatting; it also works for manually coloring cells. It is also available on the right-click menu of a cell under the Filter or Sort flyout, and in the Sort dialog.

Right-click one of the highlighted cells with an icon. Choose Filter by Color. You can choose Filter by Cell Color, Filter by Font Color, or Filter by Icon.

This tip is from Sam Radakovitz, a project manager on the Excel team. He is more fond of cats than dogs.

Title Photo: Mat Reding at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.