Power Query: Find and Highlight Duplicates

Power Query can identify duplicate records, but it does not provide a cell-highlighting or conditional-formatting tool inside the Power Query Editor. To flag duplicates, group the data by the columns that define a duplicate, count the rows in each group, and merge those counts back into the original query; after loading the result to Excel, use the count column to highlight matching rows.

What “duplicate” means in Power Query

A duplicate is not always a row that is identical across every column. If a customer table has the same email address on two records, you might consider the email a duplicate even if the names or addresses differ. In a sales table, you might define a duplicate using the combination of order number, product code, and date. The columns you choose are the duplicate key, and that choice determines what Power Query will count.

There is also a difference between finding duplicates and removing them. Finding them helps you inspect, flag, or report on repeated values. Removing duplicates changes the result by keeping one occurrence and discarding the others. Before choosing a method, decide whether you need to review every repeated row, keep one record, or simply identify which key values occur more than once.

Find duplicates with Group By

For a clear duplicate list, group a query by its key columns and count the rows in each group. In Excel for Microsoft 365 on Windows, select the relevant query and choose Home > Transform Data to open the Power Query Editor. In the editor, select the key column or columns, then choose Home > Group By. If the key uses more than one column, choose the Advanced option in the Group By dialog so you can add each column.

Set the group-by columns to the fields that define a duplicate, such as Email or Order ID and Product Code.
Enter a name for the new column, such as DuplicateCount.
Set Operation to Count Rows, then select OK.
Filter DuplicateCount to values greater than 1.

The filtered result contains the repeated keys and the number of source rows for each key. For example, if the grouping fields are Order ID and Product Code, a result with 1047, A12, and a count of 3 means that combination appears three times. Grouping only by Order ID would answer a different question: whether an order number appears more than once, regardless of product.

This grouped query is useful as an audit list, but it no longer has every original row or every non-key field. To flag the original records, use the grouped counts as a lookup and merge them back into the source query.

Flag the original rows by merging counts

Keep a copy of the ungrouped query, then create a second query that groups by the duplicate key and counts rows. Return to the original query and choose Home > Merge Queries. In the merge dialog, select the grouped query as the second table, select the matching key columns in both previews in the same order, and use a Left Outer join. This keeps every original row and attaches the count where a key matches.

After the merge, expand the new table column and select only DuplicateCount. Each original row then has the count for its key. A count greater than 1 marks a duplicate group; a count of 1 means that key appears once. This method flags all occurrences, including the first one, rather than marking only the second and later copies. If you want to label only later occurrences, that requires a separate rule for which record to retain or mark.

For example, suppose the original table contains three rows for order 1047 and product A12. The grouped query reports a count of 3 for that key. Merging it back places 3 beside all three matching rows. You can filter the count column to greater than 1 to inspect only repeated records, or keep the full result so the flag remains available to downstream reports.

Highlight duplicates after loading to Excel

Power Query creates and transforms data; Excel provides the worksheet formatting. Once the flagged query is ready, choose Home > Close & Load, or use Close & Load To to choose where the result goes. Load it to a worksheet table if you want to inspect or format the rows. You can then apply Excel conditional formatting to the loaded output.

If the duplicate count is in a column by itself, select that column and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter 1 and choose a format. Excel will emphasize counts above 1. This highlights the count cells, not necessarily every field in the corresponding row.

To color entire rows, use a formula-based conditional formatting rule. For a worksheet where the data occupies A2:D100, the duplicate count is in column D, and row 1 contains headers, select A2:D100 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With that layout, enter =$D2>1, choose a format, and confirm. The dollar sign fixes the count column while the row reference changes for each row. Adjust the range and column reference to match your worksheet. On Mac, the conditional-formatting commands are also available from the Home tab, though dialog layouts can differ slightly from Windows.

Excel also has a built-in Duplicate Values rule at Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. It works well when you want to highlight repeated values within a selected range. However, it evaluates the selected cell values; it does not automatically treat a combination of separate columns as one composite key. The merged count method is more dependable when a duplicate is defined by several fields.

