Original subtitles

In this section of the course, we're going to explore some of the techniques we can use to prepare

our data for analysis.

Now, Excel is well known for its data analysis tools, so things like pivot tables and charts and different

types of calculations and formulas.

But what's lesser known is how important it is to make sure that the data that you're going to analyze

is clean and in an appropriate state, because many times data that we're using isn't necessarily a

data set that we've created.

So it might be that you've received a data set from a client or a colleague, or maybe you've even downloaded

data from an external system or some third party software.

And as we all know, we don't live in a perfect world.

So it might be that when you've done that, download the data that comes into Excel isn't in the best

format for analyzing.

It might be that you have blank cells, all blank rows everywhere.

Maybe there's weird formatting or inconsistent case.

Maybe they're spelling errors or duplicate values.

All of these things need to be dealt with before you start to create things like pivot tables and pivot

charts.

And that is what we're going to look at in this section.

I'm going to show you lots of different techniques when it comes to cleaning data.

So in this first lesson, we need to get some data.

First of all.

And this is going to give me an opportunity to show you a couple of other techniques when it comes to

importing data.

So what we're going to do here is we're going to import a data set first from a folder that contains

numerous excel files and then we're going to import a text file.

Now for this, we need to start with a blank document.

So I'm going to use the shortcut key of control and to create a new blank workbook.

Now, to import data into Excel, we need to go to the data tab.

And it's this first group here that gets and transform data.

And this little group tends to change in every new release of Excel.

They usually change the wording and where they have things positioned in this little area, but this

is how it looks right now in Excel 2021.

Now notice in here we have a few different buttons which will allow us to quickly import data from a

text or CSV file, from the web, from another table or range of cells.

We can go to recent sources or look at existing connections.

We also have a get data dropdown over here, which gives us access to all these different sources to

import data from.

And you can see there are so many so you can do things like import data from a SharePoint list, or

maybe you want to import something from Microsoft Exchange or even an external application like Salesforce.

Maybe you want to import something from an access database or an Oracle database, or maybe you just

want to import from a different file that might be an Excel file, a text file, maybe a PDF, or even

from a folder.

So lots of different sources that we can import data into excel from now before we get to this point.

Let me show you the files that we're going to import, and you'll find these to download in the Course

Files folder.

Now, in the course files, I have a folder here called sales data.

If I double click to open this folder, I have in here for files that are all exactly the same.

They just contain sales data for different years.

But if I was to open each one, they have the same format, so they all have the same column headings.

They just contain different sales data for each of the years now because these files are pretty much

identical in their layout.

It means that I can import them and combine them all in one go.

So instead of having four separate files for the 2016, 2017 and 2019 data, what I can say to excel

on the import is import all four of these from the folder and combine them together into one big, long

file.

So let's go back to excel.

Let's go to get data from file.

And because I have these stored in a folder, I can choose to import them from the folder.

Let's select the folder.

I don't need to drill down into it.

I just need to select the folder that contains the files and click on Open.

Now, what Excel will do here is it's going to take you into power query, and you may not be all that

familiar with power query or you might have used it briefly.

We're not going to go too far into power query because it is more of an advanced topic, but I am going

to show you a couple of small things that you can do here.

So what Excel has done is it's picked up those four files and you can see them sitting here.

So sales 2016 to sales 2019.

And then at the bottom, I have a few different options with regards to what I can do with these files.

Now I can choose to combine them.

At this stage, I can simply load them straight into excel, or I can transform the data.

Now, transforming the data will open these files, empower query and will give you access to all of

power queries tools for cleaning data.

Now, the focus of this section of the course isn't to learn how to clean data.

Simple query it's to learn how to clean data in Excel.

However, let's click Transform data so you can see what this looks like.

So basically, I'm now in the power query editor.

And as I said, don't worry too much about what you see on here.

The only thing I really want to show you here is how you can use power query to combine these four files

together.

So currently, there's still four separate files.

What I can do here is click these two dropdown arrows and when I hover over it, says combined files,

it's going to give me a preview as to what the first file looks like.

And if I click on OK, it's going to combine those files together and load the data into the power query

window.

So now I basically have a big long list of all of those files instead of four separate files.

Now that I've done that, I can load it into excel and start to clean it.

So I have a close and load button at the top here.

And what this does is it basically creates a connection between that folder and excel.

So if maybe at the end of the year, I then get a file called Sales 2020 in order to update this Excel

spreadsheet, all I would need to do would be to copy the sales 2020 file to that sales data folder

because we're connected to the folder.

The connection will update and automatically include all the 2020 data in this file.

So that is why this method can be pretty useful.

Now I'm going to close down the queries and connections because once it's been imported, notice that

it's automatically been placed in a table.

How do I know that?

Will I have the table design ribbon at the top?

And I have a table name called Sales Data, which now includes all four of those files.

Now there might be a couple of things I want to do here to tidy up this data.

For example, I don't really need Column A, which is just basically listing the file name.

So I'm probably going to delete out that column.

I might want to apply some formatting to these columns a control shift down arrow and let's go to home

and apply some currency formatting.

And there might be some other bits and bobs that I want to do here.

So that is one way that I can import a folder of files into excel, and you don't necessarily have to

always import a folder.

If you just had one excel file, you could go from file from workbook and then you can just select the

file that you want to import.

Now what we're actually going to do and this next file is the file that we're actually going to be cleaning

up is we want to import a text file.

And again, you're going to find this file in the course files folder.

So if we go up to the data tab again in this get and transform data group, I have a from text CSP button.

So let's click and it's this file here that we want to import sales data dot text.

Now this is going to open a preview of this text file.

I have the option of opening this simple query and cleaning up in that application by clicking the Transform

data button.

But this time, I'm simply going to load it straight into excel and clean it there.

So let's click on the load button.

And there we go, so once again, it's loaded this data in and it's created a table automatically out

of this data.

And it's this data set that we're going to take the time to clean up because notice there are a few

different issues going on in here now to make this a bit easier to see.

I'm going to go to table design and just turn off banded rows so we can see these issues a little bit

clearer.

Now I can see that in this spreadsheet, I have a couple of blank rows in here which I need to get rid

of.

I can also see Column B. I have some inconsistent casing issues going on.

I also have some weird spacing at the front of these words.

I can see that many of these columns haven't got the correct formatting applied, and there might well

be duplicates lurking around in here as well.

So all of these are things that we need to consider when we're cleaning our data sets.

So we're going to start out with this dataset in the next lesson, and I'm going to show you how you

can quickly remove all blank rows and even blank cells from your worksheets.