Data cleaning in Excel - 10 tricks *PROs* use all the time
123 Steps
106 followers
6 Video Views·Oct 18, 2022
Dirty data is a pain. Solve it with these 10 powerful yet easy ways of cleaning data in excel.
Watch this video until end to unlock two more bonus ways to clean data in excel.
Let's clean. Number one, first name extraction. Here we have some names and we would simply like
to extract the first name part of it. Now you could write some formulas or something but here
is a dead simple way of doing this. Just go to the adjacent cell and type the first name of the
first few items. As you start typing excel will guess what you're doing and automatically suggest
that remaining first names. This feature is called flash fill. Now when you see this kind
of highlighted values like in dull color just press enter and excel will automatically do the
extraction for you. You can undo this if you're not happy especially if you would like to tweak
the way this needs to happen. For example I'm going to show you how the same can be done for
last name. Now I'll say f-a-u-g-h-n-y fawny cross white and as I'm doing it already showed me the
last name as well but if there are some middle names somewhere you can go and tweak that and
excel will pick up the pattern and show this. You can also if the suggestion doesn't come up
type the value and press ctrl e to trigger the flash fill. You can find more about flash fill
from the data section, data ribbon and flash fill button. So that's number one. Number two,
number to percentage. Let's say you have some values in a range of cells and you would like to
turn all of these into percentages. You copied this from another system or from a web page and
they are coming up as 72 78 instead you would like them to be 72 percent 78 percent. Here is a very
very simple way to do that. Go to a blank cell and type 100 there right and then select that
copy ctrl c select your original range of values and then right click paste special
and from here you can say these numbers need to be divided by that 100. So in the paste special
area just click on divide operation. What this does is it takes the 100 and divides them with
all of those and you will instantly get the values as fraction. Now at this point if you
apply percentage formatting you will get the percentage thing done. But this requires two
steps. Let's show another technique. You can go to a cell and type one percent in there,
copy that one percent and then select these paste special and do a multiplication instead.
This will apply the percentage format. Number three remove blank rows. Let us say you have
copied some data from another system or you have done an import and you have noticed that some of
the values are blank and wherever there is a blank value you want to remove that entire row because
it's irrelevant data for your analysis. You can manually select and delete them but this is going
to take a while especially if you have multiple columns and hundreds of rows. Here is a dead
simple way. Select all your data and now press f5 function key 5 key. This opens the go to box
and from here click on special. Now using the go to special area you can select items that are
meeting a specific criteria. In our case we would like to select all the blank cells. So click on
blanks and okay. Now what excel will do is in the highlighted range it will find all the blank
cells and it will select them for you. Once you have selected press the shortcut control minus.
This is for deleting cells and wherever there is a blank cell we would like to remove the entire row.
So just say entire row and click okay. Your data is cleaned up all the blanks are gone. Number four
find duplicates. Let us say you have a set of invoice numbers that are provided by Ross and
another set of invoice numbers provided by Monica and you now want to see if there are any duplicates
between both lists. All you have to do is select the first list and then you can hold down the
control key select another list. Now that you have highlighted both lists just go to home
conditional formatting highlight cell rules and click on duplicate values. This will instantly
show you all the invoice numbers that are appearing in both lists in a different color.
A great way to find any duplicates between two lists. Number five clean up formats. Let us say
you have some data that you have downloaded or a colleague provided to you and all the information
is there but the format is all messed up. You don't have to go and manually clean any of this up.
You can simply select all your data and from the home ribbon there is a clear button. Just click on
that little down arrow and say clear formats. This will instantly tidy up your data and set it back
to the original raw formatting. From here you can format it in the way that you like. Now time for
our first bonus trick. Let us say you have some data where there is inconsistent spacing. So here
you could see that Barfani has an extra space at the beginning. Likewise Dennison Crosswhite has
an extra space at the end as well as at the space in the beginning and this person has some
inconsistent spacing. This sort of problem usually happens when you copy paste data into excel from
another system or a web page. Here is a very simple trick to clean this up. You can use
the beautiful trim function and select the value and close the bracket. What trim function does is
it will remove any spaces from the end as well as beginning and if there is any extra spaces
in the middle more than one space that will also be removed. Once you have that formula just drag
it down or go to the corner of the first cell and double click and it will automatically clean up the
values and give you data without those spaces. That's the first bonus trick. Number six. Unstack
data. Imagine you have copied or someone provided you some data where the data is not in the proper
order. So you have addresses where the street address city and state are in different cells
and they go like this and this kind of format is called stack data because everything sits one on
top of another and you would like to unstack it so that you get it like in this format. You may
think this requires a bit of complex formulas or anything but this is really simple. All you have
to do is note down how many values are there in each set. So we have three values street city and
state and set up your structure accordingly. So here I have three values so I have got state city
and street as three values. Now go to street and say equal to and point to that because that's my
street address. Enter. Go to city say equal to and then select the city. Go to state select West
Virginia and then now all of these are there you simply just drag this down. You will get everything
filled like this. So once this is there you select all of this control C copy and then
right click and paste them as values. So now what we did is we have taken the values and we have kind
of staggered them here but there is no more formulas it's just values. Once this step is
done while keeping it selected use our delete blank cells trick from the earlier. So press F5
special blanks and click ok. Now in the earlier trick we used control minus and then we said
entire row but this time we will say shift cells up because if I delete entire row I will use I
will lose this data as well. So we'll say shift cells up. This will clean up the data and set it
up in the correct order for us in an unstaggered format. Number seven duplicate combinations.
Imagine you have some data where name and department or three or four columns are there
and there is some duplicate combinations going on. So for example you could see that bar phony
in procurement has appeared twice and you would like to remove one such combination. You can use
just select all of this data go to data and you can use the remove duplicates option to remove
duplicate combinations too. This is not something many people know so let me demonstrate that.
Select all of your data click on remove duplicates and from here keep both options selected. This
means you are looking for a combination of name and department and click ok. It will remove all
the duplicate combinations and it will keep values as they are. Number eight remove yellow
cells. This is again a favorite especially in office situations where you have a shared spreadsheet
or a file that multiple people access to. Occasionally people would like to go and put
some yellow color or some other formatting on it to differentiate values. So here we have our
data with a lot of yellow highlighted cells. Select all your data and press ctrl f to find.
From here instead of finding any single value we want to find all yellow color. So click on the
format button and go to the fill section and select the yellow color. Now what we are asking
excel to do is find all the cells that have yellow color as their fill color in there and then click
on find all. This will identify all the six cells. So it will say six cells find or how many
ever yellow color cells are there in your data. Now select the first one hold down the shift key
and select the last one. This way you are now able to select all the six cells. So once you
have selected you can click on the excel window and press ctrl minus which is used to delete
cells and simply say delete the entire row. Click ok and your yellow cells are gone. Number nine
extract numbers from text. Here I have some expenditure items pulled out from a credit
card statement. Now the expenditure dollar amount is there in the detail but it is not
usually at the end. Sometimes it is at the end sometimes it's in the middle and we want to just
extract the number portion. This technique works very well if you have only one number in the
value. If you have multiple items then it can it won't work. So go to the expenses section and
simply type the expenditure for the first few items. So 49.08 and you can see that as I was
typing 49.08 excel flash will kind of guess what I'm trying to do. So it extracted but this was
not right. So you can see that 44.9 to spend it extracted that. So what flash will at this point
is trying to do is it is thinking oh you want the number I will get your number and from there on
everything so it's giving me that. So I can go to the third cell and then I can simplify
that criteria and once I teach that new behavior flash will then go and extract what I want.
So this is how I can extract numbers from text. Again a very powerful and amazingly simple
technique. The last one number 10 file name from food park. This works for website addresses or
anything email addresses or whatever may be the case. So here I have some file names
with their detailed path. So I have d column something something something something and then
the file name. Sometimes there is three or four folders sometimes there are five six folders
and all I want is just the last bit the file name. You can use find replace to do this.
You believe that let me show you select all your data and press ctrl h to open up the find replace.
Now if you already have that kind of yellow color formatting there you want to first
clear the formatting conditions and what we want to find is we want to find
anything that has a path in it. So I'm going to find star slash and then I want to replace it
with nothing. What this does is star is a wild card and what that does is excel will try to find
anything followed by a backslash. So it's going to find all of that things and it will replace
them with nothing and when you say replace all see the magic happen. It will instantly replace
the entire food park and just leave the file name there. So this is what it is. Now from here if you
would just like to extract only the file name without the extension the extension is something
that appears after the dot you can again use the same technique you can say I want to delete
anything after a dot. So dot star replace all that will remove the file name the the dot thing as
well and then leave there. Keep in mind that if your file name also has some dots in it then it's
going to mess up. Now time for our bonus two technique numbers formatted as text. This is
again a common big painful problem that you see in dirty data. Let us say you got some values here
they look like numbers but they're not really numbers because when I select the number and
select a bunch of them I only see the count. I'm not seeing the sum here. So excel is thinking
these are text values. Here is a dead simple way to convert those into numbers. Just select
any blank cell copy that and select your values. Right click paste special and from here just
add the blank cell to these values. Now by definition a blank cell in excel has a zero
value. So when you add zero to a text excel will see oh you're trying to add zero. Let me see if
I can convert this into a limit. So it will convert and then it will add the zero which
won't change the value but now you have your values converted into numbers instead of text.
So there you go the 10 powerful and easy ways to clean dirty data in excel. Thank you so much for
watching this video. I hope you enjoyed it. If you like this video please give it a thumbs up
and share it with some of your friends. My name is Chandu and my mission is to make you awesome
in excel. See you again in another video. Bye-bye.