Choose the right columns and prepare the data

The most important decision is which fields form the key. Selecting too few columns can label valid records as duplicates; selecting too many can conceal repeats because a small difference makes each full row unique. For example, if the goal is to find repeated email addresses, grouping by email alone is appropriate. Grouping by email, name, and phone would miss records where the email repeats but another field differs.

Check the values before grouping. Differences that look insignificant to a person can affect whether records match, while inconsistent formatting can make a useful comparison harder to interpret. Consider standardizing the key columns first when it matches the business rule:

Use Transform > Format > Trim to remove leading and trailing spaces from text fields.
Use Transform > Format > Clean to remove non-printing characters from text fields.
Choose a consistent case, such as lowercase, if the comparison should ignore capitalization.
Set consistent data types, especially for dates, numbers, and identifiers that may otherwise be read as text.

Do not normalize automatically without considering meaning. A product code or account identifier may be case-sensitive in a particular source system. Likewise, converting an identifier such as 00125 to a number would discard its leading zeros. Decide how blanks and null values should be treated, too: repeated missing values may form a group, but that may not mean the records represent the same real-world entity.

Find versus remove duplicates

If you only need a de-duplicated result, select the relevant key columns and choose Home > Remove Rows > Remove Duplicates. Power Query keeps one row for each distinct set of selected values. Selecting every column removes only rows that match across all those columns; selecting just Email removes rows that share an email even when other fields differ.

Removing duplicates is not the same as choosing the best record. If repeated customer records have different addresses or update dates, the command does not decide which one is authoritative. Sort or otherwise define a retention rule before deduplicating when the surviving row matters. Also, do not rely on the command to preserve a particular occurrence: Power Query may optimize evaluations, and the retained row is not a substitute for an explicit rule about which record should win.

For investigations, retain the source rows and add a count or flag instead. That makes the repeated records visible for review and avoids losing information before someone has decided whether the records are truly redundant.

Refresh behavior and common problems

A Power Query result is recalculated when its source is refreshed. If the source changes, the grouped counts and merged flags can change too. Excel conditional formatting applies to the worksheet output, while Power Query supplies the refreshed values. Check the applied formatting range after changing the output location or structure, especially if new columns are added or the result is not loaded as a table.

A merge that returns blank counts usually points to a mismatch between the join keys. Check that the same columns were selected in the same order, that their data types agree, and that the values do not differ because of spaces or inconsistent formatting. If the merge produces more than one count column or unexpected rows, inspect the grouped query and confirm that the key contains exactly the intended fields.

For large datasets, grouping and merging can take time, particularly when the source is not well suited to query folding. Keep only necessary columns where practical, and avoid adding expensive row-by-row operations when a Group By and merge can express the task. If a query uses an external data source, some transformations may be processed by that source and others locally; performance depends on the connector and the transformations involved.

Frequently Asked Questions

Can Power Query highlight duplicate cells directly?
No. The Power Query Editor has tools to group, count, filter, and remove duplicates, but it does not apply worksheet-style cell colors. Load a duplicate flag or count to Excel and apply conditional formatting there.

How do I highlight an entire row when a duplicate exists?
Merge a grouped count back into the original query, load the result to a worksheet, then create a formula-based conditional formatting rule that checks whether the count column is greater than 1. Use a rule such as =$D2>1 only when the count is in column D and the selected range begins on row 2.

Can I check duplicates based on multiple columns?
Yes. In Group By, choose Advanced and add every field that defines the key. Use the same fields in the merge back to the original query. The combination, rather than any one field alone, determines whether a record is repeated.

Does Remove Duplicates keep the first row?
It keeps one row for each selected key, but you should not depend on it to preserve a particular source occurrence. If a specific record must survive, define and apply a retention rule before removing duplicates.

Will the duplicate flags update when I refresh?
Yes, the query recalculates its grouped counts and merged results when refreshed. The worksheet formatting is separate, so verify that the conditional-formatting rule still covers the output range after changes to the query or its layout.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.