Master Data Cleaning Essentials on Excel in Just 10 Minutes

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

Channel: Kenji Explains

Views: 61,962

Keywords: data cleaning, excel data cleaning, clean data in excel, excel format raw data, excel remove duplicates, excel find and replace, excel trim, excel iferror, excel proper function, clean up data using excel, excel for data cleaning, text to columns excel, excel data cleaning for beginners, learn data cleaning, master data cleaning on excel, removing duplicates on excel, remove gridlines excel, excel format table, autofit rows excel, autofit excel, autofit columns excel

Id: jxq4-KSB_OA

Length: 10min 15sec (615 seconds)

Published: Sun Jun 11 2023