Lỗi Filter Trong Excel 2010 / TOP #10 ❤️ Xem Nhiều Nhất & Mới Nhất 9/2022 ❣️ Top View | Hoisinhvienqnam.edu.vn

How To Sort Data By Color In Excel?

3 Ways To Sort By Color In Excel

How To Allow Sorting And Filter Locked Cells In Protected Sheets?

Hướng Dẫn Sử Hàm Chức Năng Atan Trong Excel.

Calculating Coefficient Of Variation In Excel

From this short tip you will learn how to quickly sort cells by background and font color in Excel 2022, Excel 2013 and Excel 2010 worksheets.

Last week we explored different ways to count and sum cells by color in Excel. 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. 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.

Sort by cell color in Excel

Sorting Excel cells by colour is the easiest task compared to counting, summing and even filtering. Neither VBA code nor formulas are needed. We are simply going to use the Custom Sort feature available in all modern versions of Excel 2022, 2013, 2010 and 2007.

Select your table or a range of cells. In the Sort dialog window, specify the following settings from left to right. The column that you want to sort by (the Delivery column in our example) To sort by Cell Color Choose the color of cells that you want to be on top Choose On Top position

Select your table or a range of cells. In the Sort dialog window, specify the following settings from left to right. The column that you want to sort by (the Delivery column in our example) To sort by Cell Color Choose the color of cells that you want to be on top Choose On Top position

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.

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. You can create rules only for those colors that really matter for you, e.g. “Past due” items in our example and leave all other rows in the current order.

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. You can create rules only for those colors that really matter for you, e.g. “Past due” items in our example and leave all other rows in the current order.

Sort cells by font color in Excel

If you want to sort by just one font color, then Excel’s AutoFilter option will work for you too:

Apart from arranging your cells by background colour and font color, there may a few more scenarios when sorting by color comes in very handy.

Sort by cell icons

For example, we can apply conditional formatting icons based on the number in the Qty. column, as shown in the screenshot below.

As 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. 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.

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.

How to filter cells by color in Excel

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 2010, Excel 2013, and Excel 2022.

The limitation of this feature is that it allows filtering by one color at a time. If you want to filter your data by two or more colours, perform the following steps:

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”. 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. Copy the formula across the entire “Filter by color” column. Apply Excel’s AutoFilter in the usual way and then select the needed colors in the drop-down list.

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”. 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. Copy the formula across the entire “Filter by color” column. Apply Excel’s AutoFilter in the usual way and then select the needed colors in the drop-down list.

As a result, you will get the following table that displays only the rows with the two colors that you selected in the “Filter by color” column.

And this seems to be all for today, thank you for reading!

Most likely this is going to be my last article in this year, so let me take a moment and wish you Merry Christmas and a very Happy New Year. We will be delighted to welcome you again here on this blog in the year of 2014!

You may also be interested in

How To Alphabetize In Excel: Sort Alphabetically Columns And Rows

How To Use The Excel Sort Function

How To Sort In Excel

How To Use The Excel Subtotal Function

Hàm Sum Trong Excel – Cách Sử Dụng Hàm Tính Tổng Qua Ví Dụ