welcome to the one video that will take you from data newbie to data cleaning Pro on Excel using
eight steps let's go so let's suppose we're analysts on Amazon and we've been given this
data set over here which you can actually download for free in the video description so you can see
we have all of our customers here including what brand they bought from and the price they paid for
it and so our manager would like us to clean this raw set of data before we send it to him
as the first step let's work on separating the contact into the first name and the last name
so you can see over here that we have it with an underscore tied together so what we're gonna do
is add two more columns so control space for that and then Ctrl shift plus Ctrl shift plus again so
one of these is gonna be the first and the second one the last name so as you can see over here we
need to somehow separate it and for this we'll use the text to columns feature now to do so
we're just going to select all of the contacts here so just Ctrl shift down all the way till
you get to the bottom here so around row 26 it should be then we're just gonna go over to data
from here we'll go to text to columns that you can find right over here under data tools so just
click inside that and I know this looks a bit daunting but just go ahead and hit next for now
and so over here the delimiter is basically gonna be the criteria that we separate by in our case
we seem to have this underscore all around so we probably want to go with that we're just going to
click on other there and here's where we'll type the underscore once we do that you can
see down below how it's gonna get separated like so so we'll just go to next from there and then
as a destination let's put it under here where it says first and hit enter there once we have
that we're just gonna go to finish and just like that you can see that it's now been separated into
first name and last name awesome now that looks a lot cleaner after that let's get going on the
brand column so if we look over here you can see that we have all of these different brands but
actually when we look closely at them you can see that Nike Nike Inc and Nike UK should actually be
just labeled the same thing as after all they're all Nike so if we wanted to get the sum of all
the Nike sales well this wouldn't be accounted for and so it wouldn't be entirely accurate so
we need to somehow try to clean that up so the first step we'll do here is try to find out
which exactly are the unique ones so we're just gonna use the unique formula so equals unique
hit the top key there and we're gonna select all of the brands from here so Ctrl shift down make
sure you reach all the way to the bottom here and then hit enter and just like that you can see all
the different ones that we have and so Knight for Nike you can see that we have Nike Inc and Nike
UK which should just be an ikey now to be able to work with this one we're gonna actually just hit
the Ctrl H and what we're gonna do here is this is going to replace things for us so wherever it says
Nike Inc we want it to be replaced with just Nike and same thing goes with Nike UK so find what we
say find Nike Inc make sure you put the dot in the end and we want to replace it with just plain Nike
and we're gonna go to replace all just below over here just click on that and you can see
it's made one replacement we'll hit OK there and same thing goes with the Nike UK so find
what we're gonna change that to Nike UK and replace with Nike hit replace all
hit okay there and hit close and that should do it for us we can now go ahead and delete this
whole column over here so now if we go back to the brands over here you can see that it's been
replaced to just plain Nike instead of being Nike Inc or Nike UK now that we've cleaned up
the brand part let's take a closer look at the products and how that can be cleaned up as you
can see over here it's looking quite messy in that there is capital letters in somewhat inconsistent
places so it makes it kind of hard to read so for this we're just going to create a new column here
so control space and then Ctrl shift plus here let's name this product as well product clean
say and down over here what we're going to use is a simple formula called proper so equals proper
you can see the description for it here and what it's going to do is it's gonna convert this touch
that we only have a capital letter as the first letter Within A Word so hit the top key select
this and just hit enter there and you can see that it just has a capital letter on the m and the s
and just double click that so it goes down all the way to the bottom here
next up we've got the location of each customer and as of now it seems to be
that there's a ton of different spaces around them so we want to also try to clean that up
so we'll go over here and select a new column again so control space and then Ctrl shift Plus
here let's name this location clean and what we want to do here is use the trim function which is
going to remove all of the excess spaces that seem to be the case all across the spine here so we'll
go equals trim hit the top key there this is a text we'll close it and hit enter there and again
we'll just double click here once the cursor is kind of locked here and just double click like so
and if you're liking this exercise and want more extensive Excel material you can check out our
Excel for business and finance course where we teach everything we know about Excel specifically
for people either looking to break into a business or a finance role or those any trying to level
up their Excel game and what makes this course different is that it's all applied to the real
world while we still cover the theoretical lessons like formatting formulas and charts we also offer
case studies that simulate the type of work you might be assigned in your day-to-day ranging
from Financial modeling to cleaning a complex data set and presenting some visual insights
on top of that we also offer several other courses including PowerPoint valuation and more so if
you're interested in checking it out you can go to the link in the description below alright back to
the video now that we've worked on the spacing issue with the location where we had too many
spaces let's now work to separate the city name and the country name for further analysis so over
here we're just gonna press Ctrl space and then Ctrl shift plus and Ctrl shift plus again and
so here let's say we go by City And then we're gonna go by country and this time around instead
of using text to columns like before we'll use the Flash Fill so here I'm just going to type London
and over here I'm just gonna type UK from here all we need to do is hit Ctrl e and Ctrl e again
and just like that you can see that we have all of the Cities Auto populated and same thing goes
with the countries over here next up let's work on the Fulfillment column and actually
making it a percentage as you can see over here under fulfillment percentage we just have the
number as of now so what happens is that when I change this to percentage format you can either
go to home and then click on that percentage or the shortcut is just alt HP you can see that it
gives us three thousand percent which isn't exactly accurate instead it should just be 30
so press Ctrl Z there to go back to how it was we're gonna need to fix this so to do so we're
gonna use uh basically we're just gonna have to put 100 over here to the side and then what we'll
do is once we have that 100 selected we press Ctrl C and down over here under fulfillment percentage
we're just going to select all of them make sure it's all the way till the end there so till row
26. what you want to do is go to paste special so go to control alt V and you can see that the
paste special pop-up shows up over here what we want to do is go under operation and we want to
multiply it we want to divide it sorry by 100 so just click on that divide there and hit OK
now you can see that we have it formatted the right way to switch to percentage so now when
we just do alt HP to switch the percentage you can see that it's now looking accurate moving on
to one of the last steps and over here under price you can see that as of now so we have all of the
prices but there seems to be something odd in that when I select multiple you can see that we don't
actually get the sum or the average like I might when I select all of these over here to the side
you can see that we got the average and the sum and so to test that out let's say I go equals hit
this price and save plus one and oddly enough it gives me a value sign and that seems to be because
this as of now is not formatted as a number so we're gonna need to manipulate it so first things
first you can see that we have a comma in there that should actually really be a DOT instead of
a comma so we're gonna go equals substitute so we'll basically replace the command for a DOT
so hit the top key there let's just move this to the side a bit and this is gonna be the text
comma and so in the old text all we want to do is in quotations
we're gonna put the comma there and then hit a comma this time to go to the new text under
a new text quotations again and we'll put a dot then we're just gonna close the quotations and
hit enter so you can see that we went from the comma to the dot let's just drag that down for now
and there is one more step that we need to take as you can see if I highlight these two I still don't
get the sum over here or the average so what we're gonna do is this time around go to value
hit the top key and so this is a text and let's just hit enter there and this time it should be
as a number so we'll drag that down and now you can finally see that we have the average and the
sum calculated as it should be the case and one final thing you might have noticed is that we have
some rows with blank data so let's look into that but first before we get into there let's
actually label these two so let's say I label this one price one and this here price final
so once we have this if we scroll all the way to the right here to the left sorry you notice that
we see all of these that are blank same thing goes up over here now the reason this is problematic is
because maybe the data here is not reliable let's suppose that with the make the decision that we
want to delete those rows that have some missing data so we can't really work with them that well
now we could go one by one try to find it and delete it but the problem is if this
is a very large data set then that's obviously not very realistic so instead what we'll do is
first select the entire table by hitting Ctrl a then we'll go Ctrl g under this go to popup
that should show up for you we're gonna go down towards the bottom where it says special
so we'll click on that then over here we want to identify the blanks so that's going to be this
thing over here make sure you click on that hit OK from here you can see that it's highlighted
all of the blank areas for us once we have those we can simply press Ctrl minus under delete here
we won't just we don't just want the shift cells left what we want is the entire row to be deleted
hit OK there and just like that we're not going to have any more blank cells so all our all our
data here we can actually work with now that you know how to clean the data you should learn how to
visualize it with this video over here to create an awesome dashboard or this other link over here
to check out our Excel chords hit the like And subscribe and I'll catch you in the next one