Welcome to week three, and welcome, Nicky. Hi, Prashan. This week, we're going to be looking at data cleaning and preparation. So Nicky, instead of cleaning and preparing data, shouldn't we be focusing on calculations, graphs, and charts? We can't really do the calculations, graphs, and charts on data that's a mess. So I'm afraid, we have to look at this first. So, how does the data get dirty in the first place? I think the most common reason for getting data that's regularly dirty is that it's coming from another source, and that could be a company database, or it comes down with missing values, weird characters, or it could be that the data is coming from an external source like a government database. And again, it could be problems with missing values or formatting that's just not suitable for what you need that data for. So, why exactly is data cleaning and preparation important for business? Well, studies have shown that data analysts spend up to 80 percent of their time just on cleaning and preparing the data. And the reason for this is, there is so much data available these days that we need to be using it, even the small businesses need to perform data analysis. But the problem is, they can't begin to do this until their data has been cleaned, and what we want to do is look at making that process much more efficient and a lot less manual. So, is there a general approach that you would suggest to us? Yes, I think the two things that I would suggest upfront, so even before you jump in and start working with the data, it's a very good idea to actually keep your original raw data in its own spreadsheet. So rather than getting there and sorting it in text to columns and all those things, leave it alone. Create a separate spreadsheet and in that sheet, have your formulas where you pull your data through and modify it in that sheet. And that has two advantages. First of all, you get to keep your original raw data as you can always get back to it if there's a problem. But also, it means that as the raw data is replaced with new data, the formula is all sitting then, that data is automatically cleaned. So we're looking at a much more efficient process as well. The other thing I would recommend is document the process. There may be a lot of steps involved, and for the person doing the cleaning but also the other uses, it's good to see what process that data is going through. Some great tips, Nicky. So, what are some specific problems that we could be expecting? I think one of the most common problems is when you get spaces and unwanted characters coming through in your data, and often, that can be easily cleaned just by using a trim or clean function. And what about those stubborn characters that actually don't disappear with trim and clean? Yes, and they can be a little bit more tricky to get rid of, but we have a great function called SUBSTITUTE. And once you've identified what those characters are, you can substitute them for nothing or something more friendly. The problem, of course, is the identification process, and there, we're going to introduce our users to the code system that Excel uses. So each character has a numeric code, space is code 30, for example. And once you've identified the code, and we'll look at functions that help us do this, you can then just replace that character with something more appropriate. Now the code is called ASCII, and in the toolbox, we've got the full ASCII code and a little bit more information on how it works. That's text covered, what about numerics and dates? Yes, they can also be problems. I think one of the most common things with numeric values is they actually come through as text. And then, you could easily convert them just by applying the VALUE function. Dates, they can come in all sorts of bizarre formats that Excel might not recognize as a date. And there, we're going to draw on our old friends, the LEFT, RIGHT, and MID, and throw in a new function that we haven't looked at before, the date function, to help get our dates back to behaving like dates. Amazing. So by the end of this week, our learners will have really clean data so that they can be using for their business analytics. Absolutely. Thank you so much, Nicky. Sure. So now, we've got some practice videos coming along for you. As always, make sure you download the Excel workbooks, so that you can work alongside us step by step. Then, try out this week's quizzes, so that you can test your new skills. Make sure you check out this week's practice challenge to test your skills in a totally different context. Check out this week's toolbox. In there, we have the ASCII codes that Nicky discussed, and as well as this week's great ninja tip. Now, it's over to you.
