(Extract) | General Excel Questions & Answers | Excel Forum

Hi Scott,
The advanced filter is creating a defined name "Extract", where the results of the advanced filter will be pasted.
In excel, by default, when a selected range has a defined name, the range address will be automatically replaced with its name.
In defined tables formulas, range references (also known as "explicit references") are also replaced with structured references. Only automatic table references can be disabled from using them in formulas, from File-Options-Formulas-Use Table Names in formulas. For named ranges, the only way to avoid the automatic replacement of  explicit references with defined names is to manually type the range reference, instead of selecting the range.

The advanced filter is actually using 3 defined names: Criteria for the criterias range, Database for the range to be filtered and Extract for the range where the results will be pasted. If you define these 3 names before using the advanced filter, they will automatically show up in your Advanced filter dialog window, in their corresponding fields: Criteria box, List Range and Copy To, you will not need to indicate them again. From what I remember, only the Extract name will be automatically created, not the other 2 names.

Named ranges are far more flexible than explicit references (and the formulas are more "readable"), once you get used to them you will find them very useful.

I know someone that has over 70.000 defined names in a single file 🙂