“There’s more than one way to skin a cat,” one of my instructors would often say. While the instructor who frequented this phrase may be a sick freak, the memorable phrase rings true for many situations in life. It absolutely applies to excel problem solving where different strategies or formulas can be used to address a data-related challenge.
Fortunately, today at Excel Shortcut no cats will be skinned. However, we will seek out three ways to address our problem, Identifying duplicate data. Duplicate data can exist in a system or Excel workbook for a multitude of reasons.
For this exercise, we will fabricate some check number information with duplicate check numbers included. “Duplicate payments? I see nothing wrong about that!” you may be thinking. I’ll pretend I didn’t hear that. For this exercise let’s suppose you are working for the payroll department where duplicate payments can get someone in trouble.
The method used to identify our duplicates may depend on the detail level we want to view. Here is our sample information. Can you quickly spot the duplicates? It’s okay if you can’t yet. We are going to learn multiple ways to do this very shortly.
The first instinct of many excel users is to sort by column and eye scan the numbers for duplicates. Although this approach can technically work, do you really want to sift through all the records? Imagine if there were hundreds or thousands of rows to consider. More rows to scan also increase the margin of error. Let’s save yourself the eye strain and find a better way.
Conditional Formatting
Let’s begin with the most common method for discovering duplicate values; conditional formatting. Conditional Formatting has a wide range of applications uses to sort or display data in a helpful way.
In the style section of the main toolbar find and Select the “Conditional Formatting” option. Navigate to the last option and select “Duplicate Values”.
The Duplicate Values Dialog Menu will appear and ask your preference of duplicate or unique values along with the method of formatting them.
Now you should be able to easily distinguish the duplicate values from the rest of the values. Sorting by check number makes the duplicates even easier to spot.
Unsorted
Sorted
Organized data is the best data!
CountIF
On Excel Shortcut, we aim to use formula based approaches to solve challenges. Let’s investigate if the COUNTIF Function can get the job done. To start, make a new column (E) called duplicates.
In the newly created column, enter the formula =COUNTIF($A$3:$A$17, A3). You are using the COUNTIF function to Search a range for a value, Cell A3 in this case. The value returned in the cell is the count of instances where this value is found.
Ensure that the cell references are absolute for the column you are searching. Use the F4 shortcut to quickly switch the cell reference from relative to absolute.
The information above accurately reflects the number of duplicates but is lacking detail. Keep reading to learn how we can gather more information on our duplicate data.
Pivot Tables
Since it doesn’t feel right leaving you with only two ways to skin a cat, I mean.. find duplicate values in an Excel Worksheet, lets read into the third and most powerful way to research duplicate cells.
First, we will remove the duplicate lines in Column E. Let’s Rename it “Tally” as this will be used to tally up checks with the same number.
The next step is to highlight the entire data set. Click on Insert and Select Insert From the “Tables” Section of the Menu Bar. This will bring up the Pivot Tables Dialog Box.
In the Dialog box skip down to the section where you select the placement of the Pivot table. Check the Existing Worksheet and click on a cell where you prefer to see the data.
Now it’s time to arrange our Pivot table. In the Rows area, drag in the check number, and in the values section, drag in the Tally information. Make sure the Values section is set to “count” instead of the likely default “sum”.
From our Pivot table, we gather that there are two distinct check numbers listed multiple times. Unlike earlier when we used the COUNTIF function, the Pivot table gives us the ability to drill down further into the data.
Click on the “Count of Tally” 3 next to 123572 to reveal each line in violation.
It appears our check paid out to Homer Simpson was recorded or possibly just listed twice. Digging deeper also revealed the Check number 123572 was assigned to a completely different employee. Both instances should be investigated in the system where the data originated!
Hope you find at least one of the three methods outlined above useful. Drop a note in the comments if you have other ways of checking for duplicate information.
