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

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.