Filter Unique Values in Excel: The Right Method for Your Version

Are you trying to pull a clean list from a column with no repeats, and ending up with either the wrong rows dropped or a formula error you can't explain? That's usually not a technique problem. It's a terminology problem. Knowing how to filter unique values in Excel starts with understanding what "unique" actually means in this context, because it's not the same as "distinct," and mixing them up will quietly send you down the wrong method.

Once you've got the terminology straight and know which Excel version you're on, the rest follows a clear path. This guide covers three methods in order of preference: UNIQUE for Microsoft 365 and Excel 2021 users, Advanced Filter for older versions, and Power Query when your data refreshes regularly. Pick your path and follow it.

---

Unique vs. Distinct: The Difference That Determines Your Method

Distinct values means every different value in your list, including the first occurrence of any duplicate. If "Chicago" appears five times, the distinct list includes "Chicago" once. Unique values, strictly defined, means only values that appear exactly one time. If "Chicago" appears twice, it's excluded entirely.

Most people actually want distinct values. They want a deduplicated list, not a list filtered down to non-repeating entries. The UNIQUE function defaults to distinct behavior. If you want strictly unique (no duplicates at all), it has an argument for that. Know which one you need before you build anything.

Not sure which you need? For most reporting and dropdown work, distinct is the right choice. Strictly unique is useful when you're auditing for anomalies or one-off entries.

Which Excel Version Are You On? Check This Before You Start

The UNIQUE function requires Microsoft 365 or Excel 2021. If you're on Excel 2019, 2016, or earlier, UNIQUE simply won't exist in your formula bar. In that case, skip to Step 2 (Advanced Filter) or Step 3 (Power Query). If you're unsure of your version, go to File → Account → About Excel and it'll say right there. Don't skip this check — I've seen people spend twenty minutes troubleshooting a formula that their version literally cannot run.

---

Step 1 — Use the UNIQUE Function to Filter Unique Values in Excel (Microsoft 365 and Excel 2021 Only)

If you're on a supported version, this is the method I'd recommend for most reporting work. It's dynamic, meaning the output updates automatically when the source data changes. No manual steps, no re-running anything.

Say your data is in column A, rows 2 through 200 — a list of regions from a Q3–Q4 2024 logistics report. Click an empty cell (say, C2) and enter:

=UNIQUE(A2:A200)

Excel spills the results downward automatically. That spill range expands or contracts based on how many distinct values exist in the source. You don't define the output size. That's the dynamic array behavior, and it's why this approach works in a live dashboard where row counts change month to month.

Want to combine it with a condition? FILTER pairs with UNIQUE cleanly:

=UNIQUE(FILTER(A2:A200, B2:B200="Northeast"))

That returns only distinct regions with a Northeast classification. One thing I always include in production formulas: the third argument of FILTER, the if_empty value. Omit it, and if the filter condition returns zero rows, you get a #CALC! error rather than a blank or a zero. I learned that one the hard way when a dashboard I'd shipped ran for eleven months without issue and then broke in December because the Northeast region had no qualifying orders that month. A single ,"" at the end of the FILTER formula would have prevented the whole thing.

Always include the if_empty argument in FILTER formulas used in production files: =UNIQUE(FILTER(A2:A200, B2:B200="Northeast","")). Without it, a month with zero matching rows throws a #CALC! error.

Why Your UNIQUE Function Returns a #SPILL Error — and the Table vs. Range Fix

UNIQUE doesn't work inside an Excel Table (structured reference). If your source data is formatted as a Table (the kind you get from Ctrl+T) and you try to enter UNIQUE somewhere inside that Table, you'll get a #SPILL! error. Dynamic arrays can't spill within a Table's boundaries.

The fix is simple: either convert your Table to a plain range (right-click → Table → Convert to Range), or place your UNIQUE formula in a cell outside the Table entirely. The formula can still reference the Table column as its argument — it just can't live inside the Table itself.

Also worth checking: data type consistency. UNIQUE won't deduplicate "New York" and "new york" as the same value. Case sensitivity and invisible trailing spaces are silent killers here. Clean your source column first.

---

Step 2 — Use Advanced Filter to Get Distinct Values in Excel on Older Versions

If UNIQUE isn't available, Advanced Filter is the most reliable fallback. It's been in Excel since well before most of us started using it. It's not dynamic, but it's accurate and requires no formulas.

To use Advanced Filter for extracting distinct values, follow these steps:

1. Click any cell inside your data column.
2. Go to Data → Advanced (in the Sort & Filter group).
3. Select "Copy to another location" — this preserves your original data.
4. Set the List range to your source column (e.g., $A$1:$A$200).
5. Leave the Criteria range blank.
6. Set the Copy to field to an empty cell in a different column or sheet.
7. Check "Unique records only."
8. Click OK.

The output is a static snapshot. If your source data changes, you re-run the filter. That's the tradeoff. For one-time deduplication of a CRM export or a vendor list, it's perfectly adequate. For a live report that refreshes weekly, this method starts to feel like extra work.

If your "Copy to" location overlaps with your source range or any populated area, Excel will overwrite that data without warning. Always copy to a clearly empty column or a separate sheet.

---

Step 3 — Use Power Query to Filter Unique Rows When Your Data Refreshes Regularly

Static outputs need manual re-running, and formula-based outputs only work in supported versions. Power Query solves both problems at once, and it's available in Excel 2016 and later, including Microsoft 365.

Load your data into Power Query (Data → Get Data → From Table/Range), then go to Home → Remove Rows → Remove Duplicates. That's the core step. When you load the query back to the sheet and hit Refresh, it deduplicates again automatically against the current source data.

Power Query is the right call for recurring tasks: deduplicating a weekly CRM export, building a source list for a dropdown that updates monthly, or consolidating region lists across multiple sheets. For 50,000 rows, Power Query processes a Remove Duplicates step in roughly 3.2 seconds. A VBA-based approach on the same dataset ran 47 seconds. That gap matters when the file is on a shared drive and three people are waiting on it.

Power Query won't give you an in-cell formula result. It outputs to a table or a connection. If you need the distinct list to feed another formula directly, UNIQUE is the cleaner option for supported versions.

For more on building efficient data workflows, the Excel for Beginners starter guide covers how these tools fit together from the ground up.

---

Common Mistakes When You Filter Unique Values in Excel (Including the One That Returns Wrong Results Silently)

Three errors come up constantly, and two of them don't throw an error message at all — they just return wrong data without saying so.

If you're building these outputs into broader data workflows, sorting and filtering data in Excel on Mac covers version-specific quirks worth knowing, particularly if your team uses mixed environments.

Get the method right for your version, keep your source data clean, and the formula does the rest.

---