digest
Short Summary
Main recommendation: To filter a table and remove rows containing any of several listed conditions, use Excel's Advanced Filter with a "does NOT contain" criteria block built with the NOT operator and wildcard asterisks, rather than Power Query, when the task is one-time and you are not importing data. The video demonstrates this approach and then shows how to extend it with a helper-style formula evaluated in memory.
Key Points
- Advanced Filter is the recommended tool for a one-off filter (not a repeatable import) because you do not need to bring data in through Power Query.
- The criteria area must match the table exactly: the column header (Product Name) is repeated, and the criterion is written as NOT followed by the text wrapped in asterisks, such as NOT A.
- The asterisk is a wildcard meaning zero or more characters, so A finds any value containing "A," which is why the NOT criterion excludes all matching rows.
- Advanced Filter reads criteria from the criteria area and applies them to the table area, so headers and text must be spelled identically in both.
- Advanced Filter stores named ranges for the criteria and extract ranges, so repeat filters can be run from the keyboard shortcut Alt, A, Q with the stored settings.
- A formula-based criterion can also be used directly inside Advanced Filter, which evaluates it in memory and avoids a visible helper column.
Details Worth Noting
- Concrete instructions:
- Copy the column header (Product Name) and paste it into the criteria area exactly as it appears in the table.
- Below the header, type NOT followed by the search text wrapped in asterisks (for example, NOT A). Make sure the number of characters in the criteria matches the source text.
- If you want to avoid typing the criterion by hand, create it with a formula, then right-click and choose Paste Values so it is stored as text.
- Select one cell in the data table, go to Data > Advanced, confirm the list range is correct, and choose Copy to Another Location.
- Set the copy-to location and the criteria range, select the option to copy all columns, then click OK to extract rows that do not contain the criterion.
- To add another exclusion, insert another column in the criteria area with its own NOT criterion, then rerun the filter with Alt, A, Q.
- For a formula-based filter, enter the formula in the criteria area with an empty cell above it, set the extract-to range separately, and click OK. Advanced Filter evaluates the formula for each row in memory and extracts the rows that return TRUE.
- Advanced Filter creates defined names for the criteria and extract ranges; the video shows these in Name Manager (Control-F3).
- When you rerun with Alt, A, Q, you must still select Copy to Another Location, but the stored criteria and extract range are reused.
Bottom Line
Advanced Filter with NOT-plus-wildcard criteria is a fast, no-helper-column way to exclude rows matching several conditions in a one-time Excel task, and its stored named ranges make repeat runs quick.
