All language subtitles for 2. Removing Blank Rows, Cells and Duplicates

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.