Sort By Colour in Excel

In the old days, the Sort dialog box in Excel only had 3 levels. However, with a bit of planning, you could sort Excel data by 4 columns or more, and once you learned that trick, life was good. Or at least it was sort of good. ;-)

Sort03

In Excel 2007, the Sort dialog box is much fancier, and you can include up to 64 sorting levels. I’ve never needed anywhere near that many – 5 or 6 fields is plenty for most tables that I’ve had to sort.

Sort By Colour

Another new feature in Excel 2007 is the ability to sort by cell or font colour, or by cell icon.

SortColour01

If you have different colours in a column, you can choose one to show up at the top or bottom of a sorted list.

SortColour02

If you used conditional formatting to add cell icons, such as traffic lights, you can sort by those icons.

SortColour06

To put the colours or cell icons in a specific order, you can add the same field multiple times in the Sort dialog box, and choose a different colour or cell icon for each sorting level. This won’t be too difficult if you have only a few colours in the list, but will be more challenging if you have lots of colours.

SortColour03

The list on my worksheet, that was previously sorted by date, is now sorted by the colours, in the order that I selected above. Yellow isn’t in the Sort level specifications, so it appears at the bottom of the list.

SortColour04

Sort By Colour At Your Own Risk

Even though you can sort by colour now, I wouldn’t recommend it. I’ve seen too many rainbow coloured Excel worksheets, where nobody can remember what the colours mean. Is yellow good? Is blue bad? Is there a colour code somewhere?

I’d rather add another column in the worksheet, and put a number code or text comment there. So, instead of marking the overdue accounts with a red fill colour, type “Overdue” in another column, or use a formula to calculate which accounts are overdue.

Or, instead of highlighting the customer names that you want to send an email to, type an X in an Email column. Then, you can sort or filter the Overdue accounts or the Email column, to focus on the rows of interest.

SortColour05

Do You Sort By Colour?

Maybe I’m missing something, and the feature is working well for you. Some people must have asked for the feature, since they added it to Excel 2007.

In your Excel worksheets, do you ever sort by colour? In what kind of lists or situations is it most helpful for you?

_______________

You may also like...

11 Responses

  1. Gordon says:

    There are far too many similar colours, even in 2003 and earlier, to make identifying cells by colour in any way reliable. 2007 just makes it impossible.

    This does not of course stop people colour-coding entire 2003 sheets then asking me “How can I add up all the pink ones?”. I send them packing with instructions to add extra columns as you suggest!

  2. Dave Roberts says:

    I add colors and sort by them. This isn’t by choice but by direction. My boss likes colors so I do too. ;)

  3. Thanks Gordon, and I hope those people follow your advice! Much easier than trying to add the pink cells.

    Dave, my sympathies. ;)

    Ron, thanks for the link to your article on the Microsoft site.

  4. Jason Morin says:

    Anything has to be better than no native ability to sort by color (still using XL2000 on my office laptop)! While I stay away from rainbows in my spreadsheets, every 6 months or so I get a request to help someone sort their data by fill color. I either write a quick VBA function or use GET.CELL and a defined name. Pretty laborious.

  5. Thanks Jason, and I hope your co-workers who want to sort by colour can get Excel 2007 eventually.

  6. Ute-S says:

    I use a bright yellow fill color to mark cells where data are still missing. (Either formatted manually or by conditional formatting for empty cells.) Then I sort those rows to the top of the list and know exactly, which missing data I have to ask my team members for.

  7. Thanks Ute-S, it’s interesting to see what other people do with the Excel features.

  8. Contextures Blog » Sort It Your Way With Excel Custom Lists says:

    […] know how to sort an Excel list alphabetically, and with Excel 2007 you can even sort an Excel list by colour. Did you know that you can also create a custom list in Excel and use that to sort your data, […]

  9. […] There you have it – Sorting by color in Excel 2003. As I mentioned before, Excel 2007 has a better (built-in) solution. So learn how to sort by color in Excel 2007, check out Debra’s Post. […]

  1. January 28, 2013

    […] What version of Excel do you have? It's built into Excel 2007 and above: Sort By Colour in Excel | Contextures Blog […]

Leave a Reply to Contextures Blog » Sort It Your Way With Excel Custom Lists Cancel reply

Your email address will not be published. Required fields are marked *