Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Use Excel’s FILTER function to show matching rows in a separate, automatically updating list. Put your criteria in a cell, such as H2, and the formula recalculates when that value changes. Unlike a header filter, which hides rows in the original table, a formula-generated list can sit beside a report or feed another formula.
Build a live list from an Excel table
Start with a structured data table. For example, name the table Sales and give it columns such as Region, Product and Units. Enter the region you want to see in H2, then place the formula in a blank worksheet cell outside the table:
As an Amazon Associate I earn from qualifying purchases.
=FILTER(Sales,Sales[Region]=H2,"")
This returns the rows whose Region matches the value in H2. Change the selection in H2 to recalculate the list. Replace Sales or the column heading with the actual table name and heading in your workbook. Microsoft describes FILTER as returning data that meets criteria you define.
Recommended Free Tools
Use the no-match argument
The third argument, "", is the optional if_empty fallback. It displays a blank result when no rows match. Without a fallback, a no-match result can return #CALC! because Excel does not currently support an empty array.
#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
For a fixed range
If your data is an ordinary range rather than a table, a basic example is =FILTER(A5:D20,C5:C20=H2,""). It returns rows from A5:D20 where the corresponding cells in C5:C20 equal H2. For a changing dataset, a table with structured references is usually more maintainable: its references adjust as table rows are added or removed. See Microsoft’s overview of Excel tables.
Make the result distinct or sort it
Return a sorted list of unique values
To display each region once, in order, use:
=SORT(UNIQUE(Sales[Region]))
UNIQUE removes duplicates; SORT orders the resulting list. See Microsoft’s pages for UNIQUE and SORT.
Sort matching rows by another column
To show the matching sales records with the largest unit counts first, you can use:
=SORTBY(FILTER(Sales,Sales[Region]=H2,""),Sales[Units],-1)
Here, -1 requests descending order. The sort array must correspond in size and row alignment to the rows returned by FILTER. If the include array contains an error or a value Excel cannot convert to TRUE or FALSE, the formula can return an error. Microsoft documents SORTBY and FILTER.
Keep the spill area clear
Dynamic-array formulas return results that spill from the formula cell into adjacent cells. Enter the formula in the top-left cell of the intended output area, then leave enough room for the result. If other content blocks the required cells, Excel cannot display the complete result. Spill formulas are not supported inside Excel tables, so put the formula in the worksheet grid outside the source table. Microsoft explains these behaviors in its guide to dynamic arrays and spilled array behavior.
Rank #4
Dynamic arrays linked between workbooks also have a limitation: Microsoft says the linked arrays are supported only while both workbooks are open. Closing the source workbook can cause #REF! when the formula refreshes.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsChoose between FILTER and header buttons
| What you need | Use a formula-generated list | Use a table or range filter |
|---|---|---|
| Where results appear | A separate spill range in the worksheet grid. | Nonmatching rows are hidden in the original range or table. |
| What happens when criteria change | A cell-driven formula recalculates its result. | You change the filter in the header dropdown; Microsoft notes a filter may need to be reapplied to show updated data. |
| Using results elsewhere | The returned array can serve as input to another formula or report. | The filter changes the in-place view; it does not create a separate formula output list. |
| Practical constraint | Requires a supported dynamic-array function and a clear spill area. | The filter window displays only the first 10,000 unique entries, which can affect finding a value in its dropdown. |
Header filters remain useful when you want to inspect a subset in place or temporarily hide rows. Microsoft explains how to apply and clear them in its guide to filtering data in a range or table. Choose FILTER when you need a distinct output area that can update from a selected criterion or be used elsewhere.
Check Excel compatibility
Microsoft’s function pages list Excel for Microsoft 365, Excel 2024 and Excel 2021 among the supported products for these functions; platform coverage varies by function page. If a formula is not recognized in your edition, check the relevant Microsoft function page before relying on it. Older or other editions may not support the dynamic-array functions used here.
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.
