Working with a large dataset in Excel typically necessitates the presence of duplicate values, which can be found frequently. You can find and highlight duplicates easily in Excel and Google Sheets. Data could become inconsistent or inaccurate as a result of incorrect calculations or formulas. In Excel, you might frequently want to draw attention to values that appear twice in different columns.
The Highlight Cells Rules feature found in Excel’s Conditional Formatting options makes it fortunately simple to accomplish this.
Highlight Duplicates in Excel
Finding and highlighting duplicates is a simple process thanks to a feature in Excel called Conditional Formatting. Focus on duplicates that appear in multiple columns in addition to the first one. To highlight duplicates from your data sets in Excel, you can use the conditional format formula COUNTIF(range, top_cell) > 1.
Here, to use the conditional formatting formula, you can take the following steps:
- Choose the data range.
- Click on New Rule under Conditional Formatting.
- If the New Formatting Rule dialog box appears, you can use a formula to specify which cells to format. Don’t forget to input the formula.
- Then, format your duplicate cells by clicking the dialog box’s Format button and selecting OK.
- In the New Formatting Rule, click OK after selecting a formatting style.
How to Highlight Duplicates in Excel
- Choose the data you want to examine for duplications. This may refer to a column, row, or set of cells.
- Go to the Styles group under the Home tab.
- To continue, simply click Conditional Formatting.
- Click the Highlights Cells Rules,
- Choose Duplicate Values.
- The Duplicate Values dialog box window’s default layout has a light red fill and dark red text.
- Clicking OK will apply the default format.
The dropdown list includes a few other predefined formats in addition to the red fill and text formatting. The last item in the drop-down menu, Custom Format, allows you to choose the fill and/or font color you want to use to shade duplicates using a different color.
Highlight Duplicates Using Conditional Formatting
Excel has a really useful feature called Conditional Formatting that makes it simple to find and highlight duplicates. In addition to the first instance, draw attention to duplicates across multiple columns.
Excel can assist you in locating duplicates if the data set contains any. Multiple entries can be identified automatically by Excel. You can also employ a specially created conditional formatting rule with the following formula:
COUNTIF(range, top_cell) > 1
Finding duplicates in the range A3:D9 can be done using the following formula:
=COUNTIF($A$3:$D$9, A3)>1
How to Show Duplicates Without the First Occurrence
Select the cells you want to color, and then make the following formula-based rule to highlight the second duplicate occurrence as well as any others that follow:
- Styles group, under the Home tab
- Select New Rule from the Conditional Formatting menu. Calculate which cells to format using a formula.
- Put a formula like this into the box marked Format values where this formula is true:
=COUNTIF($A$2:$A2,$A2)>1
In this case, the topmost cell in the chosen range is A2.
- If you want to change the font color or fill, click the “Format button”.
- Click OK once more to save and implement the rule.
The result is that the duplicate cells—aside from the first instances—are highlighted in the color of your choice.
How to Display the Third, Fourth, and Every Subsequent Duplicate Record
Using the formula as a basis, create a conditional formatting rule. To view duplicates starting with the Nth occurrence, however, swap out >1 for the appropriate number at the end of the formula. For example:
Make a conditional formatting rule based on the formula below to highlight the third duplicate instance and all succeeding ones:
=COUNTIF($A$2:$A2,$A2)>=3
Use this formula to highlight the fourth duplicate record and all following duplicate records:
=COUNTIF($A$2:$A2,$A2)>=4
Use the equal to the operator (=) to only highlight certain occurrences. For instance, you would use the following formula to highlight only the fifth occurrence:
=COUNTIF($A$2:$A2,$A2)=5
How to Highlight Duplicates in a Range (Multiple Columns)
If you want to look for duplicates across multiple columns, use one of the following techniques rather than comparing the columns to one another.
Highlight all duplicates—including the first occurrences—in all columns.
If a data set contains an item more than once but only once in the first instance, Excel’s built-in duplicates rule should be used.
Alternately, use this formula to create a conditional formatting rule:
COUNTIF(range, top_cell)>1
For example, to highlight duplicates in the range A4:D8, the formula goes as follows:
=COUNTIF($A$4:$D$8, A4)>1
Highlighting All Rows Where a Single Column Has Duplicate Values
If there are duplicate records in one or more of your table’s columns, you might want to highlight the entire row.
The default duplicates rule in Excel only applies at the cell level, as you are already aware. However, shading rows is no issue for a rule that uses a custom formula. The key is to choose all of the rows, and then use one of the formulas below to create a rule:
To show duplicate rows only, not first occurrences:
=COUNTIF($A$2:$A2, $A2)>1…….
To draw attention to duplicate rows that include first occurrences:
=COUNTIF($A$2:$A$15, $A2)>1…..
The first cell in the column to be examined for duplication is A2, and the last cell used is A15. You can see how effectively using both absolute and relative cell references has made a difference.
How to Highlight Duplicate Rows in Excel
The duplicate values in a particular column could be used to color entire rows. What happens, however, if you want to view rows with values that are the same across multiple columns? Alternatively, how do you identify rows that have the same values in every column or absolute duplicate rows?
Use COUNTIFS, a function that lets you compare cells based on a variety of criteria, for this. Consider using one of the following formulas to draw attention to duplicate rows with the same values in columns A and B:
Highlight duplicate rows without 1st occurrences:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1………
Highlight duplicate rows with 1st occurrences:
=COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1…….
Highlighting Consecutive Duplicate Cells in Excel
Occasionally, it may be sufficient to only display consecutive duplicate cells—that is, cells that are next to one another—instead of highlighting all duplicates in a column. To accomplish this, choose the data-containing cells (excluding the column header) and create a conditional formatting rule using one of the formulas below:
To draw attention to subsequent duplicates without the first occurrence:
=$A1=$A2
To draw attention to subsequent duplicates with first occurrences:
=OR($A1=$A2, $A2=$A3)…….
Make the following changes to the formulas if your Excel sheet might contain empty rows and you do not want the subsequent blank cells to be highlighted:
To highlight subsequent duplicate cells without the first occurrence and ignore blank cells, you must:
=AND($A2<>””, $A1=$A2)
Highlight subsequent duplicate cells with the first occurrence, ignoring blank cells:
=AND($A2<>””, OR($A1=$A2, $A2=$A3))
Excel’s Single-Column Duplicate Removal Feature
Here are the steps to follow if you have the data in one column and want to get rid of all the duplicates:
- Select the data.
- Go to Data>Data Tools>Move Duplicates.
In the dialog box for removing duplicates:
- Check the box next to “My data has headers” if your data contains headers.
- In the case, where there is only one column, make sure it is selected.
- Select OK.
By doing this, you would eliminate all of the column’s duplicate values and be left with only the unique values.
Highlight Duplicates in Excel Two Columns
One of the simplest methods for comparing columns in Excel is conditional formatting.
- In the spreadsheet, select all necessary cells. The cell range can be highlighted to achieve this.
- From the “Home tab”, then click the “Conditional Formatting” icon
- Select “Highlight Cells Rules” next.
- then select “Duplicate Value” from the drop-down menu.
- When a new window opens, you can decide how to format the duplicate values.
- You may utilize the standard setting of Light Red Fill with Dark Red Text and
- Next, click OK.
Following your click on OK, each duplicate value that appears in both team lists will be highlighted. These names are marked with dark red text and a light red fill to show that they appear in both columns.
How to Highlight Duplicates in Google Sheets
Google Sheets duplicates must be highlighted by using the conditional formatting formula =COUNTIF (A:A, A1)>1. Sheets knows where to look for duplicates thanks to the COUNTIF formula [=COUNTIF (A:A, A1)>1]. The information in parentheses denotes the column you wish to track as well as the particular cell you wish to begin with. The text outside of the parentheses specifies that you want Sheets to count duplicates or anything that appears more than once (>1).
It starts to highlight duplicate data, as you can see.
- Choose the column that you want to look for duplicates in.
- In the top menu, select “Format.”
- Choose “Conditional formatting.”
As a result, a box will fill up on the right side of the screen. “Format cells is” will be displayed as a prompt. Click on that and scroll to the bottom.
- Select “Custom formula is” from the “Format cells if” drop-down menu.
Duplicates in Google Sheets by Highlighting Them in Multiple Columns
- Choose the dataset name (without the headers)
- Select “Format” from the drop-down menu.
- Toggle “Conditional formatting” on.
- Select “Add another rule” from the menu.
Make sure the range is accurate (so that we can highlight the duplicates). If it is not, you can adjust it in the “Apply to range” section.
- Select “Custom formula is” from the drop-down menu after selecting “Format cells if”
- From the “Formatting style” menu, choose the formatting style you want to use to highlight the duplicate cells.
- You can specify different colors and styles, such as bold or italics, in addition to the default green color that will be used.
- Select “Done”
How Do I Highlight Duplicates in Excel in the Same Color?
- Choose the information you want to compare for duplications. The range of cells here can be a column, a row, or both.
- In the Styles section of the Home tab,
- select “Conditional Formatting” > “Highlight Cells Rules”>Duplicate Values
- When the Duplicate Values dialog window first opens, the default format selections are Light Red Fill and Dark Red Text.
- Just click OK to use the default formatting.
- The last item in the drop-down menu, Custom Format, allows you to choose the fill and/or font color you want to use to shade duplicates using a different color.
What Is the Formula for Identifying Duplicates in Excel?
Excel’s COUNTIF() function is the simplest way to count duplicates. This function keeps track of how many cells fall into the required range in the given range. The formula is
(=COUNTIF(Search Range,Cell reference)>1)
How Do I Highlight and Count Duplicates in Excel?
The simplest method for counting duplicates in a column is to use one of the Excel formulas we used to find duplicates (with or without first occurrences). Using the COUNTIF formula below, you can then determine the number of duplicate values:
=COUNTIF(range, “duplicate”)
where “duplicate” denotes any formula duplicates that you identified.
As an illustration, the duplicate formula could look like this:
=COUNTIF(D4:D9, “duplicate”)
How Can I Identify Duplicates Between Two Excel Sheets?
When the data is dispersed across numerous columns and rows, it can be difficult to compare these sheets for duplicate records. The following techniques are the most popular for locating duplicates between two Excel sheets:
- VLOOKUP, COUNTIF, or EXACT functions
- Conditional formatting
- Power Query
Why Duplicates Are Not Highlighted in Excel?
Check to see if the single cell contains any extra, leading, or trailing spaces. Although people frequently ignore it, Excel perceives the space as a distinct character. Probably the most frequent reason for Excel not detecting duplicates
You need to fix your $ signs.
An extremely prevalent issue. Excel might not scan the entire area if you do not include $ signs in your formula. As you move the formula down, Excel appears to be scanning the entire list, but in reality, it is only scanning the final few cells.
How Do You Highlight Matching Values in Excel?
- Select the complete data set.
- Go to the “Home tab.”
- Select “Conditional Formatting” from the Styles group.
- Drag the mouse pointer over the “Highlight Cell Rules option”
- Tap Duplicate Values.
- From the “Duplicate Values dialog box”, Be sure to select “Duplicate” in the
- Give formatting instructions.
- Select ok
Conclusion
The dynamic nature of conditional formatting is one of its many advantages. The formatting will consequently be updated automatically if you change the data in any cell. When working with a large amount of data, there may be occasions when you want to double-check to make sure there are no duplicates or you might want to highlight any duplicates to draw attention to them. In that case, Google Sheets has a simple formula to apply. Utilize the COUNTIF formula to find duplicates. It is given as: (=COUNTIF(Search Range,Cell reference)>1)
- HOW TO CREATE A PIVOT TABLE IN EXCEL: Step-By-Step Guide
- HOW TO ANNOTATE A PDF: Quick & Simple Steps
References
