To highlight duplicates in Excel Online, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a highlight style and confirm. The rule marks repeated values in the range you selected, making it easier to spot entries that may need checking.
What You’ll Need
- An Excel workbook open in Excel for the web, with permission to edit it.
- A clear idea of which cells should be compared. You can select one column, a block of cells, or a larger range.
- Optional: a color or style you can recognize easily against the existing cell formatting.
Step-by-Step
- Open the workbook and locate the data. Open the file in Excel Online and go to the worksheet containing the list. If the workbook is view-only, ask for editing access or save an editable copy before changing its formatting. You do not need to sort the data first; conditional formatting checks the selected range without rearranging it.
- Decide what counts as a duplicate. Think about the values that should be compared before selecting cells. If you want to find repeated email addresses, select the email column rather than the entire worksheet. If you select several columns together, Excel checks for repeated values across the whole selected range; it does not compare each row as a complete record.
- Select the range to check. Click the first cell, then drag to the last cell in the range. You can also click the first cell, hold Shift, and click the last cell. For example, if email addresses are in column C with a heading in C1 and entries from C2 through C150, select C2:C150. Excluding the heading prevents a heading value from being treated as part of the data.
- Open the duplicate-values rule. On the ribbon, select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The rule is based on the cells currently selected, so check the selection before opening the menu. If you cannot see the full ribbon, expand the ribbon or widen the browser window, then look for the Conditional Formatting control on the Home tab.
- Choose the duplicate option. In the dialog, choose Duplicate if Excel presents a choice between duplicate and unique values. Duplicate is the setting you want when you are looking for repeated entries. Do not choose Unique: that option highlights values appearing only once in the selected range.
- Pick a visible format and confirm. Choose one of the offered formatting styles, such as a colored fill, and select OK or Apply if the dialog shows that button. Use a color that contrasts with the worksheet but still leaves the text readable. If a custom-format option is available in your dialog, you can use it to choose a different fill or text appearance.
- Review the highlighted cells. Excel highlights every occurrence of a repeated value in the selected range, including the first occurrence and later copies. Check a few examples against the original data. A highlight tells you that a value is repeated; it does not decide whether the repetition is an error. For example, two orders can legitimately share the same status or product name.
- Check the rule when the data changes. If you add more entries, check that the conditional-formatting rule covers the new cells. A rule applied to C2:C150 will not necessarily include entries typed below row 150. To extend its reach, select the relevant range and apply the rule again, or use the conditional-formatting rule controls to review its applied range.
Common Mistakes
Selecting too much data. When you select a broad block, Excel looks for repeated values anywhere within that block. A customer name in one column could therefore be highlighted because the same text appears in a notes column. Narrow the selection to the specific field you mean to check, such as invoice numbers or email addresses.
Including the column heading. If a heading is included in the selection and the same text occurs in the data, Excel may flag it as a duplicate. Start with the first row of actual entries, not the header row. This also makes it clearer which highlighted cells are records that need review.
Assuming a highlighted value is automatically wrong. Repeated values may be expected. A product category, city, or department name is likely to appear many times in a normal dataset. Use the highlight as a review aid, then decide whether each match is valid before deleting or changing anything.
Expecting the rule to compare whole rows. The built-in Duplicate Values rule checks cell values, not whether every field in two records matches. Selecting A2:D100 does not identify duplicate records row by row; it can highlight a value that appears in a different column. Use a method designed to compare complete records if that is your goal.
Overlooking inconsistent data types or extra characters. A number stored as text may not behave like a numeric value, and a name with an extra space may not match the same name without it. If entries that look identical are not highlighted together, check for hidden spaces, inconsistent punctuation, and numbers stored as text before deciding that the rule is broken.
Tips
Keep the selection focused. For a reliable check, apply the rule separately to each column whose values should be compared. This avoids matching unrelated fields and makes the result easier to interpret. If you need to check several columns independently, repeat the process for each one.
Use a clear, restrained color. A strong fill makes matches easy to find, but very dark or saturated fills can make text difficult to read. Choose a style that remains legible and consider whether existing colors already mean something in the worksheet. The formatting is visual only; it does not change the cell values.
Use filters or sorting to review the matches. Conditional formatting does not move duplicate entries or group them together. After highlighting, you can sort or filter the data to investigate it, but take care to sort the full table rather than a single column so records stay intact. If the workbook is shared, tell collaborators before making structural changes.
Think about the scope before sharing the result. A highlight is useful only if other people know what range was checked and what a match means. Add a short note near the data or explain the rule when handing off the workbook. If you later paste in new rows, review the rule’s range so fresh entries are not silently left out.
Remove the rule when you no longer need it. To clear the highlighting, select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Check the selected range first: clearing rules from a larger area than intended can remove other conditional formatting there as well. If you only want to change the color, edit the existing rule instead of creating extra rules.
Troubleshooting
Nothing is highlighted. First confirm that Duplicate, rather than Unique, is selected and that the intended cells were selected when the rule was created. Check whether the values are truly identical: leading or trailing spaces, different punctuation, and text-versus-number differences can make entries that look similar behave differently. Try applying the rule to a small test range containing a known repeated value.
Unexpected cells are highlighted. The selected range may include other columns or unrelated sections. Clear the rule from the affected cells, select only the intended data field, and apply it again. Also inspect the highlighted values themselves; a repeated word in two different fields can be a real match according to the broad selection even when it is not a duplicate record.
New rows are not highlighted. The applied range may end before the new rows. Select the updated data range and add the duplicate-values rule to it, or open Home > Conditional Formatting > Manage Rules if available to review and adjust the range for the existing rule. After changing the range, check a known duplicate near the bottom to confirm it is covered.
The color is hard to see or seems unchanged. Reopen the conditional-formatting options and choose a more distinct style. Existing formatting or another conditional-formatting rule can make the result confusing. If several rules affect the same cells, review them in Manage Rules and remove or adjust rules that are no longer needed. The cell’s underlying value should remain unchanged when you change its appearance.
The menu or dialog looks different. Excel for the web can adjust its ribbon layout to fit the browser window, and interface labels may vary slightly. Make sure you are on the Home tab, expand the ribbon if it is collapsed, and look under Conditional Formatting for Highlight Cells Rules. If you lack edit permission, the controls may be unavailable; request access rather than trying to change the file in view-only mode.
Frequently Asked Questions
Does Excel Online highlight the first occurrence as well as later copies?
Yes. The Duplicate Values rule highlights all occurrences of a repeated value in the selected range, including the first one. It does not mark only the later copy.
Can I highlight duplicates in just one column?
Yes. Select the data cells in that column, leaving out the header, then apply the rule. This is usually the clearest choice when you want to check a particular field such as product codes or email addresses.
Can I find duplicate rows with this command?
Not directly. The built-in rule checks individual cell values within the selected range, not entire rows as combined records. To find matching records, use a method that compares the fields in each row rather than applying this rule to a multi-column block.
Will the highlights update if I edit a value?
Conditional formatting responds to changes within the range covered by its rule, so a match can appear or disappear when a cell value changes. Newly added cells outside that range may not be checked until you extend the rule’s range.
Does highlighting duplicates delete or change any data?
No. The rule changes only how matching cells look. It does not remove, merge, or edit values. Review the highlighted entries yourself and make a separate, deliberate change if you decide a record should be corrected or removed.
How do I remove duplicate highlighting?
Select the relevant cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Be precise with the selection so you do not clear other formatting rules that you still need.
Related
- Rate Quotation Format in Excel: A Practical Guide
- PV in Excel: How to Calculate Present Value and Payments
- Excel Payment Calendar: Build One That Updates
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Select Non-Empty Cells in Excel: Methods That WorkTo select every non-empty cell in a chosen range—including both typed entries and formulas—in Excel for Microsoft 365…
- Excel Page Breaks Keep Appearing? How to Stop ThemExcel page-break lines keep appearing because Excel is showing where the worksheet will split across printed pages. To…
- Create an Outlook on the Web Email Signature in MinutesTo create a signature in Outlook on the web, open Settings, find the signature settings, write and format…
- Regression Analysis in Excel: Steps, Results, and MeaningTo run regression analysis in Excel for Microsoft 365 on Windows, enable the Analysis ToolPak if needed, then…
- Google Sheets Dropdown Chips: Setup and UseGoogle Sheets dropdown chips are colored, pill-shaped choices displayed inside cells; create one with Insert > Dropdown or…
- How to Double Space in Word MobileTo double-space text in Word Mobile, select the text, open the Home tab, choose Line Spacing or Paragraph…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.