We help IT Professionals succeed at work.

excel 2007 duplicates

aneilg asked
does anyone have any ideas how to check for duplicates.

the problem is i have over 100 columns, and need to compare the whole row.

is there an easy way to do it.
Watch Question

Below are the steps on how to do it:
1. Select the columns that you want to check for duplicates.
2. Home -> Styles section, click Conditional Formatting.
3. Select Highlight Cells Rules and click Duplicate Values.
4. Choose the formatting you want for duplicates.
5. OK.



hello, i've looked at the article but it only checks for cells, as apposed to rows.
i have 1000's of rows in my sheet.

also, i have over 100 columns, and there may be only 1 difference in 1 row.


if you are using excel 2010 follow mindsuperbs second comment after you select the whole area you want to check, ctrl-a will select the sheet or press the square in the top right (above 1, left of A). - and give MidSuperb the points!!!!

if not using 2010 you can follow this procedure

or if you post a sample here I will set it up for you



sorry - just re-read your comment and realised what you meant, the second part of my response  will work in any version so try this (example attached), if you cant make it work post a sample.

Also consider getting the 'ASAP Utilities' which is free and has a lot of helpful functions like this one built in


If you want to know duplicated rows, you may use this approach:

1. Concatenate all cells in a row using the function below:
=Concatenate(A1,B1,C1,D1, . . .)
2. Copy the formula down until the last row.
3. Select the concatenated column, and follow Steps 2 to 4 on my post above.


Disregard my post above.

Try the CountIf function and place this formula in E2 of your sample.


The countif funtion will count the number of duplicated values in the range you selected.


If you let us know what you need to do with the date after finding the duplicate rows we might be able to come up with something more functional



hello, i've been going through your solutions.
i have 2 spreadsheets, i ran my extraction last month and thi smonth i ran it again. i have 8 different tabs. with over a 100 columns in each tab. i basically want to highlight the duplicates first then once i have checked them, remove the duplicates. i want to stay away if possible from coutif, because i have over 100 columns.


i have two spreadsheets. with data in each.
spreadsheet1 8 worksheets.
spreadsheet2 8 worksheets.

With each worksheet corresponding to each other.
I want to look for dups in each corresponding worksheet.

the countif function described above would just add an extra column that would highlight duplicated rows and making removal off them easy - dont worry about that right now- the use of multiple spreadsheets/tabs complicates things a little.
Dont worry about the excel part for the moment just try to describe what process you would go through if you didnt have any other option - I've tried to come up with what I think you want but you need to confirm:-

1. Open the two spreadsheets
2. open tab 1 of spreadsheet 1
3. remember the content of row 1
4. check the other rows and delete any that are the same as row 1
5. continue with row 2
6. at the end of tab 1 repeat with tab 2
7. at the end of tab 8 start again with tab 1 of spreadsheet 2
8. at the end of tab 8 of spreadsheet 2 repeat the whole process comparing tab 1 of spreadsheet 1 to tab 1 of spreadsheet 2

and so on

Is this what you want to achieve?

At the end would you expect two spreadsheets with 8 tabs in each with no duplicates anywhere?
If you have some sample data to attach it would help




basically i have two sets of data.
first set of data was extracted on 01/10/11 and exported to excel.
second set of data was extracted on 01/11/11 and exported to excel.

i have eight tabs in each spreadsheet.
average 100 columns.
average 3000 rows.

so i want an easy way to compare data, look at the data the delete it.


Are you going to add all the unique records from both sets?  And they are formatted identically?

If so then you could get them all on one worksheet, and then just use the data>Filter>Advanced>Unique Records only.

This seems like it would do what you want with the minimum effort.

Similar to conditional formating, but this works based on what you select.  It will check all the columns and hide any rows where the data in all the columns is identiacal to another row.  

Then I just take the visable rows and move them to a different worksheet using F5>Special> select visable cells only.  and paste them on a new worksheet.



not fully answered.