Remove Duplicates in Excel

The key difference is permanence: 'Remove Duplicates' (Data tab) permanently deletes duplicate rows from your dataset, keeping only the first occurrence of each value. Using Advanced Filter with the 'Unique records only' option temporarily hides duplicates without deleting them. Use Remove Duplicates when you need to clean data permanently, and Advanced Filter when you need to analyze without altering your original dataset or when you need to preserve the ability to see all records later.

To remove duplicates based on combinations of values: 1) Select your data range including headers, 2) Go to Data tab > Remove Duplicates, 3) In the dialog box, check only the columns you want to use for determining duplicates, 4) Click OK. Excel will keep the first occurrence where the combination of selected columns matches, regardless of whether other columns differ. This is useful for scenarios like keeping unique customer-product combinations while allowing different dates or quantities.

To track removed duplicates: 1) Add a helper column with a formula like =COUNTIFS($A$2:$A2,A2)>1 (adjust A to your key column) to flag duplicates, 2) Sort by this column to group duplicates, 3) Review before deletion, or 4) Copy your data to a new sheet before using Remove Duplicates, then use VLOOKUP to identify which rows were removed. Alternatively, use Power Query which can create a 'removed rows' output alongside the deduplicated data, giving you full visibility of what was removed.

Duplicates might be missed due to: 1) Hidden spaces or non-printing characters making seemingly identical cells different (use TRIM() or CLEAN() functions to fix), 2) Text vs. number formatting (e.g., '123' as text vs. 123 as a number), 3) Case sensitivity (Excel's Remove Duplicates is not case-sensitive by default), 4) Formatting differences that don't affect cell values, or 5) Looking at the wrong columns. Always visually inspect 'duplicates' that aren't being removed to identify subtle differences.

Excel's Remove Duplicates keeps the first occurrence by default. To keep the latest: 1) Sort your data by date/timestamp in descending order (newest first), 2) Then use Remove Duplicates, 3) The first occurrence (now your newest entry) will be kept. If you don't have a date column, add a helper column with row numbers or entry sequence, sort by that in descending order, then remove duplicates. This ensures you're keeping the most recent or highest-numbered duplicate entry.

Excel's built-in Remove Duplicates works within a single worksheet. For cross-sheet deduplication: 1) Combine data from multiple sheets using Power Query (Get & Transform in newer versions), 2) In Power Query, use the Remove Duplicates transformation, 3) Load the results to a new sheet. Alternatively, copy and paste all data into a single worksheet, add a column identifying the source sheet, then use Remove Duplicates. For very large datasets across workbooks, consider using Power BI or a database tool for more efficient deduplication.

To highlight without removing: 1) Use Conditional Formatting: select your data range, go to Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values, 2) Choose your formatting style, 3) Click OK. For more control, use a formula-based conditional formatting rule like =COUNTIF($A$2:$A$100,A2)>1 (adjust ranges as needed). You can also use Data > Filter to temporarily view duplicates, or add a helper column with COUNTIFS to count occurrences of each value.

Yes, removing duplicates can impact both: 1) Formulas referencing specific cell positions will reference different data after rows are deleted, 2) Pivot tables need to be refreshed and possibly reconfigured if the source data structure changes. Best practices include: working with a copy of your data, using structured references (Excel Tables) that adjust automatically, refreshing pivot tables after deduplication, and using lookup formulas (VLOOKUP, INDEX/MATCH) that find values rather than relying on absolute positions.