In this post we are going to discuss about different methods to remove duplicates in Excel, which includes static (User interface) and Dynamic formula solutions. Let's get into the topic.
Below is the list with duplicates, that we are going to work with.
Method 1:
Simple, time saving but static method.
Short cut key to remove duplicate is
Alt + A + M.
This method will replace the existing data with unique list, so better copy the original data and paste somewhere else before doing.
In our case I copied the data into D3 cell,
Select D3 cell and press Ctrl, Shift, Down arrow to select the whole range, if there are any blanks in our range, this selection method will stop before the blank cell, so just to make sure that we selected the entire range, press Ctrl, period or dot to switch the active cell into other corner of selection, now drag the scroll bar down to check the presence of data after our selection. Once we ensure that entire range is selected then again press Ctrl, period to get back to top of range. Now we can use short cut key Alt + A + M.
Now remove duplicates dialog box will open, check the box "My data has headers".
Because in our case we do have header as "Excel experts".
Bonus Tip:
In our case you might have noticed that "My data has headers" box already checked, that's because we kept excel experts in bold format, because of the format change excel understood that this is the header, if the data type differs between the top row and the other rows of data, excel will consider the first row as header.
In left side pic, excel will understand that "Month" is a header, because of the difference in data types.
In right side pic, excel not able to find any difference and will let you decide on header.
Back to the topic,
Once we check "My data has headers" box, then the range is selected excluding the header,
now press ok. Now, message box will appear as shown below and press ok.
Now we can see that 6 duplicates are removed, and 7 Unique values remain.
Method 2:
Simple, time saving but static method - Advanced Filter.
One advantage of this method is that we can replace the existing data, or we can copy the result in some other place.
Short cut key for this method is,
Alt + A + Q
Select the range where you want to remove the duplicates and press the key Alt + A + Q,
Below dialogue box will appear,
"Filter the list in place" will do the process just by replacing the existing data.
"Copy to another location" will paste in specified range. Click that to activate
"Copy to" option.
List range is already picked by excel, if not click on the up arrow button and select the range.
Criteria range is not required for our case and so ignore that, To select the range where we are going to paste the Unique values, click on the up arrow in "Copy to" option and select the cell, in our case it is H3 cell and then press enter.
Now again the Advanced Filter dialogue box opens, in that check the box for "Unique records only" and click OK.
Method 3:
Dynamic spilled array formula solution - Unique.
Dynamic, time saving and robust.
But you have to have Excel 2021 or above version to have this function,
The arguments of Unique function are,
=UNIQUE(array,[by_col],[exactly_once])
remaining arguments other than array is optional, so we can ignore that.
In the array argument, feed the range to remove duplicate and close the parenthesis and press enter. You will see the same result as earlier. This will update automatically if the data changes.
Method 4:
Classic method using INDEX function to remove duplicates.
Dynamic but requires press Ctrl + Shift + Enter (CSE) in Excel 2016 or earlier version.
Once you press CSE you can see that formula is housed with curly braces {}.
This is Excel 2021 version and so I didn't press CSE. In L4 cell I used the below formula,
=INDEX($J$4:$J$16,MATCH(0,COUNTIF($L$3:L3,$J$4:$J$16),0))
Let me de-construct that into pieces, so it will be easy to understand.
First, we will look into the Countif part, it has 2 arguments which is Range and Criteria.
Range argument fed with $L$3:L3, as you could see that this is the expandable range in which first L3 is locked in both row and column, so it is absolute reference. If the formula dragged down, this reference will remain same. Second L3 is not locked, so if the formula dragged down one cell, then this reference will show L4, this is relative reference. If we evaluate the range argument by pressing F9 key (do not forgot to press Esc key after evaluation otherwise it will hardcode the evaluated numbers)
the result will be as shown below.
Criteria argument fed with $J$4:$J$16 all locked so absolute reference, which is the names of all excel experts with duplicates. So, let me tell you how the count if function works,
It will pick each criteria and counts how many times the criteria exists in the range. In our case, if we take our first criteria "Mike Girvin" exists zero time in "Excel experts".So it will throw 0 in the first position, In the same way it will throw the count of each criteria. If we evaluate whole of Countif it will look like this,
When we match 0 in this array it is in the first relative position and so Match function will throw the value 1 in to row num argument of INDEX function, Thats how it gets Mike Girvin at L4 cell.
Comments