DAX Formula in Excel to Remove Duplicates: What Works

Formulas & functions · Updated November 18, 2026

There is no DAX formula in an Excel worksheet that deletes duplicate rows. For a live list of distinct values in Microsoft 365 or Excel 2021, use =UNIQUE(A2:A100); to remove duplicates from a table permanently, use Data > Remove Duplicates or Power Query. DAX is for calculations in an Excel Data Model or Power BI, not for editing worksheet rows.

Why DAX and Excel formulas are different

Excel worksheet formulas calculate results in cells. DAX, or Data Analysis Expressions, is a separate formula language used with data models, including Power Pivot in supported Windows editions of Excel and Power BI. DAX measures and calculated columns analyze model data; they do not directly rewrite a worksheet range or delete records from the source.

That distinction explains why searching for a DAX remove-duplicates formula can lead to confusing results. DAX has a DISTINCT function that returns a table expression, and it has a DISTINCTCOUNT function that returns a number. Neither one is a command to remove repeated worksheet rows. In Power BI, a distinct table expression can be used when creating a calculated table. In Excel, the practical choices for a distinct worksheet list or cleaned source data are Excel formulas, the Remove Duplicates command, or Power Query.

Choose based on the result you need: a formula-generated list that updates, a one-time cleanup of the current range, or a repeatable transformation that runs again when refreshed.

Use UNIQUE for a distinct list that updates

With values in A2:A8, enter =UNIQUE(A2:A8) in an empty cell outside the source list. If the cells contain Ana, Ben, Ana, Mei, Ben, Omar, Omar, the formula spills four values: Ana, Ben, Mei, and Omar. The original cells remain untouched, while the output updates when the source values change.

UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Make sure the cells below the formula are empty: an existing value in the spill area produces a #SPILL! error. If your Excel installation uses a localized function name or a different list separator, its regional settings may affect how you enter formulas; the examples here use English function names and comma separators.

By default, UNIQUE returns each value that occurs at least once. It does not sort the results; the order is based on first appearances in the source. If you want an alphabetically sorted distinct list, combine it with SORT. With the same values in A2:A8, use =SORT(UNIQUE(A2:A8)); the result is Ana, Ben, Mei, Omar in alphabetical order.

The function can also return unique rows. With records in A2:C6, enter =UNIQUE(A2:C6) in an empty area to return one copy of each distinct complete row. Two rows count as duplicates only when all three corresponding values match. This matters when a table has a name, city, and account number: a repeated name alone will not make the whole row a duplicate.

Decide what counts as a duplicate

Before cleaning data, define which columns establish that two records are the same. A repeated email address may indicate a duplicate contact even if the person’s name or phone number differs. By contrast, a repeated full row may be safe to remove only when every relevant field matches. The right rule depends on what the data represents, not just on which values look alike.

  • Distinct values in one column: Use UNIQUE on that column, such as customer IDs.
  • Duplicate complete records: Apply the tool to the full table or use UNIQUE on the full range.
  • Duplicates defined by selected fields: Select those fields in Remove Duplicates or Power Query, while retaining the other columns from the chosen record.

For example, suppose a customer table has Customer ID, Name, and Region. If the same ID appears twice with different regions, using all columns treats those entries as different rows. Deduplicating on Customer ID alone treats them as duplicates. A cleanup based on one field keeps a particular record and discards the other, so check which record should survive before applying it.

Remove duplicates from the source table

Use Excel’s built-in command when you want to delete repeated entries from the selected data rather than create a separate result. On Excel for Microsoft 365 for Windows, select a cell in the range or table, then choose Data > Remove Duplicates. In the dialog, check My data has headers if the first row contains column names, choose the columns that define a duplicate, and select OK. Excel reports how many duplicate values were removed and how many unique values remain.

  1. Save a copy or make sure the workbook can be restored if the wrong columns are selected.
  2. Click inside the data and open Data > Remove Duplicates.
  3. Confirm the header setting, then select only the columns that define duplicate records.
  4. Review Excel’s removal and remaining-row counts, then inspect the table.

The command keeps the first occurrence it encounters and removes later matching entries; it does not merge information from the removed rows. If the table is sorted, the row that appears first after sorting may be the one retained. Sort deliberately if a particular record should take priority, and preserve a backup because this command changes the selected data rather than producing a linked output.

On Excel for Mac, a Remove Duplicates command is also available from the Data tab, though ribbon placement can differ by release. Excel for the web has data-cleaning options that vary with the current interface and account. If a command is not available in your version, use a supported desktop version or create a distinct output with UNIQUE where that function is supported.

