Sometimes you'll get data from another source and it doesn't always transfer in a nice clean manner. So in this screencast, I'm going to kind of give you some basics on how to clean up your data and remove things that you don't like and format things the way you want it. So I've got some dirty data here in this file called Dirty data. This probably won't be this extreme, but I've got different formats here. Maybe I have cells that are out of alignment, I have some errors in here, I have some cells that are bigger than others, I have highlighted cells. You might have a workbook full of hundreds or thousands of pieces of information and you need to formatted nicely. So the first thing I'm going to do is it's always a good idea to make a copy of your data. So you can go up here to the upper left and click on this box. You can just do Control C, and that you can add another worksheet, and then you can just do Control V to paste. So it's always a good idea to just make a copy of the original data in case you make an error in formatting, you can always track down the original data. So I've got my data here that I'm going to clean up. The first thing I'm going to do is I'm going to show you how we can remove all formatting. If you wanted this, you could select on this corner here to select the entire worksheet. There's a couple of ways to do this. The first one is you can go up here to Cell Styles and we can just click on Normal here, and that will just get everything back to the normal format. Let me go do Control Z and show you another way to do this. You can just go over here to Clear, it doesn't have to be the entire spreadsheet. You could also just select Regions, for example, I could select those cells and I could do Clear, we could clear everything. So that's going to clear the data and clear the formatting. We can clear the formats or we can clear the contents. Clear contents will remove whatever is in the cell, but it keeps the formatting. In this case, we want to remove formatting, so I'm going to click on Clear Formats, and that does the same thing as going up here to Cell Styles and Normal. So we've removed all the formatting and again, you might have hundreds or thousands of data and you can just select that entire column and do the same thing that I just did. The next thing I wanted to do is get rid of these errors. There's a nice tool in Excel. Let me show you this. We can go up here to Find and Select. First of all, if you just want it to find different things, you can explore around here, so you can find. You can also find formulas. So if I click on that, we have gone to the formulas. We have to click away and we're going to do Find and Select maybe Constants, and it's going to highlight the Constants on my worksheet. So what I want to do is, I'm going to go to Special. Alternatively, you can just press F5 to bring up this box. So it brings up this Go To Special and what I'm going to do is I'm going to click on Formulas. Again, what I'm trying to do here is to get rid of my errors. So I'm going to deselect these and leave errors selected. When I press Okay, it's going to go and highlight just the errors on my worksheet. I'm going to press Control Delete, and that's going to get rid of all of those errors in that multiple cell selection. So I've gotten rid of those errors. Now, the last thing I'm going to do is I'm going to remove blanks because I don't want these blanks to be in there, I want to sort a squeeze everything up. To do that, we can highlight the region there, and maybe I want to include that top row. I can go over here to Find and Select. I'm going to go to Special once again or you can press F5 to bring this up. I'm going to do Blanks and I'm going to click Okay. It highlights just the blank cells in that selected region. Now, what I can do is I can right-click and I can do delete. I can either shift the cells up or I can delete the entire row. So in this case, I'm just going to shift the cells up. So what we've ended up doing in the long run is just kind of cleaning up our data, removing blanks, and we got rid of those error cells and so on. So this is how you can quickly clean up your data. I also wanted to show you another tool that we can use. Now, this is in a file called Gradebook. I've used this in a previous screencast. There are a couple ways that we can identify blanks. We want to eliminate these blanks and convert them to zeros. You can imagine that this, you might have lots of students and lots of homework assignments and is sort of tedious to go through and find all those blanks and replace with zeros. One of the easiest ways to do this is just to go to the Find and Select, Go To Special, we can do what we did before. I can select blanks, Okay. Now, if I want to replace all those blanks with zero, I can type in zero, and then I can do Control Enter, and that will actually replace all of those blanks with a zero. So I'm going to go ahead and do Control Z to show you the other way to do this. There is a Replace tool, a Find and Replace. So I'm going to click on Replace. You can also do control H. So it brings up this Find and Replace tool. The thing that I'm going to find, I'm just going to leave empty because I want to find blanks, but I'm going to replace with zero. You can specify. So there's these options. You can specify if you want to look through the sheet and so on. So you can experiment around with those if you'd like. But we're going to find in this selection blanks and we're going to replace with zero. I can go ahead and click Replace All, it says we made five replacements and then I can close. So that's an easy way that you can replace blanks with zeros. We've also worked with this file before, Boulder Climate Data. Whenever a reading or a measurement is not taken correctly, you get this 9999. If we wanted to calculate the average precipitation throughout the entire year, I could take the average of the precipitation column and press Enter and that's really big. Obviously, we aren't getting 1,400 inches of precipitation as a daily average here in Boulder. But what I wanted to do is I want to remove those 9999s and I'm going to convert those to just blanks. A nice way to do that is to select the entire column. So I can click on the top cell Control Shift Down, and I can go up here to the Find and Replace. So I'm going to replace 9999s. What I'm going to replace with is not a zero but a blank, and I can click replace all and it goes through. It made 35 replacements, I can close and then that seems like a more reasonable estimate for average daily precipitation. In Part 2 of the course, I'm going to show you some more advanced techniques to clean your data. So hopefully, this screencast gave you a better idea of how you can clean up your data.
