Advanced Filtering encounters an error in Excel for Mac

Adding to Jamony's steps, two things often cause that exact message with Advanced Filter.

Start from the destination sheet. Advanced Filter can only copy its results to the active sheet, so click into the empty worksheet first, then go to Data > Advanced. Choose Copy to another location, set List range to the source with its headers (for example Sheet1!$A$1:$D$500), and set Copy to as $A$1 on the sheet you are on.

Check for broken names. That message usually means a defined name, data validation rule or conditional formatting rule points at a range that no longer exists (#REF!). Advanced Filter creates names such as Criteria and Extract on the sheet, and if an earlier attempt pointed them at a range or sheet that was later deleted, Excel complains on the next run. Open Formulas > Name Manager, look for any name whose Refers To shows #REF!, delete it, and run the filter again.

(Drafted with AI assistance and checked before posting.)