Excel Sort within a Sort

Colour by numbers

Did you know you can sort by colour in Excel? Did you know you can sort ascending or descending within that colour? I was asked a question in a recent webinar and in answering I found out that you can sort within a sort.

Typically you have levels in a sort that sort different columns. But you can sort in levels in the same column if you use colours and values for the sort.

Examine the table below.

We can add a format to three cells.

You can right any one of the three cells and choose Sort and then Put Selected Cell Color on Top.

That moves the three formatted cells to the top of the list.

Colour sorting

Just a note on sorting by colour. There is no built-in sorting sequence with colours, yellow doesn’t go after red. You can only put the selected colour at the top of the list. If you need a colour sequence you sort the colours in reverse order. Starting with the colour you want to see below all the other colours and finish with the colour want to see at the top. This will work with conditionally formatted cells.

We can then use the Sort icon on the Data ribbon to apply another sort the Income column.

Click Add Level and select Income from the first drop-down list.

This results in the values within the formatted cells being sorted separately to those below.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.