Quickly Remove Blanks and Duplicates in Microsoft Excel

hi there jamie here today at teachers  tech hope you're having a great day today   today i want to show you how you can  quickly remove blanks or duplicates   whether it be in rows or cells in microsoft excel

so the first thing i want to show you is how to  delete empty or blank rows in microsoft excel and   you can see in this data set here i have some  blank rows now you could go through and select   each row here hold down control go to the next  one go to the next one if you keep holding down   control and then you could right click and delete  them all that way would work but if you had a long   data set that you're going through it's a very  inefficient use of time so a better way to do is   go up to your home tab and if you go all the way  to the very end you'll see find and select in the   ribbon so just select that and then go to special  just like that now i want to point out a shortcut   that you can do at the same time i'm just going  to close this if i use control g it this opens up   and then you can just click on special and  you get the same spot so if you like using   shortcuts try the control g now once you're into  this go to special you can see that we have blanks   right here so i'm going to click on blanks and  i'm just going to hit ok so what it's going to   do is it goes through and selects all the blanks  in this spreadsheet here now all i have to do   is go up to my ribbon under the home tab and go to  delete so once you find delete you can drop down   and it will work in a couple different ways  usually if i'm just trying to delete rows i'll   go to delete rows but you can also delete cells  and shift everything up but in this case i'm just   going to hit delete sheet rows just like that and  you can see now all my empty or blank roll rows   and this spreadsheet in excel are gone so that's  a much quicker way to delete these blanks so for

this next tip i want to show you how you can  replace blanks with a different numerical value   so what i mean by this is i'm going to go ahead  and just delete around five of these to give some   data to change here so that should be about five  through here now what we're going to do is be   able to select all the blanks in this column and  then quickly change them to zero i could change   it to any number but in this case we don't want  to if they are supposed to be blank being zero   we just want zero in each of all these so what  i'm going to do first though is to find the   blanks in this column i need to select the column  first use my control g like i did before   you can go to the find and select hit special and  go to blanks again so i'm going to hit ok and now   you can see that all the blanks are selected there  the five of them if i just type zero just like   that and then hit control plus enter look how that  fills all the empties in with zero so i could have   did this done this with a different number if you  know it's the same number that you're putting in   so now i remove those blanks and put a different  numerical value on it so give that a try   you don't have to use numbers either if you  wanted to write something in you can do the   same idea and just use the control enter and  it fills in also with the letters so the last

thing what i want to show you is how to remove  duplicates inside microsoft excel so in this   data here i did put a few duplicates so you can  see if i look at 25 i have baker mayfield here and   i actually copied the whole row over and i got it  again and there's two others that i did that too   so there should be three in total so let's say you  want to make sure you don't have any duplicates in   your data what you need to do is go up to the data  tab right here and if you're looking through the   ribbon move over until you find remove duplicates  right here so i'm just going to go ahead and   select this now what you need to do is in this  case so i want to make a match on every column so   it's going to go through here and then it's going  to make sure that's that duplicate but if you knew   it was only a certain one column you would uncheck  these my data has headers up there too so i'm just   going to go ahead and hit ok and it said three  duplicate values found and remove 50 unique values   remain so if i hit ok and then if i start to go  through it if i look at 25 and then i move down   to 40 you can see that other baker mayfield isn't  there and the two others were deleted as well   so i hope you like these quick tips about cleaning  up your empty rows or cells or replacing numbers   into different cells or finding those duplicates  it can save you a lot of time if you use these   techniques when going through let me know what you  think with this and down in the comments thanks   for watching this time on teachers tech i'll see  you next time with more tech tips and tutorials