To find duplicate lines (rows) in Excel without deleting anything, select the data rows and create a formula-based conditional formatting rule that checks whether each row’s values appear more than once. For a simple duplicate check in one column, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The steps below show how to check complete rows, highlight matches, and review them safely.
What You’ll Need
- An Excel workbook open in Microsoft Excel for Microsoft 365 on Windows.
- A clearly defined data range, with one record per row and column headings in the first row.
- The column or columns that determine whether two rows count as duplicates. For a complete-row match, include every column that matters.
Save a copy first if the worksheet contains important information or if you plan to sort, filter, or remove records after finding matches. The methods here only highlight or label duplicates; they do not delete rows.
Step-by-Step
- Decide what counts as a duplicate. If a customer list has one row per person, you might consider two rows duplicates only when the name, email, and account number all match. If you are checking whether an email address occurs more than once, only the email column matters. This distinction is important: a repeated value in one column does not necessarily mean the entire line is repeated.
- Check the structure of the data. Make sure each record occupies one row, each field has its own column, and headings are separate from the records. For example, if headers are in A1:C1 and the records run from row 2 through row 100, the data range is A2:C100. Remove any completely empty rows within the list if they make it harder to select the full range. Do not include totals or notes below the list unless you want Excel to check those rows too.
- Select the rows you want to check. For the example above, select A2:C100. Leave the heading row out so Excel does not treat a repeated heading as a duplicate record. If you want to identify duplicates using only one column, select just that column’s data cells, such as B2:B100. Confirm that the selection reaches the last record and does not extend into unrelated data.
- Use Excel’s built-in rule for a single column. With one column selected, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and select OK. Excel highlights values that occur more than once in the selected range. This is quick for finding repeated email addresses, codes, or names, but it does not establish that every value in the corresponding rows is the same.
- Create a rule for exact duplicate rows. A built-in Duplicate Values rule checks individual cells, not entire lines. To compare all three columns in the example, with records in A2:C100 and the first record on row 2, select A2:C100. Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format. Enter this formula:
=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1
The formula counts rows where column A matches the current row’s A value, column B matches its B value, and column C matches its C value. A result greater than 1 means the same combination occurs at least twice. The dollar signs keep the checked ranges and column references in place as Excel evaluates the rest of the selection; the row number in $A2, $B2, and $C2 is allowed to change. - Choose a visible format and apply the rule. In the New Formatting Rule window, select Format, choose a fill color or font style, and confirm the formatting choices. Select OK again to create the rule. All cells in each matching row should take on the chosen format. Check the Applies to range in Conditional Formatting Rules Manager if only part of the selected list changes; it should cover the data rows, for example =$A$2:$C$100. Avoid a format that makes the cell values difficult to read.
- Review the highlighted records. Compare the flagged rows and confirm that they are duplicates according to your chosen definition. If you want to see matches next to one another, use Data > Sort and sort by the fields that define the duplicate. Sorting changes the order of the records but not their values. You can also use Data > Filter and filter by a column or by color when the filter menu offers the conditional formatting color. Keep the original data until you have checked the results.
- Clear or adjust the formatting when finished. To remove the highlighting, select the affected range and use Home > Conditional Formatting > Clear Rules. Choose Clear Rules from Selected Cells if you want to keep other conditional formatting elsewhere on the sheet. If the rule is wrong, open Home > Conditional Formatting > Manage Rules, select the rule, and edit its formula, formatting, or Applies to range.
For a different layout, change the formula’s ranges and criteria to match the columns being compared. For example, if the list uses columns B through D and the first record is on row 5, the rule must compare the B, C, and D values starting at row 5, and the Applies to selection should also start on row 5. The comparison ranges must all cover the same rows and have the same height.
On Excel for Mac, the formula-based conditional formatting approach is also available; use the Home tab’s Conditional Formatting and New Rule controls, though dialog labels or placement can vary slightly by release. Excel for the web supports conditional formatting, but some rule-editing controls may differ from the Windows desktop interface. If you cannot find the formula rule option in your version, open the workbook in the Excel desktop app.
Common Mistakes
- Checking only one column by accident. The Duplicate Values rule highlights repeated cells in the selected column. It does not compare a complete row. Use the formula rule when the entire combination of fields must match.
- Including the header row. Keep headings out of the data range. Otherwise, duplicated labels or a repeated header can be treated as a record.
- Leaving out a meaningful field. If you compare only a name and email but separate account numbers matter, two distinct records could be marked as duplicates. Include all fields that define a unique record.
- Using mismatched ranges. In COUNTIFS, each criteria range must have the same dimensions. A range ending at row 100 should not be paired with another ending at row 99.
- Assuming similar-looking values are identical. Extra spaces, different punctuation, text stored as a number, and different date values can affect matching. Likewise, values that look identical because of cell formatting may not actually be the same underlying value.
- Deleting records before checking them. Conditional formatting identifies candidates; it does not decide whether a repeated record should be removed. Confirm the records and keep a backup before using Excel’s Remove Duplicates feature.
Tips
- Use a distinct highlight color. A light fill makes matches easy to scan without obscuring text. If the sheet already uses colored fills to convey meaning, choose a different style so the duplicate indicator is not confused with existing data.
- Include only the columns relevant to identity. Two order rows might share a customer and date but differ in product. Decide whether you are finding repeated orders or repeated customers, then select the right fields for the rule.
- Check for spaces and inconsistent entry. Names such as “Lee” and “Lee ” may appear the same at a glance but contain different text. If appropriate, clean a copy of the data with Excel’s TRIM function or standardize entries before comparing. Do not alter original values without a reason.
- Make your range easy to maintain. Excel tables can expand as new records are added, but conditional formatting rules should still be checked after adding data. Open Manage Rules and verify the Applies to range covers the new rows.
- Do not confuse duplicates with repeated individual values. A shared city or department may be normal. A row-level rule checks the combination of chosen columns, which is usually more useful for identifying repeated records.
Troubleshooting
Nothing is highlighted: Check that the rule formula begins with an equals sign, refers to the correct columns, and uses the correct first data row. Confirm that Applies to includes the intended range. If the records begin on row 3, for instance, the formula’s relative row references should begin on row 3 as well. Verify that there really are repeated combinations across all the fields included in the test.
Rows that look different are highlighted: Inspect each compared field, including values that may be hidden by column width or number formatting. Excel compares stored values rather than just how they appear. A date displayed without a time may still contain different time values. Widen columns or inspect the formula bar to see more of the underlying content.
Rows that look the same are not highlighted: Check for leading or trailing spaces, inconsistent punctuation, different number-versus-text storage, or an omitted column in the rule. If regional settings use semicolons as formula separators, Excel may require semicolons instead of commas when you enter a formula. Follow the separator Excel accepts in your installation.
Only some cells in a duplicate row change: Review the Applies to range and make sure the rule applies to all columns you want highlighted. Also check that the formula’s criteria use the correct column letters and that each comparison range starts and ends on the same rows.
Blank rows are highlighted: Empty records can match one another because their compared fields are all empty. Exclude blank rows from the selected range or remove them from the working copy before applying the rule. If rows with some empty fields are valid records, decide whether those blanks should count as part of a duplicate match before changing the formula.
Frequently Asked Questions
Can Excel find duplicate rows without deleting them?
Yes. Conditional formatting can highlight repeated values or, with a formula-based rule, repeated combinations across a complete row. Neither method deletes data. Review the highlighted records before taking any further action.
Does the Duplicate Values command find duplicate lines?
Not by itself. It checks whether values repeat in the selected cells. To find rows where several fields match together, create a conditional formatting rule using COUNTIFS and include each relevant column.
Can I find duplicates based on just two columns?
Yes. Select the data rows and create a formula-based rule that compares only those two columns. Rows match when both selected fields match, even if other columns differ. This is useful when those two fields together define a repeated record.
Does Excel treat uppercase and lowercase text as different?
COUNTIFS is generally not case-sensitive, so text that differs only by capitalization is treated as a match. If capitalization must distinguish records, use a case-sensitive approach rather than relying on the standard COUNTIFS rule.
How do I remove duplicates after finding them?
First save a copy and verify which records should remain. Then select the data and use Data > Remove Duplicates, choosing the columns that define a duplicate. This command removes rows, so it is different from the highlighting steps above and should be used only after review.
Will the rule include new rows I add later?
Not necessarily. A rule applied to a fixed range such as A2:C100 may not cover records entered below row 100. Check the rule’s Applies to range after adding data, or use an Excel table and confirm that the formatting rule extends to new rows.
Related
- How to Export an Active Directory User List to Excel
- Export Excel to Word: Copy Tables Without Losing Format
- How to Exit Group Edit Mode in Excel and Ungroup Sheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Subscript in Google Sheets: Methods That WorkTo show subscript in Google Sheets, paste subscript Unicode characters such as the ₂ in H₂O; Sheets does…
- Conditional Formatting Based on Another Cell in ExcelYes. In Excel for Microsoft 365, conditional formatting can check the value in another cell and change the…
- Automatic Text to Columns in Excel: Options and LimitsExcel does not have a built-in setting that automatically runs Text to Columns whenever you type or paste…
- Excel Not Responding to Keyboard? Causes and FixesIf Excel is not responding to your keyboard, first check whether the cell, worksheet, or a dialog box…
- Excel Scatter Plot: Color Points by ValueIn Excel for Microsoft 365 on Windows, the reliable way to color scatter-plot points by a value is…
- Rename a Sheet in Excel Without a MouseIn Excel for Microsoft 365 on Windows, select the worksheet you want to rename, then press Alt, H,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.