From this quick tip, you'll discover how to swiftly organize cells by their background and font colors in Excel 365 - Excel 2010 spreadsheets.

A week ago, we looked into various methods for counting and totaling cells based on their colors in Excel. If you had the chance to read that piece, you might be wondering why we didn't cover how to filter and sort cells by color. The reason is that sorting by color in Excel requires a slightly different approach, which is precisely what we're about to dive into now.

Sorting Excel cells by color is relatively straightforward compared to counting, summing, and even filtering. No VBA code or formulas are required. We'll simply utilize the Custom Sort feature, available in all versions of Excel from 365 through 2007.

1. Highlight your table or a selection of cells.

2. On the Home tab > Editing group, press the Sort & Filter button and choose Custom Sort…

3. Within the Sort dialog box, configure the following settings from left to right:

   • The column you wish to sort by (the Delivery column in our case)
   • To sort by Cell Color
   • Pick the color of cells you desire at the top
   • Set the On Top position

   

4. Hit the Copy Level button to add another level with identical settings to the first. Under Order, select the next priority color. Repeat this process for each distinct color present in your table.

5. Press OK and confirm that your rows are sorted by color accurately. In our example, "Past Due" orders appear first, followed by "Due in" entries, and lastly the "Delivered" orders, just as intended.

   

   Tip: When dealing with multiple colors, there’s no need to create rules for each one. Focus only on the significant colors, such as "Past due" items in our scenario, leaving the rest unchanged.

   

If sorting by just one color is all you need, there’s a faster method. Just click the AutoFilter arrow next to the column header you intend to sort by, opt for Sort by color from the dropdown menu, and pick the desired cell color to place at the top or bottom. Additionally, you can access the "Custom Sort" dialog here, as seen in the screenshot below.

Sorting Cells by Font Color in Excel

Interestingly, sorting by font color in Excel mirrors the process for sorting by background color. You’ll again use the Custom Sort feature (Home > Sort & Filter > Custom Sort…) but this time select Font Color under "Sort on", as illustrated below.

For sorting by a single font color, Excel’s AutoFilter works perfectly:

Besides organizing cells by background and font color, there are several other instances where sorting by color proves useful.

Sorting by Cell Icons

Take, for instance, applying conditional formatting icons based on numbers in the Qty. column, as depicted below.

As shown, large orders exceeding six units are marked with red icons, medium-sized orders have yellow icons, and smaller orders display green ones. To ensure the most critical orders lead the list, use the Custom Sort feature similarly explained before and choose to sort by Cell Icon.

Specifying the order of two out of three icons suffices, and all rows with green icons will automatically shift to the bottom of the table.

Filtering Cells by Color in Excel

To filter rows in your worksheet by colors within a specific column, you can leverage the Filter by Color option available in Excel 365 - Excel 2016.

The downside of this feature is its limitation to filtering by a single color at a time. If you wish to filter data using two or more colors, follow these steps:

1. Add an extra column at the end of the table or adjacent to the column you aim to filter by, naming it "Filter by color".
2. Input the formula =GetCellColor(F2) into cell 2 of the newly added "Filter by color" column, replacing F with the column containing your colored cells.
3. Copy the formula throughout the "Filter by color" column.
4. Use Excel's AutoFilter conventionally and select the desired colors from the dropdown list.

Ultimately, you'll obtain a table displaying solely the rows matching the selected colors in the "Filter by color" column.

That wraps up today's discussion—thank you for reading!

The above is the detailed content of How to filter and sort cells by color in Excel. For more information, please follow other related articles on the PHP Chinese website!