In Microsoft Excel, Sort & Filter is one of the essential tools. We use this for analyzing our data. If you have a large amount of data in your worksheets, you can use this to see data that fits your requirements. To find specific data from a large dataset, these tools help identify them effortlessly. In this tutorial, you will learn everything about Sort and Filter in Excel with suitable examples and proper illustrations.
Table of Contents hide
Download Practice Workbook
What is Sort and Filter in Excel?
How to Enable Sort And Filter in Excel
Types of Sort in Excel
1. Sort in Alphabetical Order
2. Sort by Smallest to Largest in Excel
3. Sort by Largest to Smallest
4. Multi-level Sort in Excel
5. SORT Function
Types of Filter in Excel
1. Regular Filter
2. Text Filters / Number Filters in Excel
3. Custom Filter
How to Undo Sort and Filter in Excel
1. Undo Sort
2. Undo Filter
💬 Things to Remember
Conclusion
Related Articles
Download Practice Workbook
Download this practice workbook.
Sorting and Filtering in Excel.xlsx
What is Sort and Filter in Excel?
Basically, sort and filter options in Excel narrow down a dataset to analyze it better. It provides you with limited data that fits your requirement. The filter tool allows you to filter a column with your given criteria. The sorting tool allows you to sort data of datasets or a column based on alphabetical order, numbers, dates, etc.
Have a look at the following dataset:
Now, let’s see what it looks like after sorting based on Profit (Descending order):
Now, let’s filter the dataset based on the region “South”:
How to Enable Sort And Filter in Excel
Now, you can sort and filter data in various ways. If you noticed from the previous section, there was a dropdown menu beside the header. Basically, in this section, we will show you how to enable that.
First, click on any cell of the dataset. Then, go to the Data tab in the ribbon. From Sort & Filter group, click on Filter.
After that, you will see the dropdown beside every header of the column.
Now, why this is helpful? Isn’t it for the Filter just? Basically, you can perform both sorting and filtering from here. It will save you a lot of time.
Types of Sort in Excel
Now, you can sort your dataset in various ways. In the following sections, we are showing some of this that will give you a clear idea about sorting in Excel. To demonstrate this and other examples, we will use the previous dataset.
1. Sort in Alphabetical Order
Now, you can sort your data based on alphabetical order. If your column contains text, you can use this for sorting. There are two types of alphabetical sorting. A to Z sorting and Z to A sorting in Excel.
Let’s sort our dataset based on alphabetical order for the Names.
📌 Steps
- First, click on the dropdown menu beside the header “Name”.
- Then, click on the Sort A to Z It will sort the Name column in ascending order.
- Now, if you click on the Sort Z to A option, it will sort the dataset in descending order.
As you can see, we are successful to sort our data based on alphabetical order in Excel.
Read More: How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
2. Sort by Smallest to Largest in Excel
Now, it actually works the same as the A to Z sort we showed earlier. If you have numerical data, use this to sort them from smallest to largest. It will sort them in ascending order.
Here, we are going to sort the Total Sales column from smallest to largest.
📌 Steps
- First, click on the dropdown menu beside the Total Sales column.
- After that, click on Sort Smallest to Largest.
In the end, you will see it sorted the Total Sales column from smallest to largest order in Excel.
Related Content: How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)
3. Sort by Largest to Smallest
Similarly, you can sort the dataset from largest to smallest (descending order). Here, we are sorting the dataset for the Profit column.
📌 Steps
- At first, click on the dropdown menu beside the Profit column.
- Then, click on Sort Largest to Smallest.
As you can see, it sorted the Profit column from largest to smallest order in Excel.
Related Content: Excel Sort Dates in Chronological Order (6 Effective Ways)
Similar Readings
- Sum Using OFFSET and MATCH in Excel (With Alternative Options)
- How to Sort Rows in Excel (2 Simple Methods)
- Sort Column by Value in Excel (5 Methods)
- How to Sort by Color in Excel (4 Criteria)
- How to Sort Dates in Excel by Year (4 Easy Ways)
4. Multi-level Sort in Excel
Now, you can perform multi-level sorting in Excel. You can also call it “Custom Sort” Multi-level sorting means, you can sort the dataset based on multiple columns. This method will first sort the dataset based on the first criteria. After that, it will again sort that based on the second criteria over that sorted column. Let’s see it in action.
Here are our multi-level sorting criteria:
- First, sort based on Region (A to Z).
- Second, sort it again based on Total Sales (Largest to Smallest).
📌 Steps
- First, click on the dropdown menu beside “Region”.
- Then, select Sort by Color. After that, click on Custom Sort.
- Now, select Region from the Sort by dropdown menu. Also, select Cell values and A to Z from the respective dropdown.
- Next, click on Add Level.
- Now, select Total Sales from the Then by dropdown menu. Also, select Cell values and Largest to Smallest from the respective dropdown menu.
- After that, click on OK.
As you can see, we are successful to perform multi-level sorting in Excel.
Read More: How to Create Custom Sort List in Excel
5. SORT Function
Now, you can also sort any dataset by the SORT function of Excel. We do not use this function too often. Because this function is only available in Excel 365.
Make sure to check the sort function before you start using this.
Here, we are going to sort the dataset based on the Product column (Descending order).
📌 Steps
- First, type the following formula in Cell H5:
=SORT(B5:F15,3,-1)
- After that, press Enter.
As you can see, the SORT function sorted the dataset based on the product in descending order. Here, it returns a range or array. You won’t be seeing any header here. But, you can use this in various scenarios.
Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)
Types of Filter in Excel
Now, the filter tool allows you to narrow down the dataset based on your criteria. After filtering the data, it will only show you your desired values. In the upcoming sections, we will show you some type of filtering in Excel.
1. Regular Filter
A regular filter means you can filter the dataset based on any values. When you enable the filer dropdown menu in the dataset, you can easily filter them with it.
Let’s filter our dataset based on the Product “TV”.
📌 Steps
- First, click on the dropdown menu beside the Product.
- Now, uncheck the (Select All) option.
- After that, check the box “TV” and click on OK.
As you can see, only the data of the product “TV” is shown here after the filtering.
Read More: Excel Auto Sort When Data Changes (9 Examples)
2. Text Filters / Number Filters in Excel
Now, the Text Filters or the Number Filters option allows you to filter your data based on particular criteria. If your columns have text values, you can filter those using the Text Filters.
Excel will automatically show the Number Filters on columns containing numeric values.
Here, we will filter the dataset based on the Total Sales of less than $3500.
📌 Steps
- First, click on the dropdown menu beside Total Sales.
- After that, select Number Filters and click on the Less Thanoption.
- As we are filtering the data based on total sales of less than $3500, enter that in the field. After that, click on OK.
As you can see from the previous screenshot, we are successful to filter the data based on the Total Sales.
Related Content: How to Use Excel Shortcut to Sort Data (7 Easy Ways)
3. Custom Filter
Like the custom sort, you can also use the Custom Filter to implement criteria with the AND or the OR logic.
Here, our goal is to filter the dataset based on the Total Sales of greater than $2000 but less than $3000. So, our data will be between $2000 and $3000.
📌 Steps
- At first, click on the dropdown menu beside Total Sales.
- Next, select Number Filters and click on the Custom Filter.
- Here, we are filtering the data based on total sales of less than $3000 and greater than $2000. So, enter the values in the field. Next, select And After that, click on OK.
As you can see, we have filtered the dataset using the Custom Filter command in Excel.
Read More: [Fix:] Sort and Filter Not Working in Excel
How to Undo Sort and Filter in Excel
1. Undo Sort
Two ways you can undo the sorting in your worksheet:
- Click on the Undo Or by pressing the Ctrl+Z on your keyboard. But, remember, this method will only after you have just applied to sort. This is not a reliable method.
- Another way to undo sorting is by creating a temporary column in your dataset so that you can revert back based on this column.
Read More: How to Undo Sort in Excel (3 Methods)
2. Undo Filter
To undo any filtering from your dataset:
- First, click on the dropdown menu where you applied the filtering.
- Now, click on the Clear Filter From “Region” option (column name can be different based on your filtering). It will take you to the main dataset:
Now, to remove the dropdown menu of filtering, follow these steps:
- Click on any cell of your dataset.
- Then, go to the Datatab.
- Now, from the Sort & Filter group, click on Clear. It will take you to the original state of the dataset.
Related Content: Excel Sort and Ignore Blanks (4 Ways)
💬 Things to Remember
✎ Before sorting the dataset, try to make a copy of that. Or make an extra column of numbers in ascending order.
✎ Text Filters will appear only for Text values in a column.
✎ Number Filters will appear if there are numerical values in the column.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the sort and filter in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- How to Sort by Name in Excel (3 Examples)
- Sort Drop Down List in Excel (5 Easy Methods)
- How to Auto Sort Table in Excel (5 Methods)
- Sort Excel Sheet by Date (8 Methods)
- How to Sort Merged Cells in Excel (2 Easy Methods)
FAQs
How do you do sorting and filtering data in Excel? ›
Click any cell in the range or table. On the HOME tab, click Sort & Filter, and click Filter. Click a drop-down arrow at the top of one of the columns to display its filter options.
How do I create a custom filter order in Excel? ›Select any cell within the range you want to sort. On the Data tab, in the Sort & Filter group, select Custom Sort. In the Custom Sort dialog box, click Options. Under Row, in the 'Sort by' drop down, select the row that you want to sort.
How do I filter data in Excel based on condition? ›On the Data tab, in the Sort & Filter group, click Advanced. To filter the list range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.
Is filtering in Excel is used to Sort information in a sequence? ›In addition to sorting, you may find that adding a filter allows you to better analyze your data. When data is filtered, only rows that meet the filter criteria will display and other rows will be hidden. With filtered data, you can then copy, format, print, etc., your data, without having to sort or move it first.
What are 3 ways you can sort data in Excel? ›Select an option: Sort A to Z - sorts the selected column in an ascending order. Sort Z to A - sorts the selected column in a descending order. Custom Sort - sorts data in multiple columns by applying different sort criteria.
How do I create a dynamic filter in Excel? ›- Go to Developer Tab –> Controls –> Insert –> ActiveX Controls –> Combo Box (ActiveX Controls). ...
- Click anywhere on the worksheet. ...
- Right-click on Combo Box and select Properties.
- In Properties window, make the following changes:
You sort data to quickly organize your data and to find the data that you want. You filter data to display only the rows that meet criteria that you specify and hide rows that you do not want displayed, for one or more columns of data.
How do I automate filters in Excel? ›- Select the data you want to filter.
- Click Data > Filter.
- Click the arrow. ...
- Choose specific values: Uncheck (Select All) to clear all of the check boxes, and then check the boxes for the specific value(s) you want to see.
Answer: Data source filters are the correct answer.
What is sequence filtering? ›(in bioinformatics) a program that masks regions of local compositional bias in biological sequences, usually as a preprocessing step for database‐search programs to reduce the number of spurious matches and ...
How do I sort and filter in Excel without mixing data? ›
- Select the column data you want to sort, and then click Data > Sort.
- In the Sort Warning dialog, keep Expand the selection option checked, and click Sort.
- In the Sort dialog, specify the criterion that you will sort on and the sorting order. See screenshot:
- Click OK.
Check that you have selected all of the data
If your data has empty rows and/or columns or if you are only wanting to filter a specific range, select the area you want to filter prior to turning Filter on. Failing to select the area leaves Excel to set the filter area.
- Select the column that you want to be sorted last (in this case, select the Sales data first – C1:C13).
- Click on the Data tab.
- In the Sort and Filter group, click on the Z to A sorting icon. ...
- The above step would make a Sort Warning dialog box pop-up. ...
- Click Sort.
- Highlight the rows and/or columns you want sorted. ...
- Navigate to 'Data' along the top and select 'Sort. ...
- If sorting by column, select the column you want to order your sheet by. ...
- If sorting by row, click 'Options' and select 'Sort left to right. ...
- Choose what you'd like sorted. ...
- Choose how you'd like to order your sheet.
You can use the Advanced Filter to create more powerful filters, such as filtering for unique records and using operators such as OR. Tea is in the Product name column twice. To filter so that tea displays only once, on the DATA tab, click Advanced. In the Advanced Filter dialog box, click the List range.
What are two different methods in which you can sort data? ›Ascending means going up, so an ascending sort will arrange numbers from smallest to largest and text from A to Z. Descending means going down, or largest to smallest for numbers and Z to A for text.
What are the types of sorting in Excel? ›Sorting is a feature in MS Excel that helps you organize data. You can sort a text column in alphabetical order (A-Z or Z-A). We can sort a numerical column from largest to smallest or smallest to largest. We can also sort a date and time column from oldest to newest or newest to oldest.
How do I organize messy data in Excel? ›- Get Rid of Extra Spaces: ...
- Select & Treat all blank cells: ...
- Convert Numbers Stored as Text into Numbers: ...
- Remove Duplicates: ...
- Highlight Errors: ...
- Change Text to Lower/Upper/Proper Case: ...
- Parse Data Using Text to Column: ...
- Spell Check:
Dynamic filters are a tool that allows you to browse through any amount of data much easier and immediately display the desired results, so you can find your tasks, projects, or logged time much faster than with the standard filters (Filters 1.0).
What is a dynamic table in Excel? ›Dynamic tables in Excel are the tables where when a new value is inserted into it. As a result, the table adjusts its size by itself. To create a dynamic table in Excel, we have two different methods: making a table of the data from the table section while another using the offset function.
How do I create a custom filter? ›
- Select the data item on which you want to filter. ...
- On the toolbar, click the filter icon .
- Click Create Custom Filter.
- Under Condition, select the type of condition you want.
- Optional: Type a value to search for in the Keywords box.
Select a cell in the column you want to sort. In the Data tab, go to the Sort & Filter group. Then you have two options. To sort values in ascending or descending order based on Excel's interpretation of the column, click the Sort A to Z or Sort Z to A icons.
What is the best way to sort and filter data in access? ›On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu. Add the fields on which you want to filter to the grid. In the Criteria row of each field, specify a criterion.
What and what are two options for filtering data? ›The auto filter and advanced filter are the two options for filtering data.
How do I automate everything in Excel? ›To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.
How do I set up automatic filters? ›- Open Gmail.
- In the search box at the top, click Show search options .
- Enter your search criteria. ...
- At the bottom of the search window, click Create filter.
- Choose what you'd like the filter to do.
- Click Create filter.
The FILTER function allows you to filter a range of data based on criteria you define. In the following example we used the formula =FILTER(A5:D20,C5:C20=H2,"") to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string ("").
What is the difference between filter and advanced filter? ›Here are some differences between the regular filter and Advanced filter: While the regular data filter will filter the existing dataset, you can use Excel advanced filter to extract the data set to some other location as well. Excel Advanced Filter allows you to use complex criteria.
Can filtering be auto and advanced? ›In addition to AutoFilter, you can create a custom filter by using the Advanced Filter tool. Advanced Filter allows you to generate a unique list of items and extract those items to another place in your worksheet or workbook.
How do you apply multiple conditions in filter transformation? ›- There is a default input group which takes input data.
- There is also a default output group which provides all those data which is not passed by any filter condition.
What are the four basic types of filters? ›
Filters can be active or passive, and the four main types of filters are low-pass, high-pass, band-pass, and notch/band-reject (though there are also all-pass filters).
What is the advanced filter option in Excel? ›Advanced filter uses the concept of a database. A database is designated as a row of labels with columns of data underneath each of those labels. Each column contains data of a specific type like a date, a number or text.
What are the types of filter in Excel? ›The two major types of data filters in Excel are text and number filters. Both types can help you search for words and symbols, but number filters can also use a range of values to refine a set of data. Excel uses logical arguments to determine how to apply a particular function, including the filter function.
What is the best approach to filter data from a data table based on a condition in UiPath? ›- Method 1 – Using "Filter Data Table" Activity.
- Method 2 – Using "DataTable.Select() method"
- Method 3 – Using "DataTable.AsEnumerable() method"
Using select method is the best approach to filter data from a data table based on a condition. Explanation: The 'SQL select statement' is used for returning the result of set of records from the given one or more tables of a provided database.
Which method allows you to filter elements based on specified criteria? ›jQuery filter() Method
The filter() method returns elements that match a certain criteria. This method lets you specify a criteria.
There are several filtration methods : simple or gravity, hot and vacuum filtrations. The selection of the appropriate method is typically dictated by the nature of the experimental situation.
What are the 3 types of filtering processes? ›Filters may be classed according to the nature of the driving force that causes filtration (i.e., gravity filters, pressure filters, and vacuum filters).
What are the five types of filtering? ›These filter types include low-pass, high-pass, band-pass, band-stop (band-rejection; notch), or all-pass.
How do you do grouping Sort and filter data in Excel? ›- Select any cell in the data range.
- On the Data tab, in the Sort & Filter group, click Sort.
- In the Sort dialog box, under Column, in the Sort by box, select the first column that you want to sort.
- Under Sort On, select the type of sort. ...
- Under Order, select how you want to sort.
How do I alphabetize rows in Excel but keep them together? ›
Alphabetize and keep rows together
If your data set contains two or more columns, you can use the A-Z or Z-A button to put one of the columns in alphabetical order and Excel will automatically move the data in other columns, keeping the rows intact.
Use of Transpose Option to Filter Data Horizontally in Excel
Choose the Transpose checkbox from the Operation options and press OK. Now, choose the transposed dataset and click on the Filter option from the Data Tab. Doing this will activate the filtering options on every column.
Filter a range of data
Select any cell within the range. Select Data > Filter. Select Text Filters or Number Filters, and then select a comparison, like Between. Enter the filter criteria and select OK.
- Choose the Entire Data.
- On the Data tab, in the Sort & Filter group, click Filter.
- Arrow in the Column Header will appear.
- Click the Arrow next to City.
- Click on Select All to clear all the checkboxes, and click the check box next to Gurgaon.
- Click OK.
Check the check box in front of each value to include in the filter. Each additional value extends the filter range.
What is sorting and filtering in Excel with example? ›You sort data to quickly organize your data and to find the data that you want. You filter data to display only the rows that meet criteria that you specify and hide rows that you do not want displayed, for one or more columns of data.
What is the difference between data sorting and data filtering? ›Essentially, sorting and filtering are tools that let you organize your data. When you sort data, you are putting it in order. Filtering data lets you hide unimportant data and focus only on the data you're interested in.
How do I sort and filter horizontally in Excel? ›- In Excel, select the cells that need to be sorted.
- On the Home tab, click "Sort & Filter" and choose "Custom Sort."
- In the "Sort" dialog box, choose "Options."
- In the "Sort Options" dialog box chose "Sort left to right" and click "OK."
- Start by typing your filter formula, like this: =FILTER(A3:C,C3:C="Text")
- Then use your filter function as the range for a sort function like this: =SORT(FILTER(A3:C,C3:C="Text"), 1, true)
Sorting in Excel is arranging data according to our requirements. It can be done alphabetically or numerically. Basic Sorting works when sorting is to do on only one column. Advanced Sorting is used in multi-level sorting, viz sorting required in 2 or more than 2 columns.
How do I sort by multiple criteria in Excel? ›
- Select the entire data set that you want to sort.
- Click the Data tab.
- Click on the Sort Icon (the one shown below). ...
- In the Sort Dialogue box, make the following selections. ...
- Click on Add Level (this will add another level of sorting options).
Data is typically sorted based on actual values, counts or percentages, in either ascending or descending order, but can also be sorted based on the variable value labels. Value labels are metadata found in some programs which allow the researcher to store labels for each value option of a categorical question.
What are the two options for filtering data? ›- Auto filter is the simpliest way to filter the data.
- It is appear on the menu bar, so, select data then click filter and then click the auto filter option.
- Unique records are created by using of advanced filter.
The techniques of sorting can be divided into two categories. These are: Internal Sorting. External Sorting.
How do you have Excel automatically Sort data? ›Auto sort with Formula in Excel
To auto sort with formula, we can use the formula =LARGE(B4:B14) .
First, use the keyboard shortcut (Alt ⇢ D ⇢ F ⇢ F) to apply the filter, or you can also go to the Home ⇢ Sort & Filter ⇢ Filter. Next, you need to click on the filter dropdown of the column “type” and from there, you need to untick all the values other than “City”. After doing click OK to apply the filter.
Is it possible to filter horizontally in Excel? ›To use a filter horizontally in Excel, you need to use the FILTER function that allows you to filter a table based on criteria that you specify in the function. You can specify a table and then using the name of the row, specify criteria to filter.
How do you filter in Excel across multiple columns? ›- First, you need to choose the header of the data table by selecting cells B4:F4 to apply the filter option.
- Now, open the Data tab and then choose the Filter option given under the Sort & Filter group.
To do this, select the column you want to sort by clicking the column's header cell. Next, choose Filter from the Data menu and then select Advanced Filter. In the Advanced Filter dialog box, click the Copy To Another Location option. If you want a unique list, be sure to click the Unique Records Only option.