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.
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.
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:
You 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.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 package!