Đề Xuất 6/2023 # How To Sort Data By Color In Excel? # Top 10 Like

Cập nhật nội dung chi tiết về How To Sort Data By Color In Excel? 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.

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…

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.

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)

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:

Sorting And Filtering Data With Excel

As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

Sorting Data

Go down to the Sort option – when hovering over Sort the sub-menu will appear

Select Expand the selection

The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

Filtering Data

The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

Go to the Data tab on Excel ribbon

Select the Filter tool

Select Eastern Company from the dropdown menu

Select Dylan Rogers from the Salesperson dropdown menu

Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

The Sort & Filter Tool

In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:

As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!

Use Learn Excel Now to help with all your Excel questions and training needs. We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.

Bạn đang đọc nội dung bài viết How To Sort Data By Color In Excel? trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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!