MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort or Filter by Icon

October 16, 2017 - by Bill Jelen

Sort or Filter by Icon

You have Excel data with icons, such as traffic lights. You want to find all of the red icons. You can sort or filter by color.

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:

Book Analysis Table
Book Analysis Table

If you want to quickly view all the highlighted rows or cells that have icons, just dropdown 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.

Pick the Formatting to Sort or Filter by
Pick the Formatting to Sort or Filter by

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

Watch Video

  • Today's trick is from Sam Radakovitz on the Excel team
  • Sam's signature formatting is white Segoe UI on Excel Green RGB(26,116,61)
  • Creating your own icon set to show items in top 25%
  • Creating a formula-based conditional formatting to highlight entire row
  • Use Filter dropdown to filter by color or by icon
  • Right-click to filter by color or icon
  • Right-click to sort by filter or icon
  • Idea to allow filtering by two colors
  • Vote at

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2049 sort or filter by icon I am
  • podcasting all of the tips in this book
  • click that I in the top right hand
  • corner to subscribe to the playlist
  • alright today's tip is from Sam rats and
  • Radek of its is on the Excel team and
  • one thing I love from Sam is the way his
  • sheets look cool all the time I always
  • go to school and try and learn how Sam
  • is doing this in one of Sam's signature
  • moves here is say go you I as the font
  • that white on this green background what
  • is this green we have to come here to
  • more colors and custom and that's the
  • magic Excel green 26-1 1661 so if you
  • want to start creating worksheets that
  • look like Sam's you can start using
  • those formats they always look really
  • really cool all right so Sam was talking
  • about sort and filter by icon and he
  • actually created a really cool icon set
  • here where he looked at things in the
  • top 25% of price so we'll select all of
  • those cells and they go to home
  • conditional formatting icon sets and
  • chose a four-level icon set really
  • important that uses four levels all
  • right so that gets that default and then
  • we wanted a conditional formatting and
  • manage the rules and then edit that rule
  • and Sam's goal is to highlight things at
  • the top 25% so by using four icons it's
  • automatically set up at the right levels
  • great tip there and then no cell icon no
  • cell icon no cell icon and then for this
  • one he chose this nice yellow
  • exclamation point all right so you don't
  • have to use the sets that are building
  • you can build your own sets or in this
  • case I use a set to highlight just
  • things that are in the top 25% that's
  • cool the other thing that Sam did is a
  • great trick that I've done before to
  • highlight the entire row so Sam's going
  • to use a formula here conditional
  • formatting create a new rule use a
  • formula to determine which cells to
  • format
  • and you know we are currently sitting in
  • cell c4 so this formula has to work from
  • the point of view of c-4 but it has to
  • work be able to be copied throughout so
  • we're going to look for everything where
  • the the price in F so dollar sign f4
  • that's really important we always want
  • to look in F but the four is allowed to
  • move is it gets copied down / the page
  • count in e is greater than let's say ten
  • cents a page and we'll just format that
  • well I will go back to the fill color
  • here go to more colors choose this nice
  • light yellow and then change the font
  • for these two may be black click OK
  • click ok click ok alright seeing that is
  • cool because that highlights the entire
  • row for something based on a calculation
  • over here and E and F all right now so
  • now that we've applied these if we want
  • to look at the things highlighted with
  • the icon or highlighted with the color
  • we can turn on the filters so data
  • filter and you can open this drop down
  • and under filter by color we can either
  • filter to all of the things that have
  • the yellow color I'll undo that or or we
  • can right click and go to filter and say
  • filter by selected cells icon and we'll
  • be able to quickly filter to everything
  • that is both yellow or has the ! so
  • either right-click or using the
  • drop-down now let's turn the filters off
  • it's also possible to come in here and
  • sort all right so if we want to sort all
  • that icon to the top we just choose a
  • cell right click and say sorts put
  • selected cell icon on top and we can
  • pull all of those items to the top of
  • the list all right these are great
  • techniques all right now I'm going to
  • deviate from sams trick here and
  • actually unfortunately makes it more
  • work for Sam you know when we use
  • filters one of the nice things that got
  • added to excel 2010 is the ability to
  • search all right so we want to search
  • for all books that have
  • word dawg it finds all those and that
  • works great that's awesome right but
  • that we could also come back in here and
  • search for all books that have the word
  • cat and then this beautiful thing called
  • ad current selection to filter click ok
  • and we now have a list of all of the
  • items that are either dog or cat alright
  • so that's a cool bit of functionality
  • that i really like but i was doing an
  • excel seminar yesterday in nashville and
  • someone pointed out that if you want to
  • filter by color if you needed a filter
  • to everything that's green or yellow
  • there is no equivalent ability to select
  • multiple items you can't click here and
  • drag or anything like that alright night
  • so now you're right I've never tried to
  • do this but if this is something you
  • have to do I can see where it would be a
  • frustrating thing so I went out to excel
  • uservoice calm and sure enough there's
  • already someone there go to mr. X dot CL
  • mr. XTL that's a chilly country of Chile
  • domain there / filter two colors and you
  • end up out here where Arnie has
  • suggested like to be able to filter
  • columns by more than one color right I
  • already voted I think you should go out
  • and vote we want to get at least 20
  • votes ears that way the Excel team can
  • take a look at this idea all right well
  • hey it's Friday that means for half the
  • world it's payday just ten dollars for
  • this book the e-book or twenty five
  • dollars in print click that I on the top
  • right hand corner now we're almost done
  • with the book I sure would appreciate if
  • you'd buy this book to get a quick recap
  • of the last 70 podcasts all right
  • today's trick from Sam rad on the excel
  • team talked about Sam's signature
  • formatting white say go UI font on the
  • Excel green RGB 26-1 1661 created our
  • own icon set to show items at the top
  • 25% robber started out with four icons
  • and then changed a little over three to
  • know settle icon and it created a
  • formula based condition to highlight the
  • entire row we use the filter drop-down
  • to filter by color or by icon you can
  • also right click to filter by color icon
  • or right click to sort by
  • filter icon and then asked you to vote
  • for that idea to allow filtering by two
  • colors go ahead and vote thereat mr. XEL
  • / filter two colors I want to thank you
  • for stopping by we'll see you next time
  • for another deck cast from MrExcel

Download File

Download the sample file here: Podcast2049.xlsm

Title Photo: traveLink / Pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

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