How to find duplicate data in Excel 2010 and 2013 spreadsheets [Guide]

If you have an Excel spreadsheet with lots of data it might take a while to manually check through it for duplicated values. One way that you can quickly find duplicated cell values is with the Conditional Formatting option. When selected, the conditional formatting highlights any cells that include duplicated numerical values.

To add the conditional formatting to the whole spreadsheet, select the small box above row 1 and to the left of column A. That selects all the spreadsheet cells as in the shot below. Alternatively, select a smaller group of cells by left-clicking and dragging the mouse over them.

conditional formatting

Once you have selected some cells, click on the Conditional Formatting option. Then click Highlight Cell Rules and Duplicate Values. That opens the Duplicate Values window below where you can choose the conditional formatting for the cells.

conditional formatting1

There you should select Duplicate and one of the formatting options such as Light Red Fill with Dark Red Text. Or you could select Unique cells instead which will add formatting to all those cells that do not have duplicated numbers. Click Custom Format to open the Format Cells window below where you can select further conditional formatting options.

conditional formatting2

Then click OK to close the window, and some of the formatting of the selected cells might switch to alternative colors as in the shot below. If you selected Duplicate from the Duplicate Values window, the conditionally formatted cells have duplicated values in them. Remove the duplicate values to erase the formatting.

conditional formatting3

The Duplicate Values option is just one of the great Excel conditional formatting options. With it you can find and edit all the duplicated data in your spreadsheets more effectively.

Share this post

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

1 comment

  1. David C

    Thanks, Matthew, for this article.

    I’d like to say that I usually use the CountIf formula to identify duplicates, which can then be sorted. I’ve also done this with large workbooks surpassing 50,000 rows, which makes the sorting option invaluable.

    If you want to compare the data amongst itself, then you first have to duplicate that column or row simply by copying and pasting, and/or with the “Insert Copied Cells” option.

    1. Once you have two different columns or rows, click on the first empty cell where you’d like the results displayed and click on the Insert Function icon.
    2. Search for and double-click on the CountIf formula.
    3. Click on the cell selection icon for the Range and select the first column or row that you’d like to compare your data with.
    4. Click on the cell selection icon for the Criteria and select the first cell that contains your data, which should be parallel to the cell containing the CountIf formula.
    5. Copy and paste, or drag and drop, the cell containing the CountIf formula next to each of the remaining cells that contains your data.
    6. Copy the results and paste them into Notepad to remove the formulas and then paste them back into their original cells.
    7. Now you can sort all of the rows or columns by the CountIf formula results, which will be the “number” of times your data was found within the Range.

    By the way, this is one way to briefly bring Microsoft Excel to its knees and beg for mercy, by maxing out your CPU for a short time, if you perform such a task with large amounts of data, as I often do. I laugh every time.

    Another option is to use the Remove Duplicates tool, sometimes after performing the process mentioned above, but keep in mind that the Remove Duplicates tool also removes all but one unique value, which can be quite useful for building summaries of your data.

    Maybe there are simpler ways, but once I discovered the CountIf formula option, I’ve used it ever since.

    I hope this helps.

    David C