Xu Hướng 6/2023 # How To Sort By Color In Excel (In Less Than 10 Seconds) # Top 13 View

Bạn đang xem bài viết How To Sort By Color In Excel (In Less Than 10 Seconds) đượ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.

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:

How Do You Sort By Color In Microsoft 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.

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:

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.

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:

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 stoplighticon 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:

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.

Thanks for letting us know!

Other Not enough details Hard to understand

How To Sort In Excel

You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

One Column

To sort on one column, execute the following steps.

Result:

Multiple Columns

To sort on multiple columns, execute the following steps.

The Sort dialog box appears.

2. Select Last Name from the ‘Sort by’ drop-down list.

4. Select Sales from the ‘Then by’ drop-down list.

Result. Records are sorted by Last Name first and Sales second.

How To Sort Lists In Excel 2003

Use these tips to prevent problems when sorting in Excel. How to sort two or more columns, sort by row, sort in a Custom Order. For Excel 2007 and later, see the Sorting Data Basics page

Using the Sort Buttons

In Excel, it’s easy to sort your data by using the Sort buttons on the toolbar. But, be careful, or one column may be sorted, while others are not.

Select one cell in the column you want to sort.

Sort Data by One Column

Select all the cells in the list. This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list — but it’s not 100% certain. Some of the data may be missed.

From the Sort by dropdown, select the column you want to sort.Note: If the dropdown is showing Column letters instead of headings, change the setting forMy list has, fromNo header row to Header row.

Select to sort in Ascending or Descending order

Sort Data by 2-3 Columns

Select all the cells in the list.

From the Sort by dropdown, select the first column you want to sort.

Select to sort in Ascending or Descending order

From the Then by dropdown, select the second column you want to sort.

Select to sort in Ascending or Descending order

From the Then by dropdown, select the third column you want to sort.

Select to sort in Ascending or Descending order

Sort Data by 4+ Columns

Occasionally, you may need to sort by more than three columns. For example, in a mailing list, you may want to sort by Country, Region, City, and Name. To do this, you can sort the list multiple times, starting with the least important sort.

In the mailing list, there are four columns to sort. Name and City are the least important fields in the sorting process, so they can be sorted first.

Excel will retain what it can of this sort while you sort by the remaining fields.

From the Sort by dropdown, select Country.

From the Then by dropdown, select Region.

After sorting, the list is sorted by Country, then by Region, then by City and finally by Name.