in this video we'll go from having a raw data cell like this to converting it into a clean Excel file
in just 10 steps so let's get into it and you can download this exact same Excel file in the video
description for free so over here you can see that we have the data set this is the row one basically
but before we make any changes to it we should probably save a copy so you can either save a
new Excel file or just save this sheet by clicking the control key and just dragging that sheet so
you have two separate ones alright now we can get started with step 1 out of 10 and first you can
see here that these columns are just too short if I stretch them out the number looks okay but if I
close it down you get this sign over here same thing with the rows some rows are just too long
like this one right over here so let's go ahead and refit this by just going to control a that's
going to select the entire table for us and then we'll go over to the side where it says format
and you can autofit the row width and the column width now the shortcut for
these is just alt h o i that's gonna auto fit the columns and then alt H
O A to Auto filter rows now you can see that we can read everything a lot better
now that we can start to see the data set a bit better you notice that the client names are very
very long so let's suppose that we would like to shorten it such that we don't have anything
within these parenthesis so for this what we can do is select the whole column you can just click
up over here and then we're gonna go to the replace tool so that's going to be over to the
side under find and select and just clicking on replace there the shortcut for it is simply Ctrl H
and So within it what we want to find is anything within the parenthesis but what makes it hard here
is that there's sometimes of different lengths so what we can do is just put the parenthesis sign
use this asterisk and close apprentices that basically means that anything that's within a
parenthesis is going to remove so replace with nothing then we're gonna hit on replace all
hit OK there and just close and now you can see that's looking a lot better we can double
click here to resize continuing on with the client names and let's suppose that we want this to be in
lowercase so they're a bit easier to read what we can do is First add a new column so we'll go ahead
and select this column D and just hit Ctrl shift plus and now that's a new column for us we'll call
this a client again and then the function that we're going to use is called Lower so equals
lower and it converts all the letters and look into lowercase so we're gonna go top there and
wanna convert all of these letters over here so we'll just hit enter there on the first one
and then we can double click here on the side to just drag it down all the way to the bottom there
now because we have the same column twice we could just delete this one so hitting Ctrl minus but the
problem there is that it all breaks and the reason for that is because it's referencing this column
over here so first we're gonna need to paste these as values instead of as a formula as they are now
so we'll go Ctrl shift down to select all of them Ctrl C and then we're gonna paste these as values
so down over here you can see that we have a lot of different pasting features and we want to paste
it as a value which is this one right here the shortcut there is alt H VV now we can go ahead
and remove this column so Ctrl minus next up in number four you can see that we have the
different contacts but the problem is here they have some very odd spacing and sometimes they're
capitalized sometimes they're not so let's go ahead and make that change so we'll go ahead and
insert a new column here so control space Ctrl shift plus we'll call this one contact again
and so first let's say we want to remove the spacing we can simply use the trim function
hit the top key there and we'll just select this and you can see that it's removed all
that spacing problem and similarly we have what's known as equals proper
hit the top key there and just select that and what this one does is that it only puts a capital
letter at the first letter of each word so we can now just merge these two together so we've got the
property there but at the very front we're gonna add the trim hit the top key there and then we
need to close the parenthesis for both and just hit enter now you can see that it looks cleaned
up and we'll just double click there to drag it down and then again we need to paste it as a value
so we'll select the column by hitting Ctrl space Ctrl C and then alt H VV that's going to paste it
as a value there now we can remove this column over here by hitting Ctrl minus and if you're
liking this video and want to level up your Excel skills you can consider checking out our Excel for
business and finance course and what makes this course different is that it's all applied to the
real world while we still cover 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 data set and presenting some visual insights
and if you get stuck along the way you can always ask us the course instructors any questions on
the discussions Forum we also offer several other courses including power bi VBA and macros and more
so if you're interested in checking it out go to the link in the description below alright back
to the video moving up to step 5 and you can see over here that the department is split into the
department name and the region so we would like to separate this into two different columns so
let's go ahead and add a new one just by hitting Ctrl shift plus and let's call this one the region
from here what we can use is this trick called text columns so first let's select the whole
area there so Ctrl shift down to select all of these then we're gonna head over to data
text to columns so this one right here and then we're just gonna hit on next
and here you can see basically this is what it's going to separate each of these columns
by and in our case it's that underscore so we want to head over to other and just type an
underscore in there and you'll see under preview what that currently looks like would hit on OK
there and then the destination where we want the output to be we want it to be right here in E3
once we're all okay with it we're just gonna hit on finish there and you can see that it's
split it into two separate columns now in Step six before we move any further we should try to check
if there's any duplicate values so for this we can select the whole table by hitting Ctrl a and
then we're gonna go over to data again and it's gonna be this icon right here that says remove
duplicates so just click on that we're okay with these our data does have headers and just hit on
OK and it says that it's found three duplicates and it's removed them so that looks all cool
alright now in number seven let's move on to the next column which is going to be the payments one
in this case and you can see here that we have some issues in that there's some blank cells now
for this it would be nice to not have it as blank and maybe type something like an N A but suppose
this is a very long data set and so it's going to take a lot of time to go one by one instead
what we can do is select the whole table so Ctrl a then under home we're gonna go over to find and
select go to special and within this we want to select all of the blank cells so we're going to
go over to Blanks and just hit on OK and you'll notice there that it selected the blank ones
for us from here we want to add an N A Sign so we'll go up over here under the formula bar just
type an A and then it's very important that we hit the control enter and not just enter there
and you can see there that it's changed all of these into n a for us
great now moving on to step 8 and over here under column J you'll notice that we have this formula
which is simply The Profit divided by the revenue the problem is down over here because we've got
text on one side it doesn't quite work and it gives us this error sign so to work around that
we're gonna use the if error formula so right here at the very front we're gonna type if error
hit the top key there and the idea here is that if there is no error it's gonna do the normal
calculation we'll hit the comma there now if there is an error we need to give it an alternate result
which in our case is the n a which we need to put in quotations because it's a formula
so we're gonna put it in there close the parenthesis and hit enter
and then we're gonna drag that down awesome now we can see how those signs have changed to an
a for us moving on to step 9 and now that we're happy with the values let's go ahead and format
the header row so we'll select it by hitting Ctrl shift right let's say we want a bold end so Ctrl B
and we can also change the highlight colors let's say we go for a dark blue and we change
this over here to a white so we can see the header there and finally in Step 10 we can get
rid of these grid lines as you can sometimes be a bit distracting we'll head over to view there and
just click on gridlines for that the shortcut is alt w v g that's going to activate it again for
me so let me just remove them there awesome now that we've cleaned the data The Next Step would
be to create some visuals out of it and you can learn how to do that with this video over here or
by taking our course over here hit that like and that subscribe and I'll catch you in the next one