Bạn đang xem bài viết How To Sort By Color In Excel * Productivity Portfolio được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. 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.
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 By Color In Excel (In Less Than 10 Seconds)
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:
3 Ways To Sort By Color In Excel
There are several ways to sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.
Instructions in this article apply to Excel for Microsoft Office 365, Excel 2019, Excel 2016, and Excel 2013 for Windows and Mac.
Select a Range to Be Sorted in Excel
Before data can be sorted, Excel needs to know the exact range to sort. Excel can automatically include related data in a range so long as there are no blank rows or columns within the selected area. Blank rows and columns between areas of related data are okay. Excel then determines if the data area has field names and excludes those rows from the records to be sorted.
Allowing Excel to select the range to be sorted is fine for small amounts of data. However, for large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before sorting.
If the same range is to be sorted repeatedly, the best approach is to give the range a name. If a name is defined for the range to be sorted, type the name in the Name Box, or select it from the associated drop-down list. This way, Excel automatically highlights the correct range of data in the worksheet.
Any sorting requires the use of sort order. When sorting by values, there are two possible sort orders: ascending and descending. However, when sorting by colors, no such order exists, so you must manually define the color sort order.
How to Sort by Cell Background Color in Excel
In the example below, the records of students age 20 and younger are highlighted in red. To sort the data by cell background color so that the red entries appear on top:
Highlight the range of cells to be sorted (cells A2 to D11 in the example).
Select the Sort on drop-down arrow and choose Cell Color.
Clear the My data has headers check box so that the first row doesn’t get cut off.
Select the Order drop-down arrow and choose Red.
When Excel finds different cell background colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.
Choose On Top from the drop-down list next to the sort order box so that the red cells will be at the top of the list, then select OK.
The four records with red backgrounds are grouped together at the top of the data range.
When working with calculations, you can make negative numbers in Excel appear red by default to help those numbers stand out more.
How to Sort by Font Color in Excel
In the example below, the records of students enrolled in nursing programs appear in red, and those enrolled in science programs are blue. To sort the data by font color:
Highlight the range of cells to be sorted (cells A2 to D11 in the example).
Select the Sort on drop-down arrow and choose Font Color.
Clear the My data has headers check box so that the first row doesn’t get cut off.
Select the Order drop-down arrow, then choose Red.
When Excel finds different font colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.
Choose On Top from the drop-down list next to the sort order box so that the red entries will be at the top of the list.
Select Add to add a second sort level.
Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Blue.
Select OK to sort the data and close the dialog box.
The two records with the red font color are grouped together at the top of the data range, followed by the two blue records.
How to Sort by Icon in Excel
Icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes. The example below contains dates and temperatures that have been conditionally formatted with the stoplight icon set based on the daily maximum temperatures.
Follow these steps to sort the data so that records displaying the green icons are grouped first, followed by the yellow icons, and then the red icons:
Highlight the range of cells to be sorted (cells A2 to B31 in the example).
Select the Column drop-down arrow, then choose the column containing the conditional icons (Temperature in the example).
Due to the way conditional formatting with icons works, you can leave the My data has headers check box selected.
Select the Sort on drop-down arrow, then choose Conditional Formatting Icon.
Select the Order drop-down arrow, then choose Green.
Choose On Top from the drop-down list next to the sort order box so that the green icon entries will be at the top of the list.
Select Add to add a second sort level.
Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Yellow.
Select Add to add a third sort level, then use the same settings as the first two levels, but this time select the Order drop-down arrow and choose Red.
Select OK to sort the data and close the dialog box.
The records with the green icon are grouped together at the top of the data range, followed by the records with the yellow icon, and then those with a red icon.
How To Sort And Filter Data In Excel
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.
Cập nhật thông tin chi tiết về How To Sort By Color In Excel * Productivity Portfolio trên website Hoisinhvienqnam.edu.vn. 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. Chúc bạn một ngày tốt lành!