So Can You Really Sort On Colours In Excel 2007?

Yes you can sort on colours in Excel, and much more! This articles summarises how you can sort data in Excel 2007, including sorting on colours and much more. Excel has always been able to sort lists of data by column headings such as surname or salary if these are included in your list. But with Excel 2007 sorting can also be done based on font colour or cell colours or cell icons.

Yes you can sort on colours in Excel, and much more! This articles summarises how you can sort data in Excel 2007, including sorting on colours and much more. Excel has always been able to sort lists of data by column headings such as surname or salary if these are included in your list. But with Excel 2007 sorting can also be done based on font colour or cell colours or cell icons.

For an example to work on try creating a list of staff names in Excel 2007 with headings surname, firstname, post and salary and then add several staff details to the list. Just for fun add different font colours to each surname. To do this click on the cell, select the Home tab and in the Font commands choose a font colour and then repeat for each of the other surnames.

Once you've created these different coloured surnames you can sort the list by surname colour order. To do this, select any cell within your list. You do not need to highlight the list as Excel knows the table range from the selected cell. Then choose the Data tab and click on the Sort command button.

In the Sort panel you'll see that Excel 2007 has ticked "My data has headers" so the headings will remain static when you sort your list. You'll also see in the panel one "Sort By" entry. Under "Column" choose "surname", then under "Sort on" choose "Font color". Then under "Order" you'll see a choice of all the currently used font colours. Choose one as the first to sort on.

You need to repeat this to set the second colour to be sorted on. So still in the Sort panel click on "Copy Level". A second sort row appears with the same selected data. Just change the selected colour under "Order" to set the second sort colour. Copy the level and change the chosen colour as many times as you wish to set the sort order surname colours. Excel 2007 does not have a default colour sort order so you need to do this each time you want to set a sort by colour.

Then click OK to carry out the sort. If you want to edit your sort choices, just select any cell in the list and click to Sort button on the Home tab. The Sort panel will re-appear still showing your earlier choices. You can also delete any of the sort choices by first selecting it, then clicking "Delete level".

In Excel 2007 you can also set cell colours using conditional formatting. For example in your list you created earlier, highlight the salary values (not the heading), select the Home tab and click on the Conditional Formatting command button and choose one of the Colour scales options. Cell background colours will be set depending on the values in each cell. You can then apply a colour sort in exactly the same way you did earlier but this time the colours are set for you.

The Sort panel has another sort criteria we haven't looked at yet, sorting by Cell icons. These are created in the same way conditional cell colours are created by Excel 2007's conditional formatting. Lets remove the conditional colour formatting you created earlier. Just select the same cells, then in the Home tab click on Conditional Formatting, Clear Rules, Clear Rules from Selected Cells. Instead we'll apply cell icons. So select the same cells again, click on Conditional Formatting, but this time choose icon sets and pick one of the sets. Excel adds one icon symbols from the set to each cell, with the icon applied dependant on the cell value. Try varying the values and you'll see the icons changing.

Now if you repeat the list sorting exercise as we did earlier and sort your list by the salary column, but this time sort on cell icon. You can set the icon sort order in the same way you did for colours by setting the first icon to be sorted on and then add as many sort levels as there are icons.

Wondering how many sort levels you can have? Excel 2003 lets you sort up to 3 levels, but Excel 2007 lets you sort up to 64 levels at a time. And coupled with the massive increase in the maximum number of rows in Excel from 16,000 to 1 million, Excel 2007 lends itself to some mighty complex sorting activities. And now with the added sorting by colour or icon features your Excel results can be visually more striking, even if you don't have 1 million items to sort quite yet.

To learn much more on using Excel 2007's colour sorting and other features consider attending one of the many good instructor lead training courses available.