Cleaning up messy spreadsheets

So we've been given an Excel document, or some sort of export from some sort of database. It might be something online, it might be from some sort of internal system that you got in your business, but the data has been messy, and we need to clean it up before we can do anything really.

So we're going to look at the tactics for doing that. We're going to open up, in your 'Exercise Files'-- if you haven't got the exercise files, download them, there'll be a link on your screen. And let's open up 'Half marathon Entries'.

So we've got all the entrants for our half marathon. The times they got, it's a charity event, there's the prices, and times, and data stuff. But there's some messed up formatting. The quickest and easiest is that it’s kind of ordered that along the top instead of-- what would probably be nicer if it was ordered top to bottom.

So this is pretty easy, we're going to select all the cells. We're going to click on this one here, go all the way to the end, and hold 'Shift', and click the last one. Where is the last one? Just there. So these are all my cells. Then I can go to 'Copy'. And it selects the whole lot of them.

Then anywhere down here, or you can make a new sheet and just paste it on to this. Up to you, I'm not going to use another sheet. I'm just going to paste it underneath. And I'm going to go to 'Paste', and there's this little drop down underneath 'Paste', rather than the shortcut 'Control V', use this.

And there's one little option, you can kind of see a little arrow kind of flipping it across. And you can see, it's what's called ‘Transpose', and it's just flipped from left to right, top to bottom. And with this other stuff, I'm going to click 'cell 1'. 'Delete' all this.

One thing I should probably remind you is, don't overwrite, we've only got one copy of this original data. We might do a 'Save As' so that we're not wrecking the original. I'm okay with wrecking it because I've got lots of copies. So, I've selected all of this, and I'm going to right click any of the 'columns', and hit 'Delete'. Cool, so we got rid of those. Phase 1 complete.

Phase 2 is, there's no column heads, so we need some heading on the top that says 'Name', whether 'Paid' or not. So I'm going to right click anywhere where it says '1', in here. And say 'Insert'. And I've got a new row along the top here. I'm going to call this one 'Name'. I can tab along to the next one, or I can just click in the next cell. This is going to be 'Entry Fee'. This is 'email', and this one here is the 'Sponsorship'. This one here, the next one is the date they entered, so 'Entry Date'. And the last one was the time they ran, rather, 'Finishing Time'.

Great, so we've got our column headings. We need column headings for lots of reasons. We got to pull charts out of this, and lots of other things. So what we might do for this top one here, is we might select all of these. So just click in the first one, drag across. I'm going to make mine 'Bold'. And that's it for adding columns.

You might potentially need to add a column down the left hand side depending on your data. So you can do the exact same thing, and right click 'A' and go to 'Insert'. And you can have a list down here as well. And start adding titles. I'm going to hit 'Save'.

Last thing is, I might just drag these columns out here so I can see a little bit better, they're different areas. So I can see the email addresses, sponsorships. And we go there. 'Date of 'Entry', if you see these hashes here, just means that it's only showing part of the data. Generally doesn't do it for text, but will definitely do it for numbers, because missing a few zeros off a number could be very dangerous.

So what we're going to do is, do some formatting with the currency and the dates. We'll save that to the next video, let's go and do it.