Đề Xuất 4/2023 # How To Sort By Color In Excel (In Less Than 10 Seconds) # Top 11 Like | Beiqthatgioi.com

Đề Xuất 4/2023 # How To Sort By Color In Excel (In Less Than 10 Seconds) # Top 11 Like

Cập nhật nội dung chi tiết về How To Sort By Color In Excel (In Less Than 10 Seconds) mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác 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 To Sort Data By Color In Excel?

How to sort data by color in excel?

When you using a worksheet, sometimes you may fill the rows or cells with various colors to make the worksheet much readable. And sometimes you want to sort the cells by color in Excel. In this case, you can use the sort function to sort the data by color quickly as follows:

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier…

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse Anything:

Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.

More than 20 text features:

Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.

Merge Tools

: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.

Split Tools

: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.

Paste Skipping

Hidden/Filtered Rows; Count And Sum

by Background Color

; Send Personalized Emails to Multiple Recipients in Bulk.

More than 300 powerful features;

Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

1. Select the range that you want to sort the data by color.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse:

Quickly insert

complex formulas, charts

and anything that you have used before;

Encrypt Cells

Create Mailing List

and send emails…

Super Formula Bar

(easily edit multiple lines of text and formula);

(easily read and edit large numbers of cells);

Paste to Filtered Range

Merge Cells/Rows/Columns

without losing Data; Split Cells Content;

Combine Duplicate Rows/Columns

… Prevent Duplicate Cells;

Compare Ranges

Select Duplicate or Unique

Rows;

Select Blank Rows

(all cells are empty);

Super Find and Fuzzy Find

in Many Workbooks; Random Select…

Exact Copy

Multiple Cells without changing formula reference;

Auto Create References

to Multiple Sheets;

Insert Bullets

, Check Boxes and more…

Extract Text

, Add Text, Remove by Position,

Remove Space

; Create and Print Paging Subtotals;

Convert Between Cells Content and Comments

Super Filter

(save and apply filter schemes to other sheets);

by month/week/day, frequency and more;

Special Filter

by bold, italic…

Combine Workbooks and WorkSheets

; Merge Tables based on key columns;

Split Data into Multiple Sheets

;

Batch Convert xls, xlsx and PDF

More than 300 powerful features

. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint

, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

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.

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.

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.

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.

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.

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.