Advanced Filtering encounters an error in Excel for Mac

When Advanced Filter copies results to another worksheet, start from the destination sheet, not the source sheet.

1. Open the empty destination sheet and select A1.
2. Choose Data > Advanced Filter, then Copy to another location.
3. For List range, select the source data, including its column headers.
4. Leave Criteria range blank, set Copy to to the destination’s A1, and check Unique records only.
5. Click OK.

Select the ranges using the range-picker buttons to avoid mistyped sheet references. Each source column should have a nonblank, distinct header.

The message in your screenshot is a general reference error, so it doesn’t identify the exact cause. If the steps above still fail, try the same operation with a small sample in a new workbook. If that works, check the original workbook’s defined names for #REF! errors under Formulas > Name Manager.

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.)

To extract unique rows onto another worksheet, first separate the filtering operation from copying its result. The exact error text and Excel version are missing, so it would be premature to diagnose a macOS compatibility bug.

Work on a copy of the workbook. Select the source range with its column headings, choose Data, Advanced, and select Unique records only. For an initial test, copy the results to an empty area of the same worksheet, then copy that result to your destination sheet. This avoids complications from selecting an output area on another sheet.

If your Excel version supports dynamic arrays, enter =UNIQUE(SourceSheet!A2:D100) in an empty destination cell, adjusting the range and sheet name. Leave room for the results to spill and copy/paste values if a fixed snapshot is required.

If either method fails, provide the exact error, Excel’s About version, source headers, and destination setup, with confidential data removed.