Welcome to excel champs in todays video we are going to learn about six powerful

methods to remove the duplicates from our data first of all we will use pivot tables

to identify the duplicates in our data set now here I have a small example

I have name of few students with the subject and the year of passing out

first of all I'll create a pivot in the same excel sheet I'll go to insert

00:30.3 insert a pivot table select existing worksheet and select a range here

00:35.2 and select the cell F22 and press ok we will put

the name in the rows than subject and than year

now I have put everything in the rows now this looks quite confusing

we'll go to desgin and change the report layout we'll go to show in the tabular form

now here I'll again go to design go to subtotals and

do not show subtotals now here I got the name of the students

the subjects and the year of passing out so these are my unique entries now lets

try this out I have a lsit of 13 students

and here I got the list of 10 students it means this data set contains

three duplicate values so this is how you can refine your data and get the

unique values using a pivot table option now second method is using

power query now first of all it is always advised to covert your

data into a data table how I'll do this I'll simply click anywhere

on my data and press CNRT+T from my keyboard and press enter

now this is changed into a table what I'll do is I'll click on the first cell go to the

data and click on from table range here my power query editor has

opened now there are two methods of removing the duplicates

first of all you can click on this table you will get a few options

and you can simply click on remove duplicates now here I got

my ten unique entries and my duplicate values which were three in count

have been removed now I'll again I'll remove this step again I got my

13 values now the 2nd way is you can select your data complete data set

go to home click on remove rows and remove duplicates

it has again removed my duplicates from my data set and I

got my unique values now I'll close close and load to table on

existing and I'll copy this here so I got my unique values

in the excel sheet now the third method is using the inbuilt function countif

or countifs now here we have only one single criteria so we will use countif

now to use countif first of all we will have to use a function called

textjoin as the name suggest this function will combine the given ranges

into one single cell first of all I'll use textjoin I'll define a delimiter

since I do not need a space so I'll leave this blank I'll put

true since I want to ignore my empty cells and than now I'll define

my range press enter now you see the magic range of converting this

data into a range I did not copy this formula till the end it was

automatically copied till the end now I'll use countif function in this range first of all

the array is range now my range is this column1 and what is my criteria

I just want to check if my this value is repeated in this given range

so I'll press enter again I got 1 this 1 means that

this entry is occuring once and when I select 2 this means that

this text is occuring twice now lets select the 2 these are my

repetative values now the next method is using conditional formatting now here

lets try this conditional formatting I am selecting my data go to home and conditional formatting I'll

select cell rules which are duplicates values now you will see it is going to

highlight my values which are repeted it has highlighted physics because it is

occuring twice or more than two times in my this column but this again does not make

sense beacuse I want the combination of these three cells which

combined are occuring twice so what I'll do is again I'll join my cells

using testjoin delimiter to than I'll select this range and press enter

so my cells are combined now we will use conditional formatting in this

combined cell I'll select my range conditional formatting highlight cells and duplicate values

you can always customize if you want to highlight these in red color

yellow green or you can also choose a custom color border or any kind of format

you like so here we will keep it default and press ok so these are the entries

which are occuring twice or more than two times in my data now the next

method is using the advance filters and go to data I have an option here

advanced which I belive most of you must not have used it

click on advcanced now here I have two options filter the list in place

or copy to another location we will try both the options in this video

first of all I am using filter the list in place and I am selecting this range

which is already selected you can see so no need to change it now I am just clicking on this

unique records only and press ok now you will notice my duplicate

entries are hidden it lies somewhwere between the row 32 and 36

only my unique values can be seen now I'll select this again press

Alt semicolumn cnrtl+c and paste here cntrl+v so

you will see that all my unique values are copied now lets select the second option

I am deleting this table I'll click on name go to data advanced now

I am using the option of copy to another location I'll click copy to another location choose my

range which is already selected copy to let me give this a new location

lets say its F22 unique records only and ok now this has copied only

the unique entries in my data set to my new given range now the last

option is the default option which most of us are using I'll select my range

and you have this option remove duplicates under data tools I'll click on

this this will ask for a confirmation that do you want to remove your duplicates

from all the columns or from a single coulmns since these columns are

interconnected with each other so I'll select all these columns and press ok

so it has removed my three duplicate values and it has found ten unique

names I hope you like this video please do not forget to like comment and share

thank you for watching guys