Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
In the previous lesson, we saw how we can import a folder of files and also a text file into Excel.
And it's this text file that we're going to clean up and prepare for analysis.
Now if you find that when you are opening these files, you get this security warning running across
the top in yellow, then you can simply click Enable content is just because we've created external
links when we've imported this data.
So I'm going to click Enable content and I'm going to say, do not ask me again for network files.
So now we have this data set.
And the first thing we're going to deal with here are these blank rows.
I'm also going to show you how to handle blank cells and how to remove duplicate entries.
So let's start with the blank rows, first of all.
Now, blank rows in general can cause problems when you're analyzing your data.
For example, if I want to put this data into a pivot table, then start creating things like charts.
If I have blank rows in there, it's going to throw off my data.
So you always want to make sure that you remove any blank rows that you have in your dataset.
Now, if you have a very large dataset and you have quite a lot of blank rows in there, it's going
to be pretty tedious to go through and try and delete all of these manually.
So I might decide I want to select these two rows, hold down control, select these to scroll down,
select them all.
That's not particularly time efficient.
Fortunately, there is a quick way to do this in Excel, so all we need to do here is click somewhere
in our data, go up to the home tab and then all the way over in the editing group if we go to find
and select and then go to special.
And this is going to open this little go to special dialog box.
Now, if you're interested in keyboard shortcuts, there isn't a keyboard shortcut to go directly.
To go to special for what you could do is press control g and then click the special button.
Now what this allows you to do, amongst other things, is select different items within your spreadsheet.
One of the options that we have here is to select all blanks.
So let's select that click on OK, and it highlights all of those blank rows.
So now with them all highlighted, I can go up to that home tab again into the sales group and using
the Delete dropdown, I can delete sheet rows and I've deleted all of them in one go.
So that's going to save me a lot of time.
Now, another thing you want to make sure you deal with in your spreadsheets are blank cells.
Now, I don't have any blank cells, but if I did, let's just delete a few things out of some of these
cells.
And let's go like, that's like, so now again, blank cells can cause a bit of a problem.
It's always better to have some kind of numeric value in these cells, even if that is just a zero.
So once again, if I want to select all blank cells in this particular column, all I would need to
do is select the Column Control G Special and I can select blanks and click on OK.
And that will select all of the blank cells just in the range that I specified.
So now I can enter a zero into all of them in one go.
So all I need to do here is type zero control enter.
And that's going to put a zero in all of those blank cells.
I haven't had to scroll through and do them all individually.
So again, that is a real time saver of a trick.
And the final thing I want to show you here is removing duplicates from your dataset.
So it might be when you import your data and some duplicates come across for whatever reason.
Now, when I say duplicates, I mean an exact duplicate of every single column in this row.
Now it's always worth removing duplicates, even if you're not sure if you have duplicates in your spreadsheet.
And in Excel, we have a button that can check duplicates for us and remove them quickly.
So let's make sure we collect somewhere in a spreadsheet up to the data tab and in the data tools group
we have.
I remove duplicates button.
Now, when I click, this is going to ask me to determine what is actually a duplicate.
And the first thing I need to make sure that I select is my data has headers and notice.
It's picked up all of the different columns and it's put a tick in the box next to all of them.
So what this basically means is that every column has to be the same for it to be considered a duplicate.
Now I'm happy with that because I only want to remove duplicates where every value in the row is duplicated.
So let's click on OK?
You can see that it's actually found two duplicate row.
Old values and it's removed them.
Click on OK.
And we are done.
So that is how you can very quickly delete black rose input data into blank cells and also remove duplicates.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.