A quick way to clean excel data using a common excel tool.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 02:45
- - Skill Level: 4
- - What's my skill level
Clean excel data
A trick using the Data Autofilter to clean excel data so that it can be worked on further.
Transcript
In Excel we sometimes receive data that looks something like this. It is quite clear from the report that the information we want is actually over here. So you’ll see there are headers, footers, etc.
The problem with this is that we cannot use it in pivot tables, data sorting, etc, so we need to clean it up. Instead of spending hours going like this and basically working your way through the entire spreadsheet you can consider making use of the data- auto- filter.
Let’s just undo it and what we can do is we switch on the data-auto-filter and now we are ready to clean up the data.
So in this case what we want to do is this is what we want to keep –so we have to ask the auto-filter to find what we don’t want and you’ll see it is anything like this – it has got headers, there are some more headers, page numbers, etc.
So when I click on the auto filter- you’ll see I have all the items that are listed there and in this case I know, that what I want to get rid of I want to now see – is everything here except the sales person codes. So I am just going to go and de-select all of those. When I say OK – what I see is everything that I don’t want.
To get rid of it now is as simple as – I highlight the full row over here – not the cell – go all the way down. I am going to hold Shift and click. The blue numbers, not the black numbers – I can right click on any one and I’ll say “delete row” and now when I clear the filter suddenly the report is a lot cleaner.
In this case I happen to know that I also don’t want these where there is a max and min, so I am going to go to the second level and I am going to say “just show me what I don’t want to see – the max and min” – again I highlight the area, right click – delete row and when I clear, my data base has been cleaned in a lot less time than would be required if I went through and manually deleted each row that I don’t want.