Xem Nhiều 11/2022 #️ How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010 / 2023 # Top 14 Trend | Trucbachconcert.com

Xem Nhiều 11/2022 # How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010 / 2023 # Top 14 Trend

Cập nhật thông tin chi tiết về How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010 / 2023 mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.

From this short tip you will learn how to quickly sort cells by background and font color in Excel 2016, 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 2016, 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

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

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.

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 Sort By Color In Excel * Productivity Portfolio / 2023

Why Use Color

Perhaps, at one-time, color sorting and filtering wasn’t available, but you can do it now. The tutorial below can be used for font colors or cell color. For these instructions, I’ll be using Microsoft Office 365. However, the steps can be used in older Excel versions.

It’s probably my laziness, but I find it useful to change a cell’s background color. I might use this when I’m prioritizing or grouping a list. I need to highlight a cell and choose a background color.

In my screen snap above, I’ve applied the cell background color to Column C. However, some people might prefer using color across a row. In the second screenshot below, I used conditional formatting. Regardless of whether you’re using columns or rows, the process is the same. It also works with Excel tables.

How to Sort by Color in Excel

Tick the My data has headers checkbox in the top-right if your worksheet uses them.

Select your first fill color and keep the Order value as On Top.

Add in your other color levels. If you prefer, you can also use the On Bottom option.

You should now see your sorted list by color. You’re not limited to just 3 colors, but you can keep adding if they’re on your sheet. You also don’t need to enter the last color as Excel will put it on the bottom. In my case, that was ” No Cell Color.”

Select the column you wish to filter that has color.

Select your cell color from the side menu.

The same process works if you wish to sort by font color.

How to Filter by Color in Excel

Excel allows you to sort and filter by color

The color can be either the cell background color or font color

You can place colors on top or bottom

The system knows which colors you’ve used

No cell color is for normal cells

You can add multiple color sorts

Sort rules are processed in order

Some people prefer to filter instead of sort. This is great if you want to focus in on certain items. Even better, the procedure is shorter.

In my example, I was using a background color. However, when I added colored text, Excel adjusted the options. On the side menu for Filter by Color, you would also see Filter by Font Color.

Quick Takeaways

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-17

How To Sort And Filter Data In Excel / 2023

Sorting and filtering data offers a way to cut through the noise and find (and sort) just the data you want to see. Microsoft Excel has no shortage of options to filter down huge datasets into just what’s needed.

Advertisement

The first and most obvious way to sort data is from smallest to largest or largest to smallest, assuming you have numerical data.

We can apply the same sorting to any of the other columns, sorting by the date of hire, for example, by selecting the “Sort Oldest to Newest” option in the same menu.

How to Filter Data in Excel

Because our list is short, we can do this a couple of ways. The first way, which works great in our example, is just to uncheck each person who makes more than $100,000 and then press “OK.” This will remove three entries from our list and enables us to see (and sort) just those that remain.

Advertisement

We can also combine filters. Here we’ll find all salaries greater than $60,000, but less than $120,000. First, we’ll select “is greater than” in the first dropdown box.

In the dropdown below the previous one, choose “is less than.”

Next to “is greater than” we’ll put in $60,000.

Next to “is less than” add $120,000.

Advertisement

How to Filter Data from Multiple Columns at Once

In this example, we’re going to filter by date hired, and salary. We’ll look specifically for people hired after 2013, and with a salary of less than $70,000 per year.

Add “70,000” next to “is less than” and then press “OK.”

Type “2013” into the field to the right of “is after” and then press “OK.” This will leave you only with employees who both make less than $70,000 per year who and were hired in 2014 or later.

Advertisement

Excel has a number of powerful filtering options, and each is as customizable as you’d need it to be. With a little imagination, you can filter huge datasets down to only the pieces of information that matter.

How To Sort By Color In Excel (In Less Than 10 Seconds) / 2023

Excel has a lot of options when it comes to sorting data.

And one of those options allows you to sort your data based on the color of the cell.

For example, in the below dataset, you can sort by color to get the names of all the students who scored above 80 together at the top and all the students who scored less than 35 together at the bottom.

With the sorting feature in Excel, you can sort based on the color in the cell.

In this tutorial, I will show you different scenarios where you can sort by color and the exact steps you need to do this.

Note that in this tutorial, I have taken examples where I am sorting based on numeric values. However, these methods work perfectly well even if you have text or dates instead of numbers.

Sort Based on a Single Color

If you only have a single color in your dataset, you can easily sort it based on it.

Below is a dataset where all the students who have scored more than 80 have been highlighted in green.

