Excel Sorting By Colour

  steviegee 14:21 20 Apr 2007

I have a spreadsheet with rows of data in. I have coloured the rows in various colours. Can I and how do I sort the rows by colour please? For example all the red cells/rows then all the green etc...

  VoG II 14:26 20 Apr 2007

It isn't straightforward but doable click here

  steviegee 14:35 20 Apr 2007

Thanks VoG thats a bit of a problem as I am at work and I can only access certain sites!!

  VoG II 14:58 20 Apr 2007

If you have color-code cells in your worksheet, you find that at times it is useful to sort rows by the colors of the cells. That is, sort all the reds at the top, followed by the blues, followed by the yellows, and so on.

Unfortunately, Excel provides no such tool. You have to do it manually. This page describes how to do it.

The first thing you need to do is create an additional column that will contain the ColorIndex (click here for more information about the ColorIndex) of either the font or the background of the cell. To the right of the data you want to sort, insert a new column by selecting the cell the right of the data, and choosing Columns from the Insert menu.

Next, you need a VBA function to return the ColorIndex value of the cell. Put the following code in a standard code module in your workbook.

Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer

Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
C = Rng.Interior.ColorIndex
End If

If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
C = GetWhite(Rng.Worksheet.Parent)
End If
End If

ColorIndexOfCell = C

End Function

Function GetWhite(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = &HFFFFFF Then
GetWhite = Ndx
Exit Function
End If
Next Ndx
GetWhite = 0
End Function

Function GetBlack(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = 0& Then
GetBlack = Ndx
Exit Function
End If
Next Ndx
GetBlack = 0
End Function

Then, in the newly created column, enter either of the following formulas:

If you want to sort by the Background color of the cell, use the formula


If you want to sort by the Font color of the cell, use the formula


  VoG II 14:58 20 Apr 2007

Of course, change the reference A1 to the first cell in the range. Use Edit, Fill, Down to fill this formula down to the entire range of data you want to sort.

In these cells, you'll see numbers between 1 and 56. Each of the values indicates the ColorIndex of the cell.

Now, you can sort your data in the normal way, but choose the new column as the primary or first sort key. The cells will be sorted in ascending (or descending) order of the ColorIndex values.

So far, this is all well and good if you are happy with the default order of ColorIndex values. For example, by default, Red = 2, Blue= 5, and Yellow = 6. Therefore, when sorting by ColorIndex values, the data will list all the reds first, followed by the blues, then the yellows.

If you want to modify this order, you will need to create a "custom list" and tell Excel to use this list as the sort order. First, create a custom list by going to the Tools menu, Options item, Custom Lists tab, and selecting NEW LIST in the Custom Lists box. Then, enter the ColorIndex values in the order you want them to appear in ascending sorts, in the List Entries box. You can enter the numeric values (between 1 and 56) on separate lines in the List Entries box (create a new line by pressing ALT+ENTER) or by separating the entries with a comma all on the same line. (NOTE: Non-USA English users may have to use a semicolon rather than a comma.) For example, to sort in order Blue, Yellow, Red, the custom list would be (without the quotes) "5,6,2".

Then, in the Sort dialog box, click the Sort By drop down box, click the Options button, and choose this new list from the lists displayed.

Yes, sorting by color is a bit tricky, and something that we all would like to see built in to Excel. However, until Microsoft provides this feature as a built in tool, we must make the best of what is available.

NOTE: This method sorts by the color specified by the cell's properties. It does NOT work with colors that are displayed as a result of Conditional Formatting.

  steviegee 15:30 20 Apr 2007

Thanks VoG that will keep me busy!!

  fitshase 16:54 20 Apr 2007

Excel 2007 allows you to filter by colour and sort by colour as well.

It is probably one of the best additions in the new version.

  Simsy 18:21 20 Apr 2007

there is an easier way... we should clarify whether the colouring of the cells you have done has been done "manually". I assume that is the case? If so then what VoG™ has suggested is the route...

However, if the colouring occurs because of "conditional formatting" it is probably possible to sort using the same conditions.

You have coloured the cells manually haven't you?



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Aardman's new YouTube channel to share the work of independent animators

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017