Cập nhật thông tin chi tiết về How To Sort In Excel / 2023 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.
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.
To sort on one column, execute the following steps.
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.
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.
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-17
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.
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.
(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.
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.
Bạn đang xem bài viết How To Sort In Excel / 2023 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!