Cleaning up data in Excel spreadsheets
Excel is a package that is designed to make the analysis of data as convenient as possible however when you import data from another location you will often experience problems with the data and it will usually look untidy. The following data cleanup tips can help you to make your data more user-friendly.
Data cleanup tips and tools
The following tips and tools are good facilitators in data cleanup, but always remember to save your work before you employ any of these tips. It is more difficult to return to the original form of the document then sorting or filtering it:
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
- Data cleanup tip 1: PDF Data. If you are trying to bring a pdf into Excel don’t waste your time with manual workings. Rather get a pdf to Excel converter and save yourselves hours of frustration. You may still need to perform the steps below but at least your starting point is more accurate.
- Data cleanup tip 2: Data is not recognized properly
This usually happens when you import data, when you import figures Excel might not recognize the value as a figure but rather regards it as text. Therefore when you include that cell in formulas you will not get the right result. To sort this problem you can copy the cell and right click on a blank cell. Select Edit, Paste Special and then Add. You will now have to replace the new cell with the old cell that was not recognized. - Data cleanup tip 3:
Hidden Rows can result in you thinking that the formulas calculated by Excel are incorrect because they are not the same as your ‘manual’ results. Make sure that there are no hidden rows or columns that might give unexpected results. - Data cleanup tip 4:
Often in text people make the mistake of adding an extra space between words. For anyone to sit and manually adjust two spaces to one space will be very time-consuming and tedious; rather use the Find and Replace function that Excel provides. Type Ctrl + H, this will bring up the Find and Replace dialogue box, in the field for ‘Find what’ you type two spaces and in the ‘Replace with’ field you type just one space. Then click Replace All and your problem is solved. - Data cleanup tip 5:
Sorting: When you type lists into an Excel spreadsheet very few times is the list pretty in the sense that it is not easy to find a specific value or word. Excel has a sorting operation that can sort the data. Figures can be sorted in ascending or descending order and text can be sorted alphabetically, either from A – Z or from Z – A. - Data cleanup tip 6:
Imported phone numbers, often when phone numbers are imported you will have a variety of different ways that the numbers are written, you can use the Find and Replace tool to have all the phone numbers look the same. Firstly you need to remove all the extra characters so that you only have nine digits left. Press Ctrl + H, in the ‘Find what’ box you type a left or right parenthesis or a hyphen or a period; in the ‘Replace with’ box you do not type anything, click Replace all. To ensure that Find and Replace only works on the telephone numbers highlight the area you would like to adjust before typing Ctrl + H.
Data cleanup is not something that can be defined once, there are constantly new tools that are being developed to assist you in your data cleanup process. Miricle Solutions is a company that has expertise in Excel and have numerous courses to help you to maximize your usage of the Excel package from basics to power courses and fun and useful stuff. Contact Miricle Solutions to aid you and your business by learning the art of Excel.