Cập nhật nội dung chi tiết về How To Sort And Filter Data In Excel 2022 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.
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.
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.
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.
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.
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.
How To Allow Sorting And Filter Locked Cells In Protected Sheets?
How to allow sorting and Filter locked cells in protected sheets?
In general, the protected sheet cannot be edited, but in some cases, you may want to allow the other users to do sorting or filtering in the protect sheets, how can you handle it?
Allow sorting and filtering in a protected sheet
Allow sorting and filtering in a protected sheet
To allow sorting and filter in a protected sheet, you need these steps:
5. In the Protect Sheet dialog, type the password in the Password to unprotect sheet text box, and in Allow all users of this worksheet to list to check Sort and Use AutoFilter options. See screenshot:
Then the users can sort and filter in this protected sheet
Tip. If there are multiple sheets needed to protect and allow users to sort and filter, you can apply Protect Worksheet utility of Kutools for Excel to protect multiple sheets at one time.please go to free try Kutools for Excel first, and then go to apply the operation according below steps.
Now all specified sheets have been protected but allowed to sort and filter.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
complex formulas, charts
and anything that you have used before;
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
without losing Data; Split Cells Content;
Combine Duplicate Rows/Columns
… Prevent Duplicate Cells;
Select Duplicate or Unique
Select Blank Rows
(all cells are empty);
Super Find and Fuzzy Find
in Many Workbooks; Random Select…
Multiple Cells without changing formula reference;
Auto Create References
to Multiple Sheets;
, Check Boxes and more…
, Add Text, Remove by Position,
; Create and Print Paging Subtotals;
Convert Between Cells Content and Comments
(save and apply filter schemes to other sheets);
by month/week/day, frequency and more;
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 Randomly Sort A List In Excel
Have you ever needed to take a list and randomly sort it? How would you do it? It’s not as intuitive as you think. We’re so used to sorting lists in alphabetical order or from smallest to largest, but this idea of randomly sorting a list is not very common in Excel. Let’s jump right into it and show you how to randomly sort a list in Excel.
Say we have some data that we’d like to sort:
Normally, we could sort by Movie title, the date that it opened (as it’s shown in the picture), or by the total amount it grossed. But what if we wanted to “shuffle” this list? How could we go about doing that?
By the way, this is the same data that we used in the Sum the Top 5 Values post.
You can also copy and paste this table to follow along:
Movie Date Opened Total Gross
Deadpool 2/12/16 $363,070,709
Zootopia 3/4/16 $341,268,248
Batman v Superman: Dawn of Justice 3/25/16 $330,360,194
The Jungle Book (2016) 4/15/16 $364,001,123
Captain America: Civil War 5/6/16 $408,084,349
Finding Dory 6/17/16 $486,295,561
The Secret Life of Pets 7/8/16 $368,384,330
Suicide Squad 8/5/16 $325,100,054
Rogue One: A Star Wars Story 12/16/16 $532,177,324
Sing 12/21/16 $270,329,045
Does this article help you? If so, please consider supporting me with a coffee ☕️
Randomly Sort a List in Excel
To sort a list randomly in Excel, first you need to add a new column to your data. When using Excel Tables, you can simply type in a new column name at the next available table header and it will be automatically joined to your table.
Here, we type in “Sort Order” and press Enter.
Next, we need a way to randomly sort the list. We can use the RAND() function in Excel to help us with that. The RAND() function will return a number at random between 0 and 1.
After we add the formula, we can sort by that column. This will randomize the list for us.
Also, each time the RAND() function is calculated, you get a different number. This is why you see the table has all different numbers.
Another cool tip is that if you’re not happy with the initial shuffling of the list, you can simply keep sorting between Ascending and Descending to keep shuffling the list. This works because when you sort the list, it recalculates the RAND() function, giving a new number each time.
Keeping the List Order
But what if you don’t want the RAND() function to keep recalculating? What if you want to keep the sort order?
If you want to keep the sort order, you can simply save the values from RAND() before sorting again.
Bạn đang đọc nội dung bài viết How To Sort And Filter Data In Excel 2022 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!