Enter the formula =GetCellColor(F2) in cell 2 of the newly added "Filter by color" column, where F is the column congaing your colored cells that you want to filter by.Create an additional column at the end of the table or next to the column that you want to filter by, let's name it " Filter by color".If you want to filter your data by two or more colours, perform the following steps: The limitation of this feature is that it allows filtering by one color at a time. If you want to filter the rows in your worksheet by colors in a particular column, you can use the Filter by Color option available in Excel 365 - Excel 2016. It is enough to specify the order of two icons out of 3, and all the rows with green icons will get moved to the bottom of the table anyway. If you want the most important orders to be on top of the list, use the Custom Sort feature in the same way as described earlier and choose to sort by Cell Icon. column, as shown in the screenshot below.Īs you see, big orders with quantity more than 6 are labeled with red icons, medium size orders have yellow icons and small orders have green icons. Sort by cell iconsįor example, we can apply conditional formatting icons based on the number in the Qty. If you want to sort by just one font color, then Excel's AutoFilter option will work for you too:Īpart from arranging your cells by background colour and font color, there may a few more scenarios when sorting by color comes in very handy. You use the Custom Sort feature again ( Home > Sort & Filter > Custom Sort…), but this time choose Font Color under " Sort on", as shown in the screenshot below. In fact, sorting by font colour in Excel is absolutely the same as sorting by background color. BTW, you can also access the " Custom Sort" dialog from here, as you can see in the right hand part of the screenshot below. Simply click on the AutoFilter arrow next to the column heading you want to sort by, choose Sort by color from the drop down menu, and then select the color of cells that you want to be on top or at the bottom. If sorting cells by only one color is what you are looking for, then there's even a quicker way. " Past due" items in our example and leave all other rows in the current order. You can create rules only for those colors that really matter for you, e.g.
Tip: If your cells are colored with many different colors, it is not necessary to create a formatting rule for each and every one of them. In our table, the " Past Due" orders are on top, then come " Due in" rows, and finally the " Delivered" orders, exactly as we wanted them.
Neither VBA code nor formulas are needed. Sorting Excel cells by colour is the easiest task compared to counting, summing and even filtering. The reason is that sorting by color in Excel requires a bit different technique, and this is exactly what we are doing to do right now.
If you've had a chance to read that article, you may wonder why we neglected to show how to filter and sort cells by color. Last week we explored different ways to count and sum cells by color in Excel. From this short tip you will learn how to quickly sort cells by background and font color in Excel 365 - Excel 2010 worksheets.