Xem Nhiều 1/2023 #️ How To Sort Data By Color In Excel? # Top 5 Trend | Trucbachconcert.com

Xem Nhiều 1/2023 # How To Sort Data By Color In Excel? # Top 5 Trend

Cập nhật thông tin chi tiết về How To Sort Data By Color In Excel? 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.

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…

Read More… Free Download…

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.

Read More… Free Download…

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

with password;

Create Mailing List

and send emails…

Super Formula Bar

(easily edit multiple lines of text and formula);

Reading Layout

(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);

Advanced Sort

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.

Read More… Free Download… Purchase… 

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.

Increases your productivity by

Read More… Free Download… Purchase… 

How To Sort By Color In Excel * Productivity Portfolio

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 2022

Setting Up Data

When you sort and conditionally format data, usually it’s a large number of rows. The following examples use a set of data with 20 rows. The data represents a list of customers and the amount of revenue made from each sale.

(Data setup for sorting and formatting examples)

Notice that headers are used at the top of each column. This is important for sorting when you want to change the column to sort on. Excel’s sorting functionality is handy even when you only have a few rows. If you want to view a list of revenue numbers based on the highest value or lowest value, instead of eyeing values and determining the right one based on your own human review, Excel 2019 will ensure that you can sort values and find the ones that have the highest revenue.

Sorting Data

(Excel sort buttons)

Excel can identify if your data is a set of dates, textual values or numbers. The sort function then orders cells based on the detect data type. For instance, if you have a list of revenue sales, Excel knows to sort cells based on numeric values. If you have cells formatted as dates, Excel knows that these values should be ordered in chronological order. Cells that are text values such as customer names are ordered alphabetically.

(Sort configuration window)

The “Sort By” dropdown has the headers for each column listed. Since we have “Customer” and “Revenue” as a column header, these two values display in the “Sort By” dropdown. If you don’t have column headers, Excel lists the column letter labels. Should you have several columns, having only letter labels make it difficult to configure your sort order.

The “Sort On” dropdown defaults to “Cell Values,” which means that the value is used for the sort. This is the typical configurations, but you can also sort on cell color or font color. This is useful when you set conditional formatting, which is covered in the next section.

The “Order” dropdown indicates if you want to sort data in ascending or descending order. The “A to Z” option means that you want to sort data in ascending order. The “Z to A” option means that you want to sort data in descending order.

(Data sorted by “Revenue”)

Notice that names still match up with revenue values. This is because the “Sort” functionality knows to keep rows aligned even though you’re ordering data by one column. If you decide to change the order to customer names, repeat these steps and choose “Customer” from the “Sort By” dropdown. Columns are still aligned properly but rows are ordered again based on the customer’s name.

Conditional Formatting

Sorting data doesn’t highlight certain cells that might need to stand out among the others. For instance, you might want to know which customers had revenue within a specified range. You might want to know which customers had revenue under or over a certain threshold. You can sift through all of your records, but conditional formatting that changes the font or background makes these cells stand out much more and makes them easier to find. With a short customer revenue list that contains only 20 rows, you can easily find the customers that bought and added revenue to your income, but if you had thousands of records even a sorted list would make it difficult to find specific records.

Excel has a function called “conditional formatting” that changes the color of a cell’s font or the background color of a cell to make it stand out and easy to find when you’re looking for certain values that meet a condition.

(Conditional Formatting button)

The “Conditional Formatting” button is found in the “Home” ribbon tab. The image above shows the Conditional Formatting button, which is also in the “Styles” category.

(Conditional formatting dropdown options)

With conditional formatting, you aren’t limited to just one color with one condition. You can set multiple colors using multiple conditions. For instance, you might want to know which customers brought in revenue under $100 and which customers brought in over $1000. You can then take this data and use it for reporting and product information. Using revenue charts and conditional formatting, you then know which customers are the best (or worst) to market to and upsell additional product.

From the “Highlight Cells Rules” dropdown options, choose the “Greater Than” option. This opens a new configuration window.

(Greater than conditional formatting configuration window)

(Conditional formatting set on cells greater than $1000)

With conditional formatting, you can now quickly see which customers brought in revenue over $1000. This formatting persists even when you sort cells again using the “Sort” option. Should you decide to use other conditions, you can make them other colors to make it easy to distinguish between the two conditions.

Once you understand the way conditional formatting and sorting works, you can make it much easier to work with large data sets that must be evaluated each month, especially revenue sheets.

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.

Bạn đang xem bài viết How To Sort Data By Color In Excel? 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!