Advanced filtering and sorting in Excel: find what you need in seconds

When a sheet grows from a few dozen rows to several thousand, scrolling with the mouse wheel is no longer an option. You need to see only one customer’s orders, amounts above a certain figure or a region’s sales sorted from highest to lowest, and you need to do it fast.

Excel offers several tools for this: AutoFilter, multi-level sorting, Advanced Filter and, in recent versions, functions such as FILTER and SORT. Here you will see when to use each one, with concrete examples and the mistakes that tend to ruin the result.

Before you start: prepare your data

Filtering and sorting only work well if the list is properly built. Check these points:

• A single header row with unique names.
• No completely empty rows or columns inside the data.
• No merged cells.
• One data type per column: numbers as numbers and dates as dates, not as text.

If you also turn the list into a table with Ctrl+T, filters are switched on automatically and the range expands as you add rows.

The examples use this sales list in the range A1:D100:

AutoFilter: the basics, done right

Select a cell in the list and go to Data › Filter (or press Ctrl+Shift+L). An arrow appears on every header. Besides ticking and unticking values, each column’s menu offers filters based on the data type:

• Text Filters: contains, begins with, ends with, does not equal.
• Number Filters: greater than, between, Top 10, above average.
• Date Filters: this month, last quarter, between two dates.
• Filter by Color: useful if someone has highlighted rows by hand or with conditional formatting.

In the filter’s search box you can use wildcards: * stands for any number of characters and ? for exactly one. For example, Product ? shows Product 1, Product 2 and Product 3, but not Product 10.

Filters on several columns add up: if you filter Region = North and Amount greater than 1,000, you see the rows that meet both conditions. To remove them all at once, use Data › Clear.

Custom multi-level sorting

The A-Z and Z-A buttons sort by a single column. To combine criteria, use Data › Sort:

1. Select a cell in the list and open Data › Sort.
2. In the first level choose Region, A to Z.
3. Click Add Level and choose Amount, largest to smallest.
4. Make sure the My data has headers box is ticked and click OK.

The result groups regions alphabetically and, within each one, shows the highest amounts first.

Orders that aren’t alphabetical

If a column contains priorities such as High, Medium and Low, alphabetical order doesn’t help. In the Order drop-down choose Custom List and type the values in the order you want. Excel will remember it for other workbooks on the same computer.

Be careful when sorting a single column

If you select just one column and sort it, Excel asks whether you want to expand the selection. Always choose Expand the selection: otherwise only that column is reordered and every value ends up paired with the wrong row.

Advanced Filter: conditions AutoFilter can’t handle

AutoFilter always combines columns with AND. If you need “North with an amount over 1,000, or any sale in the South”, use Advanced Filter. First type a criteria range, for example in F1:G3, copying the exact headers:

Conditions on the same row are combined with AND, and conditions on different rows with OR. Then:

1. Select a cell in the list and go to Data › Advanced.
2. Check the list range and enter F1:G3 as the criteria range.
3. Choose Filter the list, in-place, or Copy to another location if you’d rather have the result separately.
4. Click OK.

This dialog also includes the Unique records only box, which hides repeated rows without deleting them.

Filtering and sorting with formulas (Excel 365 and 2021)

Dynamic array functions return a result that updates by itself when the data changes, without reapplying any filter. To see North sales with an amount above 1,000:

=FILTER(A2:D100,(B2:B100="North")*(D2:D100>1000),"No results")

The asterisk works as AND. For an OR condition, add the conditions together:

=FILTER(A2:D100,(B2:B100="North")+(B2:B100="South"))

You can also sort the result. This formula shows North sales sorted by the fourth column (Amount) from largest to smallest:

=SORT(FILTER(A2:D100,B2:B100="North"),4,-1)

To get a sorted list of regions without repeats:

=SORT(UNIQUE(B2:B100))

Enter these formulas in an area with free space below and to the right: the result spills into neighboring cells and, if any of them is occupied, Excel shows a spill error instead of the data.

Time-saving tricks

• Filter by the active cell: right-click a value › Filter › Filter by Selected Cell’s Value.
• Totals for visible rows: =SUBTOTAL(109,D2:D100) adds up only the rows the filter leaves visible.
• Copying the result: when you copy a filtered range, Excel copies only the visible rows, so you can paste the result into another sheet directly.
• Reapply: if you change data while a filter is active, Data › Reapply refreshes the view without redoing the criteria.

Common mistakes when filtering and sorting

• Empty rows in the middle: the filter or sort stops at the first blank row and leaves the rest out.
• Numbers stored as text: they are sorted like words, so 10 appears before 2.
• Leading spaces: “ Customer A” isn’t grouped with “Customer A” and appears in a different position.
• Forgetting an active filter: the funnel icon on the header means some rows are hidden. Many “totals that don’t add up” come from this.

If your list carries blank rows, you can clean it first with our free tool to delete empty rows. And if filtering reveals repeated records, remember that the Unique records only option merely hides them: to remove them from the file for good, use the tool to remove duplicates and filter again on a clean list.

Conclusion

For quick lookups, AutoFilter is enough. When you need to combine criteria, multi-level sorting and Advanced Filter cover almost everything, and in Excel 365 or 2021 the FILTER, SORT and UNIQUE functions give you results that update themselves. Prepare your data properly, check for active filters before drawing conclusions, and you will find what you’re looking for in seconds.

Frequently asked questions