Almost every organization and business uses Excel spreadsheets to store data about employees, clients, and projects. This is because Excel offers versatile tools and features for data storage, manipulation, and analysis. As much as acquiring skills and certification courses in Excel and Microsoft gives you a competitive edge over others, as the organization expands, the data expands as well. To help you, Excel provides many features to find, highlight, and identify duplicates in Excel columns without deleting them.
Overview
Microsoft Excel is a popular piece of office software that lets people make spreadsheets for a wide range of purposes. Excel skills are often needed for jobs, and getting better at Excel could be a good way to make yourself more marketable in the job market. One way to get better at Excel is to figure out how to find copies in Excel files. In this piece, we explain why it’s important to find duplicates, show you how to find, and identify them in Excel, and give you tips to help you find duplicates in your projects.
Tips For Finding Duplicates in Excel
Here are some tips that can help you find copies more easily in your Excel projects:
#1. Master formulas
Formulas may be difficult at first, but they make specialized jobs easier. Check out Microsoft’s support page and Excel blogs.
#2. Always check
In smaller collections, duplicates are easy to find. When working on a project, check spreadsheets for copies often.
#3. Learn More About Excel
if you frequently use it at work. To identify duplication and other duties faster, watch video tutorials and test different functions.
How to Find Duplicates in Excel
Spreadsheets in Excel are a handy way to organize and look at data. Even though duplicate values are sometimes necessary for a data set, they can make the information less clear and harder to evaluate correctly. Due to human mistakes, Excel sheets can also have duplicate values, which can make the data wrong. To stop this from happening, it’s often helpful to find copies so you can decide whether or not to get rid of them.
How to Find Duplicates in Excel: Step-by-step Guide
It’s normal for data to come from more than one place, for multiple people to enter records, or for more than one transaction to have the same information. Because of this, you will need to find and change copies. Hence, Excel gives you different ways to do this based on the data you have and the results you want. To help you, here are several methods that you can use to find, count, and filter duplicates in Microsoft Excel
#1. How to Find Duplicates in Excel: Using the Conditional Formatting Method
You can use this method to locate cells with duplicate information in Excel:
- Open Excel.
- Click the upper left “File” option.
- Select “Open” from the drop-down menu.
- In the dialogue box, pick your file.
- Finish by clicking “Open” to close the window and view your paper.
- Choose the cell range to check for duplicates.
- Highlight cells by right-clicking and dragging to select the full range.
- In the upper left corner of the screen, click “Home”.
- Use the menu to select “Conditional Formatting.”
- When the drop-down menu opens, hover over “Highlight Cell Rules” at the top.
- On the second menu, click “Duplicate Values” at the bottom.
- In the dialogue box, click the right-down arrow and select a formatting style to format duplicate values.
- When done, click “OK” to exit the window and apply the changes.
- Your chosen color will highlight duplicate data in Excel.
#2. How to Find Duplicates in Excel: Using the triplicate values
If you want to find only triplicate values, you can use the following steps:
- Open Microsoft Excel.
- Click “File” in the top left corner.
- Select “Open.” from the drop-down option.
- Find your document in the dialogue window and click it.
- Click “Open” to exit the dialogue box and view your spreadsheet after selecting the file.
- Selecting the region you’re working on and hitting the “Home” button in the upper left corner will delete any conditional formatting.
- Click “Conditional Formatting” next.
- In the drop-down menu, click “Clear Rules” at the bottom.
- The second menu’s first choice is “Clear Rules from Selected Cells.”
- Leave the cells highlighted and click “Conditional Formatting” again after clearing the settings.
- Click “New Rule” at the bottom of the drop-down menu.
- In the dialogue box, find “Select a Rule Type” and
- Click “Use a formula to format cells.”
- There will be a text box under “Format values where this formula is true.”
- Enter the formula: =COUNTIF($A$1:$C$10,A1)=3 in the text box.
- After finishing, click “OK” to close the dialogue box.
- Excel will now show duplicate numbers in the selected area.
#3. How to Find Duplicates in Excel: Using the COUNTIF Method
You can use the following steps to find duplicates in your documents using a COUNTIF formula:
- Like above, open Excel and your document.
- Pick the cell next to the first cell in the range to check for duplicates. We’re using A2, so we chose B2.
- In the formula bar above the spreadsheet, enter: =COUNTIF(A: A, A2)>1.
- From the formula straight into the cell.
- A2 is the initial cell, however, you can write another cell in the formula.
- In the highlighted cell, click the little box in the lower right corner.
- Dragging highlights other cells to check.
- Duplicates appear as “True” in Excel, while unique values appear as “False”.
- To change Excel’s “True” or “False,” adjust the calculation.
- To display “Duplicate” and “Unique,” use the following formula in the blank cell located next to your first cell: =IF (COUNTIF (A: A, A2)>1, “Duplicate”, Unique.”
- Next, drag to check all column cells.
- Formulas that display “Duplicate” next to duplicate values and blank next to unique values are also available:
- =IF(COUNTIF (A:A,A2)|”Duplicate”, “”)
- Finally, dragging selects all column cells and checks for duplicates.
Find Duplicates in the Excel Column
When a value occurs in a column more than once, it is called a duplicate value. Microsoft Excel is a common tool in business analysis, but it can be challenging to spot and remove duplicate data. In Excel, it is common practice to clean up massive datasets by deleting duplicates. This is because Duplicate data can appear in a spreadsheet if many users have access to the same document or if two or more tables are merged into one.
Consequently, the information is useless. This is so because duplication becomes more common as data sets grow larger. Problems may arise if they are not identified and addressed in a timely manner. Also, Excel’s built-in functions and basic formulas are straightforward to use to find and highlight duplicates. The following guide shows how to find several values in a column, including illustrations and the correct format.
Find Duplicates in the Excel Column: Step-by-step Guide
Finding duplicates in a single Excel column or across numerous columns is harder than filtering. With Excel’s conditional formatting, you may easily detect duplicates in a single column, but there’s no reason to eliminate them. To find duplicates in Excel columns quickly and efficiently, follow the instructions below.
Step 1: Highlight the Column With the Duplicate Values.
In Microsoft Excel, the first step to finding duplicates is to point out the column with the duplicate numbers. To do this, click and drag your mouse over the column header to select the full column. When it is selected, a blue edge will show up around the column.
Step 2: Open up the Conditional Formatting Tool
The next thing to do is to open the tool for conditional formatting. To open the tool, click on the Home tab and then on Conditional Formatting. When you open the Conditional Formatting tool, choose “Highlight Cell Rules” and then “Duplicate Values.”
Step 3: Select the Options for Duplicate Value
When you choose the “Duplicate Values” choice, a window will pop up where you can choose different duplicate value options. These choices include the type of formatting to use on the duplicate values and the range of cells to look for duplicates.
Step 4: Review the Duplicate Values
Once the duplicate numbers have been found, you can look at each one to see if it should be taken out. To get rid of the duplicate values, pick the cell that has the value and press the delete key.
Step 5: Clear Conditional Formatting
Once you have gone through all of the duplicate numbers and deleted the ones you don’t want, you can turn off the conditional formatting. To do this, select the Home tab and then click Conditional Formatting. Choose “Clear Rules,” then choose “Clear Rules from Selected Cells.” This will delete the conditional formatting from the chosen cells.
How to Identify Duplicates in Excel Without Deleting
When dealing with a lot of data, we often have problems with duplicate entries in Excel. The most common ways to find duplicates in Excel are the COUNTIF tool and Conditional Formatting. Aside from these two, we will look at several other ways to Find Duplicates in Excel Without Deleting Them.
How to Identify Duplicates in Excel Without Deleting: Step-by-step Guide
We will see how Conditional Formatting and different functions like COUNTIF, IF, etc. can be used to find duplicates based on our tastes without deleting them.
#1. How to Identify Duplicates in Excel Without Deleting: by Conditional Formatting
Conditional formatting helps you identify the presence of duplicates in the table and eliminate them, which can be done by following these steps:
- First, specify the range of cells you want to look for duplicates in.
- Next, click on the “Conditional Formatting” button on the “Home” tab of the Ribbon.
- Select “Highlight Cell Rules” from the drop-down menu and then “Duplicate Values” from the next menu.
- Lastly, choose the format you want to apply to the duplicate numbers and click “OK.”
- Once you apply the conditional formatting to your data set, any duplicate numbers will be highlighted in the color(s) you chose.
- You can then take steps to get rid of the duplicates, if necessary.
#2. How to Identify Duplicates in Excel Without Deleting: by formulas
Here are some formulas that can be used to find the numbers in a list that are repeated without deleting them.
- Select a blank cell next to the list of numbers you want to find duplicates of,
- Then enter the formula =IF(COUNTIF($A$1:$A$14, A2)>1, DUPLICATE”,”), and
- Finally, drag the fill handle down to the cell where this formula needs to be applied.
#3. How to Identify Duplicates in Excel Without Deleting: by COUNTIF
The easiest way to find duplicates without deleting them is with the COUNTIF function. To do this, follow these steps:
- First, type the code that appears below into cell F5.
- Next, Press the ENTER key.
- The last step is to move down to AutoFill for the rest of the series.
- COUNTIF functions return TRUE if there are duplicates in a given range, and FALSE if there are no duplicates.
- Then, choose the whole set of data and press CTRL+SHIFT+L.
- Last, search the dataset for numbers that are TRUE.
How Do I Find Duplicates in Two Excel?
They are,
- VLOOKUP, COUNTIF, or EXACT functions
- Conditional formatting.
- Power Query.
- External tools and add-ins.
- Visual checks for duplicates.
How Do I Select Only Duplicates in Excel?
Selected cells in a range, table, or pivot table report. Click Conditional Formatting, then New Rule, in the Home tab’s Styles group. Choose Classic in Style, then Format only top or bottom ranked values in Format only unique or duplicate values.
What is the Fastest Way to Count Duplicates in Excel?
By using the COUNTIF function.
What is the Best Formula to Find Duplicates in Excel?
=IF(COUNTIF($A$2:$A$7, A2)>1,” Duplicate”,” Unique”).
How Do I Find Duplicates in a Table?
By using the GROUP BY and HAVING clauses
In a Nutshell
I am convinced you now have a good idea of how to find duplicate numbers in columns in Excel. Use whichever method you feel most comfortable with. Also, if you have any questions or doubts about this topic, feel free to leave a comment here.
References
