Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To create a list that updates when its source data or filter criterion changes, enter one dynamic-array formula in a clear worksheet cell: =SORT(FILTER(A2:D100,C2:C100=H1,""),4,-1). It returns rows where column C matches the value in H1, then sorts those rows by the fourth column of the returned array, descending. Change the ranges, condition, sort column, or direction to fit your workbook.
Build a filtered, sorted list with one formula
Microsoft describes FILTER as a function that returns data from a range based on criteria you define. Nesting it inside SORT applies the sort to the matching rows:
=SORT(FILTER(A2:D100,C2:C100=H1,""),4,-1)In this example, A2:D100 is the data to return, C2:C100=H1 tests each row against the criterion in H1, and "" supplies a blank result when nothing matches. The outer function sorts the filtered rows using column 4 of the returned array, in descending order. The index is relative to the returned array: because this example starts at A, its fourth column is D. Microsoft’s FILTER function documentation demonstrates this nesting pattern.
Adapt the filter criteria
Match one condition
Use a comparison that produces a TRUE or FALSE value for each row. For example, =FILTER(A5:D20,C5:C20=H2,"") returns rows from A5:D20 whose column C value equals H2.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Require all conditions (AND)
Multiply the tests to return rows only when both are true:
=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")Accept either condition (OR)
Add the tests to return rows when either is true:
=FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),"")These multiplication and addition patterns for multiple criteria are shown in Microsoft’s FILTER documentation.
Choose the sort column and direction
SORT(array,[sort_index],[sort_order],[by_col]) sorts an array of the same shape. Its default sort order is ascending; use -1 for descending. In SORT(FILTER(...),4,-1), the 4 identifies the fourth column in the filtered result, not necessarily worksheet column D. See Microsoft’s SORT function documentation.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Use SORTBY when the sort range should remain explicit
If column insertions or deletions could change which field you intend to sort by, consider SORTBY instead. It sorts using a corresponding range rather than an index within the array, and Microsoft notes that this makes it more flexible for grid data when columns are added or deleted. The matching example is:
=SORTBY(FILTER(A2:D100,C2:C100=H1,""),D2:D100,-1)Here the matching rows are sorted by the corresponding values in D2:D100, descending. Keep the sort range aligned with the source rows and the filter condition. See Microsoft’s SORTBY function documentation.
Keep the list live as data changes
Allow room for the spilled results
Dynamic-array formulas spill results from the formula cell into neighboring cells. Enter the formula in the top-left cell where the output should begin and keep the expected spill area clear; occupied cells in that area can produce #SPILL!. Microsoft explains spill behavior and troubleshooting in its dynamic-array formulas and spilled-array behavior documentation.
Rank #4
Use an Excel table for growing source data
If records are stored in an Excel table, structured references can make the source range adjust as rows are added or removed. Put the formula outside the table: Excel does not support spilled array formulas inside tables. The same Microsoft spill behavior documentation covers this limitation.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsTroubleshoot empty results and errors
- No matching rows: The third
FILTERargument, such as"", defines what to return when there are no matches. If you omit it, a no-match result can produce#CALC!because Excel does not support empty arrays. See the FILTER documentation. - An error in the include test:
FILTERreturns an error if its include array contains an error or cannot be converted to Boolean. Check the criteria ranges and the values they evaluate. #SPILL!: Check for content blocking cells where the result needs to spill and clear the obstructed cells.#REF!from another workbook: Dynamic arrays linked across workbooks have limited support and are supported only while both workbooks are open. Closing the source workbook can cause#REF!when the formula refreshes.
Check Excel compatibility before sharing
Microsoft lists FILTER and SORT for Microsoft 365, Excel 2024, and Excel 2021 across the desktop, Mac, and mobile editions covered on its function pages. Confirm the recipient’s Excel version before sharing: older versions without dynamic-array support will not provide the same spill behavior. Microsoft says dynamic arrays were introduced in September 2018 and released to Microsoft 365 subscribers in the Current Channel in January 2020. See the FILTER function documentation, SORT function documentation, and dynamic-array documentation for version details.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
