digest
Short Summary
Main recommendation: To filter rows and columns in a large GDP dataset (over 3,000 rows and 46 columns), use the dynamic array FILTER function in Microsoft 365, combining SEARCH, ISNUMBER, and XMATCH; for any version of Excel, use Advanced Filter with a helper-column formula using AND, SEARCH, and ISNUMBER. The video frames the task as extracting only GDP indicator rows, a list of specified countries, and only the 2000 to 2010 columns.
Key Points
- The FILTER approach handles both criteria at once: rows where Indicator Name equals "GDP" and rows whose Country name appears in the country list.
- Country names sit together in one cell, so SEARCH is run across the whole country list as an array, and ISNUMBER converts the found positions into TRUE/FALSE tests.
- Column filtering is done by a second FILTER, using XMATCH to look up each of the 46 field names, with the 2000 to 2010 columns selected.
- The legacy solution uses a helper column with the AND-style test (multiplying conditions) and then Advanced Filter, which runs the formula down the table in memory without needing a dynamic array version.
- Advanced Filter needs a criteria range whose top cell is not a field name, and an output range listing the desired column names.
- The presenter says the Advanced Filter method seems easier than the FILTER formula and asks viewers which they prefer.
Details Worth Noting
- Concrete instructions:
- Paste the field names "Country" and the years 2000 through 2010 in a header row above the output area.
- In the top-left output cell, enter FILTER with the full table as the array. Include a first condition that the Indicator Name column equals "GDP", and a second condition using ISNUMBER(SEARCH(country list, single country cell)) to match the country list. Combine the two conditions by multiplying them, since both must be true.
- Edit the formula so the column-level filter uses a second FILTER, with the lookup value set to the full 46-field name row and XMATCH returning positions; wrap the result in ISNUMBER so it produces TRUE for the needed columns.
- For the older method, add a helper column next to the table with a formula that tests whether the row is GDP and whether the country is in the list, copy it down, then open Data > Advanced Filter, set the table as the list range, the helper formula cell as the criteria range, and the field-name row as the copy-to location.
- Apply custom number formatting (a comma followed by two more commas) to display large values in millions.
- The data set has more than 3,000 rows and 46 columns, with many empty rows; the presenter uses Ctrl+Shift+Arrow to find the full data extent.
- The dynamic array formula spills results automatically; only the top-left cell holds the actual formula.
- Advanced Filter stores its criteria and extract ranges as named ranges (checked via Name Manager), so it can be rerun after the source data changes.
Bottom Line
The FILTER function gives a fast, formula-based result in modern Excel, while the helper-column plus Advanced Filter method works in any version and updates when rerun after data changes.
