Excel: Remove Duplicates

basic data analytics1To remove duplicate values in Excel, follow the steps below.

This is the first post in a series of basic data analytic procedures using Excel. If you work with data regularly, these procedures will help you understand your data better and analyze it faster.

I started this series because I am asked how to do these tasks, sometimes by experienced contractors and auditors.

These procedures are written for Excel 2013, but the same steps should also work in Excel 2010, but the screenshots might be a little different.

Why Duplicate Transactions Matter

Knowing whether a file contains duplicates is important for the following reasons:

  • If you total a column of numbers for all records, duplicate records will cause the total to be incorrect. Likewise, if you’re trying to determine the percentages of IT auditors vs financial auditors in a list, duplicate records can affect the percentages.
  • Having duplicates may indicate the system is processing or exporting records incorrectly.
  • Duplicate transactions may be an indication that users are making errors or that fraud is occurring.

Removing Duplicates Across All Columns

To remove duplicates across all values (columns) in an Excel table:

  1. Click inside one of the cells in the table from which you want to remove duplicates.
    In Eample 1 below, I clicked in cell A2 (to enlarge an example, click it).
  2. On the Data tab, click Remove Duplicates.
    See Example 1 below.

    Example 1

    Example 1

  3. The Remove Duplicates dialog box appears. Since you want to remove rows/lines that are duplicates across all columns, leave all the columns checked, and press OK.
    See Example 2 below.

    Example 2

    Example 2

  4. The duplicates are removed. Notice that Excel tells you how many duplicates were removed and how many unique values remain.
    See Example 3 below.

    Example 3

    Example 3

Removing Duplicates Across Specific Columns

If you are interested in removing duplicates only in specific columns, leave only those columns checked.

For example, if you wanted to remove all duplicate positions from the list, you would leave only the Position column checked (see Example 4 below).

Example 4

Example 4

When you press OK, the list would appear as follows (see Example 5).

The first IT auditor, Finance Auditor, and Operations Auditor would remain, and all others would be removed (if only getting rid of bad auditors was that easy).

Example 5

Example 5

See all the posts for these series at Excel: Basic Data Analytic series.

9 Comments

Filed under Audit, Data Analytics, Excel, How to..., Technology

9 responses to “Excel: Remove Duplicates

  1. John

    I wish it was a bit more robust, first finding the duplicates and then prompting you for what you wanted to do with them. Sort of a blend of data filter & remove duplicates … filter duplicates.

    Like

  2. Good series idea. I am amazed at the amount of people who do not know how to use excel, but even MS word seems to be a problem. When I review reports doing things like repeating table headers, using header tags to create a TOC, making tables repeat across pages, etc. is just something people never learn to do. Maybe I’ll do a few write-ups on “style”.

    Also, I’d love to see a post or two about creating excel add-on ribbons. I’ve never made my own, but I’ve installed and used many of them.

    Like

    • Christian,
      I’m amazed too, but I did audit for a year before I learned vlookup. In my case, I just didn’t know any better. I get frustrated when you show an auditor something as basic as vlookup and he refuses to use it. I’ve found that’s usually auditors that are 35+ years old.

      Yes, most people don’t know Word, but I did a lot of publishing documents in my early years and I learned Word pretty well. The best trick that most auditors don’t know is that Word can delete the first couple characters of multiple lines without deleting the rest. Very handy for cleaning up data in a pinch.

      I’ll do a post on that some day with examples.

      I like your style idea. Go for it. You guys sure are putting a lot of info out there. Wow. Where do you find the time….

      Like

      • John

        One of my favorite audit stories is a guy that would spend a day a week summarizing payroll data (I didn’t know that). He sent me the data and I replied 5 minutes later with his errors. When I showed him pivot table, he exploded… true story. He felt so dumb, I actually felt guilty. It made me really wonder if I would do more for the company simply looking for those opportunities. A fifth of his year was spent on manually building a pivot table.
        Christian, I didn’t see a plug, are you in the blogroll?

        Like

  3. Pingback: Excel: Identify Duplicates | ITauditSecurity

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s