In Excel for Microsoft 365 on Windows, sort by several criteria with Data > Sort and add a level for each priority; filter records by choosing conditions from the column dropdowns. For more complex AND/OR rules, use Advanced Filter or a formula with FILTER.
Sorting changes the order of records, while filtering hides records that do not meet your conditions. Choosing the right method depends on whether you want to rearrange a list, temporarily narrow what you see, or create a separate results list that updates with your data.
What multiple criteria mean in Excel
Multiple sort criteria tell Excel which field to compare first, second, and so on. For example, you might sort a sales list by Region from A to Z, then by Sales from largest to smallest, and finally by Date from oldest to newest. The second rule only determines the order of rows tied on the first rule; the third rule resolves ties left by the first two.
Multiple filter criteria define which records remain visible. If you filter Region to West and Sales to values greater than or equal to 500, Excel shows rows that satisfy both conditions. Within a single column, you can often select multiple values to show either one value or another. These distinctions matter: sorting does not remove records, and filtering does not permanently rearrange or delete them.
Sort by multiple levels with the Sort dialog
Use the Sort dialog when the order matters across multiple columns. First make sure the list has a header row and that each record occupies one row. Click a cell in the data, then select Data > Sort. In the dialog, select the first column under “Sort by,” choose the sort basis and order, and click Add Level to define the next priority. Add further levels as needed, then select OK.
- Choose the main grouping field under Sort by, such as Region, and set its order to A to Z.
- Select Add Level. Under Then by, choose Sales and set the order to Largest to Smallest.
- Add another level for Date and choose Oldest to Newest if dates should break any remaining ties.
- Check that My data has headers is selected when the first row contains field names, then apply the sort.
Excel applies the first level before the next one. A common example is a staff roster sorted by Department, then Last Name, then First Name. If two employees have different departments, their names are not compared to decide which department appears first. The Sort dialog also supports sorting by cell color, font color, or icon when those options are relevant, and custom lists can put known labels in a nonalphabetical order.
Take care when Excel asks whether to expand the selection. Sorting just one column can separate values from the other fields in their rows. In a normal table, sort the whole data range so each record stays together. If the data is an Excel Table, clicking a cell in the table and using the Sort dialog is a reliable way to work with its records.
Filter several columns with AutoFilter
For routine filtering, turn on the header dropdowns using Data > Filter. If your range is formatted as a table, its headers normally include filter buttons automatically. Open a column’s dropdown, clear Select All if necessary, choose the values to keep, and confirm. Repeat in other columns to combine conditions.
For instance, filtering Region to West and Sales to greater than or equal to 500 displays only West records meeting the sales threshold. Filters on different columns work together as AND conditions: a row must pass every active column filter to remain visible. In one column, selecting West and East generally works as an OR condition: a row can match either selected value.
Number, text, and date columns offer relevant filter commands, such as number comparisons or date ranges. You can also use the column’s search box to find a particular text value. The dropdown’s options depend on the data type and the values in that column. To restore the full list, clear the individual filter or use Data > Clear; that clears filter conditions but does not delete hidden rows.
Use Advanced Filter for AND and OR logic
Advanced Filter is useful when the criteria are too specific or layered for ordinary dropdown selections. It uses a separate criteria range: copy the exact column headers from the data into a blank area, then put criteria below those headers. Select a cell in the list and choose Data > Advanced in the Sort & Filter group. Specify the list range and criteria range, then choose whether to filter the list in place or copy matching records to another location.
The layout determines the logic. Put conditions on the same criteria row to require them together; put alternatives on separate rows to allow either row to match. For example, with headers Region and Sales, a row containing West and >=500 means Region is West AND Sales is at least 500. Two criteria rows containing West on one row and East on another mean West OR East. You can combine this: put West and >=500 on one row, and East and >=700 on the next to accept either complete condition set.
Advanced Filter also has a Unique records only option. When selected, it can return distinct records from the chosen list range; it does not mean “keep only records that occur once” in every possible interpretation. If you need to identify duplicates or retain a particular occurrence, use a dedicated duplicate-checking method rather than assuming this option performs that task.
Create a live results list with FILTER
A formula is a good choice when you want matching rows displayed elsewhere and refreshed as the source values change. FILTER is available in Excel 2021 and Microsoft 365. Consider a small data range with headers in row 1 and records in A2:D7: column A is Region, B is Rep, C is Sales, and D is Date. The six records are North/Ana/620/Jan 8, West/Ben/480/Jan 9, West/Cara/730/Jan 10, West/Dev/560/Jan 11, East/Eli/810/Jan 12, and West/Farah/650/Jan 13, all dates in 2026.
With those records in A2:D7, enter this in an empty cell to return West records with sales of at least 500, sorted by Sales from highest to lowest:
=SORTBY(FILTER(A2:D7,(A2:A7="West")*(C2:C7>=500)),FILTER(C2:C7,(A2:A7="West")*(C2:C7>=500)),-1)
The result contains Cara at 730, Farah at 650, and Dev at 560, in that order. In the include test, multiplication (*) combines true/false tests as AND. For an OR test, addition (+) can combine conditions, such as a match for West or East. Keep each test range the same height as the source data. The formula spills into adjacent cells, so leave room for the results; if occupied cells block the spill, Excel returns a spill error.
This example uses exact text matching for West and a numeric threshold for Sales. If the source range grows beyond row 7, update the ranges or convert the source into a table and adapt the formula to table references. The formula returns a view of matching records; it does not move or delete the source data. If no records match, FILTER can be given an optional fallback value, such as a message, to avoid an empty-result error.
Choose the method that fits the job
- Use the Sort dialog when the goal is to rearrange an entire list using ordered priorities, such as Department, then Last Name.
- Use AutoFilter for quick, interactive narrowing by values or common conditions in one or more columns.
- Use Advanced Filter for criteria ranges with explicit AND/OR combinations or for copying matching records elsewhere.
- Use FILTER with SORTBY when you need a formula-driven results area that recalculates as source values change.
These tools can complement each other. You might filter a table to inspect a region, use a multi-level sort to review its records in priority order, or use a formula to produce a separate summary list while leaving the source intact. Filtering is usually the simplest choice for exploration; formula results are more useful when another sheet or report should display matching rows automatically.
Common problems and how to avoid them
- Only one column moves: undo the sort if the records became mismatched, then sort the whole range or table. Confirm that Excel is sorting the complete record, not an isolated field.
- Headers appear among the records: check the header setting in the Sort dialog and ensure the list has a single, clearly labeled header row.
- A sort order looks wrong: numbers or dates stored as text may sort differently from genuine numbers or dates. Check the cell values and convert inconsistent entries to the appropriate data type.
- Rows seem to be missing: a filter may still be active. Inspect the header dropdowns or use Data > Clear to clear filters.
- Criteria fail unexpectedly: Advanced Filter criteria headers must match the source headers. Check for extra spaces, incorrect comparison operators, or criteria placed on the wrong row.
- A formula gives an error or unexpected rows: confirm that the include range matches the data range in size, the criteria text matches the cell values, and the spill area is empty. FILTER and SORTBY require a version that supports dynamic arrays.
For a repeatedly used list, an Excel Table can make the data easier to maintain: select the range and choose Insert > Table, then confirm that the table has headers. Tables expand when records are added, and their header dropdowns provide convenient filtering and sorting. If you use formulas with ordinary cell ranges, remember to extend the references when new records are added.
Frequently Asked Questions
Can I sort Excel by three or more criteria? Yes. Open Data > Sort, use Add Level for each additional priority, and arrange the levels in the order Excel should apply them. Each later level resolves ties from the levels above it.
Can I filter by two conditions in the same column? Yes. Use the dropdown’s value selections to include multiple values, or select a text, number, or date filter when you need comparisons. In Advanced Filter, put conditions on one row when both must be true; put alternatives on separate rows when either can match.
Does filtering delete rows that do not match? No. A normal filter hides nonmatching rows; the records remain in the worksheet. Clear the filter to show them again. Copying results with Advanced Filter or returning them with FILTER creates a separate output rather than deleting the source.
Can I sort by one column and keep each row together? Yes. Sort the complete list or table, not just the cells in one field. If Excel offers to expand the selection, choose the option that includes the adjacent data so each row’s related values remain together.
Why can’t I find the FILTER function? FILTER is available in Excel 2021 and Microsoft 365, but not in some older Excel releases. In an older version, use AutoFilter or Advanced Filter instead. Excel for the web also supports modern dynamic-array formulas, though ribbon placement and dialog details can differ from the Windows desktop app.
Related
- Excel Row and Column Select Shortcut Keys Explained
- Excel Ribbon Keyboard Shortcuts: A Practical Guide
- Excel Reverse Undo Shortcut: What Works and What Doesn’t
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function: Extract Text from a Cell’s MiddleTo extract characters from the middle of a cell in Excel, use the MID function, which takes a…
- Excel IFS Between Two Numbers: Formula and ExamplesTo check whether a value in A2 falls between 10 and 20, inclusive, use =IFS(AND(A2>=10,A2<=20),"In range",TRUE,"Out of range").…
- Remove Duplicates in Excel Filter: A Step-by-Step GuideTo remove duplicate rows in Excel for Microsoft 365 on Windows, select a cell in your data, choose…
- Excel Formula List in Hindi: Meanings, Uses and ExamplesAn Excel formula list in Hindi is a guide to common spreadsheet functions, their Hindi meanings, and examples…
- Excel Roundup a Sum: 10 Useful Formula PatternsTo round an Excel total up to the next whole number, put the amounts in B2:B5 and enter…
- Excel NPV Formula Year by Year: How It WorksIn Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.