Sort by cell fill color

Sorting by cell fill color is available if at least one cell in the range has a fill color set.

Sort columns of the selected range by cell fill color

Sorting columns in the selected range by fill color is only available using custom sorting.

To sort the columns of the selected range by fill color, follow these steps:

1.Select a range of cells containing more than one row.

2.Open the Custom sorting window in one of the following ways:

•On the Home tab, in the Data group, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

•On the Data tab, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

The appearance of the  Sort button depends on the last command invoked using it and may differ from the one shown here.

•Right-click any cell in the selected range and select the Sort > Custom sorting command.

•Press Ctrl+Shift+R (Windows, Linux) or ⌘Cmd+⇧Shift+R (macOS).

3.If the Sort cells window appears with a suggestion to add adjacent cells to the range:

•Select Add the data and sort if you want to add adjacent cells to the current range, and click OK.

•Select Ignore the data if you want to apply sorting only to the selected range, and click OK.

4.In the Custom sorting window:

•Select the Columns have titles checkbox if you want to use the values of the upper cells as column titles. Titles will not be part of the sorting.

•In the Column column, select the name of the column you want to sort by from the drop-down list.

•In the Sort column, select By fill color from the drop-down list.

•In the Order column, select To top or To bottom sort order. Cells with the selected color will be moved to the top or bottom of the selected column, respectively. The remaining cells of the column will be located above or below these cells.

•In the Color column, select the color from the proposed set.

5.At the bottom of the window, click Apply.

Sort rows of the selected range by cell fill color

Sorting rows in the selected range by fill color is only available using custom sorting.

To sort the rows of the selected range by fill color, follow these steps:

1.Select a range of cells containing more than one column.

2.Open the Custom sorting window in one of the following ways:

•On the Home tab, in the Data group, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

•On the Data tab, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

The appearance of the  Sort button depends on the last command invoked using it and may differ from the one shown here.

•Right-click any cell in the selected range and select the Sort > Custom sorting command.

•Press Ctrl+Shift+R (Windows, Linux) or ⌘Cmd+⇧Shift+R (macOS).

3.If the Sort cells window appears with a suggestion to add adjacent cells to the range:

•Select Add the data and sort if you want to add adjacent cells to the current range, and click OK.

•Select Ignore the data if you want to apply sorting only to the selected range, and click OK.

4.In the Custom sorting window, click  Additional settings on the top panel of the window and select the Sort by rows checkbox.

5.Select the Rows have titles checkbox if you want to use the values in the first column of the selected range as row titles. Titles will not be part of the sorting.

6.In the Row column, select the name of the row you want to sort by from the drop-down list.

7.In the Sort column, select By fill color from the drop-down list.

8.In the Order column, select the sort order: To left or To right. The cells with the selected color will be moved to the leftmost or rightmost part of the row, respectively. The remaining cells will be placed to the right or left of these cells.

9.In the Color column, select a fill color from the available color set.

10.At the bottom of the window, click Apply.

Sort the range of filtering by cell fill color

The range of filtering can only be sorted by columns; sorting by rows is not available.

To sort the range of filtering by cell fill color, follow these steps:

1.Click Sort and filter in the upper cell of the column you want to sort by.

2.In the Sort and filter window, in the Sort type drop-down list, select By fill color.

3.Select the sort order using the  To top or  To bottom options. Cells with the selected color will be moved to the top or bottom of the selected column, respectively. The remaining cells will be located above or below these cells.

4.Select the fill color from the proposed set. The selected color will be ticked.

5.At the bottom of the window, click Apply.

You can also sort the range of filtering by cell fill color using custom sorting. To do this, proceed as follows:

1.Click Sort and filter in one of the top cells of the range.

2.In the Sort and filter window, in the Sort type drop-down list, select Custom sorting.

3.In the Custom sorting window:

•In the Column column, select the name of the column you want to sort by from the drop-down list.

•In the Sort column, select By fill color from the drop-down list.

•In the Order column, select the sort order: To top or To bottom. Cells with the selected color will be moved to the top or bottom of the selected column, respectively. The remaining cells will be located above or below these cells.

•In the Color column, select a color from the available color set.

4.At the bottom of the window, click Apply.

The appearance of the sort and filter button may differ from the one shown here because it depends on whether sorting has been applied before, the sort order (ascending or descending), and whether a filter has been applied:

–: No sorting or filtering applied.

–: Filter applied, no sorting applied.

–: Sorted in ascending order with no filter applied.

–: Sorted in descending order with no filter applied.

–: Sorted in ascending order with filter applied.

–: Sorted in descending order with filter applied.