The are some basic rules for data cleansing that you should follow when trying to clean some data in Excel.
Work off a copy of the data- NOT THE ORIGINAL
The first rule should be that you work off a copy of the data. As eager as you may be to get stuck into the file sent via email or downloaded, take a few seconds to create a copy and work off that. Try always have a copy of the original data received to refer back to in case of a disaster!
The process of cleaning data can lead to accidental errors and there is nothing worse than not knowing how to get back to the original data.
Some ideas for this would be:
- Save the file as a separate file with the name ORIGINAL at the end, or else create a new file and call it WORKING COPY. Either way there should be 2 files and the one you never work on, only look at or make copies from.
- If this is too tedious then create a new sheet within the spreadsheet where you do your work. Label the original data as ‘Original Data’ and work on the copy sheet
- If you receive a text file (.csv, .txt etc) make sure you keep a copy in its original format (i.e. as a text file). It may be easier to work with in Excel, but sometimes characters can get lost/ changed during the import process.
Below a brief video showing how some of the above tips would work with quick ways to create copies.