Sort Excel 2013's Data Lists on Font and Fill Colors and Cell Icons

In Excel you normally sort the records of a data list or rows of a table on the values (entries) contained in one or more columns of the list or table; however, Excel 2013 also enables you to sort on the font or fill color or cell icons that you assign to them.

These colors and icons are assigned by using the Conditional Formatting feature to mark those values in the columns of a data list or table that are within or outside certain parameters with a distinctive font or fill colors or cell icon.

To sort a data list on a font color, fill color, or cell icon in a single field of the table, you click its AutoFilter button and then choose the Sort by Color option from the drop-down menu. Excel then displays a continuation menu on which you click the font color, fill color, or cell icon to use in the sort:

  • To sort the records so that those with a particular font color in the selected column — assigned with the Conditional Formatting Highlight Cell Rules or Top/Bottom Rules options — appear at the top of the data list, click its color swatch in the Sort by Font Color section on the continuation menu.

  • To sort the records so that those with a particular fill color in the selected column — assigned with the Conditional Formatting Highlight Cell Rules, Top/Bottom Rules, Data Bars, or Color Scales options — appear at the top of the data list, click its color swatch in the Sort by Font Color section on the continuation menu.

  • To sort the records so that those with a particular cell icon in the selected column — assigned with the Conditional Formatting Icon Sets options — appear at the top of the data list, click the icon in the Sort by Cell Icon section of the continuation menu.

You can also sort the data list on more than one color or cell icon in the Sort dialog box opened by selecting the Custom Sort option from the Sort & Filter button’s drop-down list on the Ribbon’s Home tab or on the Sort by Color continuation menu.

When you want to sort the records in a data list on more than one font or fill color or cell icon, you select the field with the color or icon from the Column drop-down list; select Font Color, Fill Color, or Cell Icon in the Sort On drop-down list; and then click the color swatch or icon to use in the first level of the sort in the Order drop-down list.

If you need to add another sort level, you click the Add Level button and then repeat this procedure of selecting the field in the Column drop-down list, selecting the Font Color, Fill Color, or Cell Icon in the Sort On drop-down list, and selecting the specific color or icon in the Order drop-down list.

When you finish defining all the levels for the sort, click OK to have Excel go ahead and sort the list’s records.

You can sort the records in the data list order by all the fill colors or cell icons assigned by applying the Conditional Formatting Color Scales and Cell Icons options.

For each of three or five sorting levels you define in the Sort dialog box, the name of the field in the Column drop-down list button remains the same in all levels along with the Fill Color or Cell Icon option in the Sort On drop-down list button.

Only the actual color or icon selected in the Order drop-down list button changes, reflecting the order in which you want to see the records appear in the sorted data list.

blog comments powered by Disqus
Advertisement

Inside Dummies.com