Use Power Query for repeatable cleanup

Power Query is a good choice when you import data regularly and want the same deduplication rule applied again after refresh. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range. In the Power Query Editor, select the column or columns that define duplicates, then choose Home > Remove Rows > Remove Duplicates. Select Close & Load to return the transformed result to Excel.

Removing duplicates in Power Query affects the query output, not the original source file. When the source changes, refresh the query to apply the steps again. If you select one column before removing duplicates, Power Query uses that selection to determine which rows are duplicates; choosing the full set of columns instead compares complete rows. The order of operations can affect which values are retained, so sort or otherwise prepare the query with the intended rule in mind.

Power Query is useful for recurring imports, large cleanup workflows, and steps you want to document. It is less immediate than the one-click worksheet command, and a query result will not behave like a manually edited source table. On Mac and in Excel for the web, Power Query features and editing support can differ from Windows desktop Excel, so check the commands in the version you use.

Where DAX fits in an Excel Data Model

DAX is useful when the question is about analysis rather than deleting data. For example, a PivotTable measure can report how many distinct customers are represented in a model, while leaving all sales rows available for other calculations. DAX measures respond to filters and report context, so the distinct count can change when a user filters a date, region, or product.

For a DAX example, assume the Excel Data Model contains a Sales table with a CustomerID column. Create a measure using =DISTINCTCOUNT(Sales[CustomerID]). If the model has customer IDs 101, 102, 101, and 103 in the current filter context, the measure returns 3. It counts distinct values in that context; it does not remove the repeated sales records. DAX is available through Power Pivot in supported Windows editions of Excel, and its availability depends on the Excel edition and setup. Excel for Mac does not provide the Power Pivot authoring experience available in Windows.

In DAX, DISTINCT returns a table expression, not a worksheet spill range or a delete operation. DISTINCTCOUNT returns a number, not a cleaned table. Use a distinct table expression in a suitable model-table context in Power BI, or use Excel’s worksheet and query tools when the goal is to produce or clean rows in an Excel workbook.

Common problems and checks

  • UNIQUE shows an error: Check whether the output cells are blocked by existing content. Clear the spill area or move the formula to a larger empty area.
  • Rows that look the same are not treated as duplicates: Check for spaces, different punctuation, nonprinting characters, or genuinely different values in other columns. Excel compares cell contents, not just what appears visually.
  • A name-only duplicate check removes a useful record: Decide whether the name or another field is a reliable identifier. Names may be shared by different people, while a customer ID may distinguish them.
  • The cleaned list changes order: UNIQUE preserves first-appearance order unless you wrap it in SORT. The Remove Duplicates command retains the first matching row in the current data order.
  • A DAX measure seems to count more or fewer records than expected: Check the active filters and whether blank IDs should count. DAX measures calculate in the current model context, which may differ from the entire worksheet.

For a quick audit before deleting anything, add a temporary helper column. With names in A2:A8, enter =COUNTIF($A$2:A2,A2)=1 in B2 and fill it down. For the sample list Ana, Ben, Ana, Mei, Ben, Omar, Omar, the formula returns TRUE for the first Ana, first Ben, Mei, and first Omar, and FALSE for later repeats. This flags first appearances; it does not delete rows. For case-sensitive matching or complicated cleanup rules, define the comparison carefully rather than assuming the default duplicate tools meet every requirement.

Frequently Asked Questions

Can I use DAX directly in an Excel cell?
No. Worksheet cells use Excel formulas such as UNIQUE. DAX is used in a data model, such as a Power Pivot measure in supported Windows Excel editions, and does not spill a distinct list into ordinary worksheet cells.

What is the Excel formula to remove duplicate values?
With values in A2:A100, use =UNIQUE(A2:A100) in an empty cell to create a distinct output list. It leaves the source range intact. Use Data > Remove Duplicates if you want to change the existing range.

How do I remove duplicate rows based on just one column?
In Data > Remove Duplicates, select only the column that defines a duplicate. Excel keeps the first matching row and removes later ones, including any differing details in those removed rows. Back up the data and confirm that this is the intended rule first.

Does UNIQUE remove duplicates from the original data?
No. It returns a separate dynamic-array result that updates from its source. To physically clean the source, use Remove Duplicates, or load a Power Query result and use that as the cleaned dataset.

Does DAX DISTINCT remove duplicate rows?
DISTINCT returns a distinct table expression in a suitable DAX context; it does not delete source rows. For a distinct count in a model, use DISTINCTCOUNT. For deduplicating Excel worksheet data, use Excel’s own data tools.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.