Excel highlight duplicates how to#

Excel highlight duplicates how to#

How to highlight duplicate rows in Excel.

Shade entire rows based on duplicate values in one column.

How to find N th and subsequent duplicate records.

How to highlight duplicates in a range (multiple columns).

How to highlight duplicates in Excel except 1 st instances.

Highlighting duplicates in Excel with 1 st occurrences (built-in rule).

These techniques work in all versions of Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and lower. The biggest advantage of this method is that it not only shows you the existing dupes, but detects and colors new duplicates as you input, edit or overwrite your data.įurther on in this tutorial, you will find a number of ways to highlight duplicate records depending on your specific task. The fastest way to find and highlight duplicates in Excel is using conditional formatting. Undoubtedly, the duplicate formulas are very useful, but highlighting duplicate entries with a defined color could make data analysis even easier. Last week, we explored different ways to identify duplicates in Excel. Also, you will see how to highlight duplicates with different colors using a specialized tool. We are going to have a close look at different methods to shade duplicate cells, entire rows, or consecutive dupes using conditional formatting. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.In this tutorial, you will learn how to show duplicates in Excel. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. This will highlight the duplicate cells containing the temperatures 9,10 and 13 in columns A to D. This will redirect us back to the previous formula box.

Go to Fill>Background Color and set it to the color you want to highlight to.

Then, we have to click on the Format tab beside the preview box.

In the Format values where this formula is true box, we need to assign the formula =COUNTIF ($A$2:$D$6,A2)>1.

Click on Use a formula to determine which cells to format.

Example of how to Apply Conditional Formatting

From the home tab in the ribbon, go to Conditional Formatting > New Rule.

To highlight duplicate values, we need to:

Columns A, B, C and D has the name of the winners for each week. The following example contains a temperature database during the weekdays of December. But, the relative cell A1 changes for each iteration. We are using COUNTIF in the range with an absolute address. This evaluation is done in the selection of the time the rule was created. It evaluates the formula relative to the cell that is active. Excel evaluates the formula used in conditional formatting. Still, we are using a custom formula for more flexibility. Conditional formatting has some preset rules. We use conditional formatting to highlight the cells. If a value exists more than once, COUNTIF returns TRUE and invokes the formatting rule. The COUNTIF function counts the occurrence of each value in the range.

Here, we are using conditional formatting to highlight the duplicate values.

=COUNTIF(Range,A1)>1 How the Formula Works Example of How to Highlight Duplicate Values Generic Formula In this tutorial, we will learn how to highlight duplicate values in Excel.įigure 1. We can easily highlight duplicate values using conditional formatting and the COUNTIF function. Highlighting duplicates is one such feature. Excel has some very effective ways to deal with duplicate values.