Here are the steps to sort by the color of the cells:

Select the entire dataset (A1:B11 in this example)

In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.

In the ‘Order’ drop-down, select the color based on which you want to sort the data. Since there is only one color in our dataset, it only shows one color (green)

In the second drop-down in Order, select On-top. This is the place where you specify whether you want all the colored cells at the top of the dataset or at the bottom.

The above steps would give you a dataset as shown below.

Keyboard Shortcut to Open the Sort Dialog box – ALT A S S (hold the ALT key then press A S S keys one by one)

Note that sorting based on color only rearranges the cells to bring together all the cells with the same color together. Rest of the cells remain as is.

This method works for cells that you have highlighted manually (by giving it a background color) as well as the ones where the cell color is because of conditional formatting rules.

Sort Based on Multiple Colors

In the above example, we only had cells with one color that needed to be sorted.

And you can use the same methodology to sort when you have cells with multiple colors.

For example, suppose you have a dataset as shown below where all the cell where the marks are more than 80 are in green color and the ones where marks are less than 35 are in red color.

And you want to sort this data so that you have all the cells with green at the top and all the ones with red at the bottom.

Below are the steps to sort by multiple colors in Excel:

Select the entire dataset (A1:B11 in this example)

In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.

In the ‘Order’ drop-down, select the first color based on which you want to sort the data. It will show all the colors that are there in the dataset. Select green.

In the second drop-down in Order, select On-top.

In the ‘Order’ drop-down, select the second color based on which you want to sort the data. It will show all the colors that are there in the dataset. Select Red.

In the second drop-down in Order, select On-Bottom.

The above steps would sort the data with all the green at the top and all the reds at the bottom.

Clarification: Sorting by color works with numbers as well as text data. You may be thinking that you can achieve the results shown above by just sorting the data based on the values. While, this will work in this specific scenario, imagine you have a huge list of clients/customers/products that you have manually highlighted. In that case, there is no numeric value, but you can still sort based on the color of the cells.

Sort Based on Font Color

Another amazing thing about sorting in Excel is that you can also sort by font color in the cells.

Suppose you have a dataset as shown below and you want to sort this data to get all the cells with the red color together.

Below are the steps to sort by font color in Excel:

Select the entire dataset (A1:B11 in this example)

In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.

In the ‘Order’ drop-down, select the color based on which you want to sort the data. Since there is only one color in our dataset, it only shows one color (red)

In the second drop-down in Order, select On-top. This is the place where you specify whether you want all the colored cells at the top of the dataset or at the bottom.

The above steps would sort the data with all the cells with the font in red color at the top.

Sort Based on Conditional Formatting Icons

Conditional formatting allows you to add a layer of visual icons that can make your data or your reports/dashboards look a lot better and easy to read.

If you have such data with conditional formatting icons, you can also sort this data based on the icons

Suppose you have a dataset as shown below:

Below are the steps to sort by conditional formatting icons:

Select the entire dataset (A1:B11 in this example)

In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.

In the ‘Order’ drop-down, select the icon based on which you want to sort the data. It will show all the icons that are there in the dataset. Select the green one first.

In the second drop-down in Order, select On-top.

In the ‘Order’ drop-down, select the second icon based on which you want to sort the data. It will show all the icons that are there in the dataset. Select yellow.

In the second drop-down in Order, select On-Top.

In the ‘Order’ drop-down, select the third icon based on which you want to sort the data. It will show all the icons that are there in the dataset. Select Red.

In the second drop-down in Order, select On-Top.

The above steps would sort the data set and give you all the similar icons together.

Note that sorting will follow the order in which you have it in the sorting dialog box. For example, if all the icons are set to sort and show at the top, first all the cells with green icons would be shown as it’s at the top in our three conditions. Then the resulting data would have the yellow icons and then the red icons.

Not Losing the Original Order of the Data

When you sort the data, you lose the original order of the dataset.

In case you want to keep the original dataset as well, it’s best to create a copy of the data and then perform the sorting on the copied data.

Another technique to make sure you can get back the original data is to insert a column with row numbers.

Once you have this column added, use this when sorting the data.

In case you need the original data order back at a later stage, you can easily sort this data based in the columns with the numbers.

Interested in learning more about sorting data in Excel. Here is a massive give on how to sort in Excel that covers a lot of topics such as sorting by text/dates/numbers, sorting from left-to-right, sorting based on partial text, case sensitive sorting, multi-level sorting and much more.

You May Also Like the Following Excel Tutorials:

Bạn đang xem bài viết How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010 / 2023 trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!