Original subtitles

It's time now for us to do exercise five and what we're going to do here is we're going to practice

preparing data for analysis.

And there's quite a few different steps that I'd like you to take him.

So using this data, I would first like you to remove all blank rows.

I then like you to remove any duplicate records.

I'd like you to apply the correct number formatting to each column, so check every single column and

make sure you have the correct number formatting applied.

And then I'd like you to add a new column to the right of Column E and Name Region Code.

And then in that column, using functions, I'd like you to combine the store code and the country fields

so that the format is AB de Dash UK.

And then finally, I'd like you to put all of this data into a table and name the table store underscore

sales.

So quite a bit to do there.

We have covered all of this throughout this course, so hopefully you won't have too many problems there.

Now is the time to pause the video, but if you'd like to see my answer, then please keep watching.

So the first thing I'm going to do here is we're going to remove all of the blank rows from data set.

So let's select our dataset on the home river and we're going to go up to find and select and into go

to special.

And from here, we're going to select blanks.

Click on OK.

Once we have all of those blank was selected, we can simply jump up to delete and choose to delete

sheet rows.

Now, the second thing I asked you to do was to remove any duplicate records from your dataset, and

we have a specific button on the data tab to do this for us, and that is called remove duplicates.

So let's click it.

I'm checking for exact duplicates.

So where the information is exactly the same in every single column?

Let's click on OK, and it's found two duplicates in my data, and it's removed them for me.

The next thing I asked you to do was to apply the correct number formatting to each column.

So if I take a look at Column A, I can see that I don't have the correct formatting applied here,

so I'm going to control shift down Arrow to select all of the data from the home tablet's click the

dropdown and choose the short date format.

Now, looking across everything else looks pretty good.

The only thing I would probably do here is apply some currency formatting to this sales column.

So control shift down Arrow, and I'm going to jump up to the dropdown.

Let's select currency and I'm going to take this decimal places down to zero.

The next thing I asked you to do was to add a new column to the right of Column E and name it region

code.

So let's click to the right of Column E, right click and insert a brand new column.

Now this needs the heading of region.

Oops!

Region code I'm going to double click just to make that column a little bit wider.

Now what I asked you to do here was to use functions to combine the store code, which we have just

here with the country code.

And these two need to be separated with a space dash space.

Now there are a few different ways that you can do this.

You could use the contacts function or you can simply use ampersand, and that's how I'm going to do

it this time round.

So let's type in equals.

We're going to select the store code and we want to join it with so ampersand sign space dash space.

Now, as that is text, it needs to go in quote marks space dash space.

Quote Mark Ampersand again because we want to join that to.

The country value her answer, and we should get the format that we're looking for.

Now if you used Conker.

Let me just show you how that would work if you were using canned cat instead of Amazon's.

This time we want text one, which is the store code comma text two.

It's going to be as space dash space.

So in quote marks space dash space, close quote marks comma, and then we want our next piece of text.

So whichever method you use, it's entirely up to you.

Let's answer, and then we can double click to copy that down.

Now, the final thing I asked you to do here was to put this new clean data set into an Excel table

and then name the table store underscore sales.

So a quick way to create a table control.

T yes, my table has headers.

Let's click on.

OK.

And of course, you can change this color, which is exactly what I'm going to do right now.

Let's make that green.

And on the table, design ribbon in the properties group where we have table one.

I'm just going to rename this to stool, underscore sales and hit enter.