Identifying Duplicates in Excel
It’s easy for anyone working with large quantities of data to get lost in the sea of numbers: Values may be missing, in the wrong place or appear multiple times in the table. Sometimes these duplicates are intentionally there, but often they are mistakes. However, in both cases you may want to have these duplicates automatically displayed to you: When dealing with complex tables, it’s a real hassle to have to find these entries manually. So, it’s helpful to know how to find duplicates in Excel.
Register a domain name
Build your brand on a great domain, including SSL and a personal consultant!
Excel: Displaying Duplicated Values
Typically, you may want to find duplicates in Excel for one of the following reasons:
- Duplicates are there by mistake and should be deleted.
- All duplicates are important for analysis and should be highlighted in Excel.
Read our separate guide to learn how to remove duplicates from datasets.
Excel can find duplicates for you. This way, you don’t need to go through every single row yourself. The spreadsheet program can automatically display duplicates for you. To do so, you first need to select the area you want to check. This could be an entire row or column, or a separate user-defined area. You can use your mouse to select an area by dragging the square box. You can also select individual cells. Simply hold down the [CTRL] key and then click on the relevant cells.
Once you have selected an area for analysis, you can then instruct Excel to identify duplicates. You can do so via Conditional Formatting. You can find this feature in the Styles section of the Home tab. Highlight Cell Rules contains the function for Duplicate Values –you can select which color you wish to use to highlight the duplicates. You can also instruct Excel to show you the exact opposite: unique values. These are values that only appear once in a table.
The Duplicate Values function highlights all the entries that appear more than once. This means that not only double values are identified, but also values that can be found in three or more instances. If you want to find values that appear three times in a table, Excel has a function for this. All you need to do is define a rule. You can find this function in the Conditional Formatting menu. In this menu, you can define a formula to be used for formatting. If you want to find triplicates for example, you can use the COUNTIF function:
=COUNTIF($A$2:$A$10;A2)=3You can select the area to be analyzed (the first parameters in the formula). You can also specify the first cell of the section in the second parameter: If the content appears three times (=3), the corresponding cells are highlighted. Conditional formatting is automatically applied to the selected cells and checked for the content of the other cells. (For this reason, you have absolute references in the first parameter and relative references in the second.)
You can also completely hide all other cells. This simplifies working with duplicates as they can be identified with ease. A filter is used for this purpose. You can apply a filter to entire columns, which should be selected first. To activate the filter, switch to the Data tab where you’ll find the appropriate button.
The first cell of the column will then show a drop-down menu where you can define the filter criterion. (It is therefore a good idea to work with header rows.) Filter by Color so that Excel only displays duplicates. Since Excel has color-coded all duplicate values, you can now apply this color as the filter criterion. This enables you to instruct Excel to only show the duplicates; everything else will be hidden.
HiDrive Cloud Storage with IONOS!
Based in Europe, HiDrive secures your data in the cloud so you can easily access it from any device!
Related articles
Ground PictureShutterstock Editing an Excel drop-down list
Drop-down lists allow you to configure tables in Excel in a way that is more professional, easier and less prone to errors. However, like anything else in day-to-day work, drop-down menus also have to be changed now and again. For this reason, we are going to explain how to edit Excel drop-down lists so you can adapt their content and appearance and add useful information for other users.
UndreyShutterstock Creating an Excel drop-down list
If you regularly use spreadsheet programs to process large datasets, you will welcome anything that helps. This is where drop-down lists in Excel come in. These allow you to conveniently add content with just one click so you don’t have to type the same thing again and again. We show you how to insert a drop-down in Excel.
Wrapping text in Excel: How to display the contents of your Excel cells on multiple lines
Excel cells often contain only a few numerical values or short text. But when you’re dealing with lengthy contents, the Microsoft application often hides a portion of the information. You can prevent this by wrapping text, which Excel allows you to do in two different ways. In this article, you’ll learn everything you need to know about this feature.
Ground PictureShutterstock Freezing Rows and Columns in Excel: A Guide
The bigger an Excel table is, the harder it is to maintain an overview. The problem is that cells like the header bar become hidden without the use of special formatting. The following article shows you how to freeze certain Excel columns and rows so they stay visible when scrolling.
G-Stock StudioShutterstock How to create a table in Excel
Excel is a powerful tool for formatting, analyzing and presenting your data. Excel lets you create tables that perfectly organize groups of data and provide useful features like sorting and filtering functions, for example. In this tutorial, you’ll learn how to create Excel tables and tailor them to your individual needs.
