Open up the example workbook, and take a look at the first sheet. You’ll see that it has a number of names next to employee numbers.
Unfortunately, something went wrong in our example HR system, and some people have been put on the list twice with two different employee numbers.
We’ll use conditional highlighting to find them.
Highlighting duplicate values
First, select all the cells in the Name and Employee Number columns.
Make sure the Home tab is open in the Ribbon, and click Conditional Formatting > Highlight Cells Rules > Duplicate values…
A new window will appear.
In the drop-down below the text Format cells that contain: select Duplicate
The final drop-down menu gives you many highlighting options. Just for the sake of doing something different, we’ll select Yellow Fill with Dark Yellow Text.
Finally, click OK.
If you scroll through the list, you’ll see that any cell that matches another cell exactly has been highlighted in yellow.
You’ll also notice that none of the employee numbers have been highlighted. Each of these is unique.
Some of the duplicates are far apart, and that’s not convenient. Let’s filter the unique names out so we only see the duplicates.
To do this, simply right-click on a cell that’s been highlighted, and select Filter > Filter by Selected Cell’s Color.
You can also do this from the Data tab.
Start by clicking Filter, which will add the filter arrows to the top cells in each column.
Click on the arrow in the Name column, and select Filter by Color. Then choose the dark yellow color.
Now, you’ll see only the highlighted cells, and you can deal with them how you wish.
We’ll look at a couple strategies for cleaning up duplicate values in a moment.
The above method is nice because it highlights duplicate values, so you can come back to your spreadsheet and find them again quickly.
If you’d like to get rid of duplicates without highlighting, you can do that, too.
As an example, we’ll get rid of the duplicated names from the Full Name column in our example spreadsheet.
If you’ve filtered the column by color, clear the filter now so all the cells are visible.
To get started, select the list in which you’d like to remove duplicate entries. We’ll select the Full Name column.
Alternatively, you can simply select a cell in a table to filter everything in its column.
Then, open the Advanced Filter dialog by clicking Advanced in the Sort & Filter portion of the Data tab.
You’ll see a new dialog.
The List Range: box may contain a larger range than you initially selected (you can see that cells in column B were also included). If you don’t want to copy the entire table, you’ll need to change the selection.
In this case, if you want to only select column A, you’ll need to change the List range: from $A$1:$B$26 to $A$1:$A$26.
Choose either Filter the list, in-place, or Copy to another location.
We recommend copying the list to a new location, because you’re deleting data here—and if you want to get it back, it’s going to be rather difficult.
Click into the Copy to: box, and specify a new location. For our purposes, we’ll simply enter D1.
Make sure that the Unique records only option is checked.
Then, just hit OK, and you’ll get the de-duped list in its new location.
Keep in mind that this removes all duplicates. If you want to keep one value that’s been duplicated many times, you’ll need to use a different method.
Consolidating duplicate values
If you have duplicate values, there’s a chance you’ll want to combine them in some way or another.
Excel’s consolidation feature can help you do this (or any of these 3 methods).
The consolidate menu gives you lots of options for the final value after consolidation; we’ll just look at average here, but there are other useful options you can use. Skim the menu to check it out.
On the second and third sheets in our sample workbook, you’ll see that we have a list of companies and their stock values. On the second sheet, those values are from May. On the third, they’re from June.
Let’s find the average value from those two dates.
First, open the Stock Values May sheet, click into cell D1, and type “May/June Stock Average” so you don’t forget what these values are.
Next, in the Data tab of the Ribbon, click Consolidate (it’s in the Data Tools section). That will bring up a new window.
Make sure the Function: drop-down is set to Average (you’ll notice that there are lots of options; spend some time exploring these).
Then, click into the Reference: box, and click and drag to select all the stock values in column B.
Click Add next to the All references: box. Then click into the Reference: box again, delete the contents, and select the stock values from June (you’ll need to click over to the next sheet). Click Add when you’re done.
This is what it should look like when you’re done:
Because we didn’t select the column labels, we’ll leave the Use labels in checkboxes unchecked.
Finally, click OK, and you’ll see the average stock values in column D of the Stock Values May sheet.
Duplicate values can be a real pain—especially in very large spreadsheets.
But with conditional formatting, advanced filters, and consolidation, you can manage duplicates with ease!