More Excel data cleansing tips

More Excel data cleansing tips- working in cells

Although we cannot exactly predict which data issues you may have, we do suggest you follow a process to figure out what the problem actually is. Too many people just stare at the screen and hope the solution will pop out at them.

You need to play with the ‘dirty’ data and see what happens. The video clip below suggests some ways to look for the problems within a cell.

To follow along in your Excel Download File go to 1.3How to approach a data scrub

So in summary:

• Click on the cell- not at the end of the text but further along the cell
• Move your cursor backwards and forwards with your arrow keys to see how many characters are in the cell, especially spaces.
• Look for spaces at the end of the cell
• Delete components and see what works. If you delete a space or comma does Excel change the cell into a number?
• In Excel, deleting is the same as replacing with nothing. If in the manual process you delete a comma and it works, you will need to find a way to replace the comma with nothing.
• At some point you will need to decide what you will use to clean the data. A tool (Flash Fill, Data Filter) is useful for once off clean ups. Formulae are better when you want to set up a template that will perform the data clean up. It might take a bit more time to setup, but from then on it should just be a copy/ paste.