5 easy ways to clean your data in excel

1. Change Text to Lower/Upper/Proper Case - When you inherit a workbook or import data from text files, often the names or titles are not consistent. Sometimes all the text could be in lower/upper case or it could be a mix of both. You can easily make it all consistent by using these three functions:

LOWER() –  Converts all text into Lower Case.
UPPER() – Converts all text into Upper Case.
PROPER() – Converts all Text into Proper Case.

2. Handle your duplicate data - There can be 2 things you can do with duplicate data – Highlight It or Delete It.
If your data has headers, ensure that the checkbox at the top right is checked.
Select the Column(s) from which you want to remove duplicates and click OK.

3. Select and Treat All Blank Cells - Blank cells can create havoc if not fixed beforehand. You may want to fill all blank cells with ‘0’ or ‘Not Available’, or may simply want to highlight it. If there is a huge data set, doing this manually could take hours. Thankfully, there is a way you can select all the blank cells at once.

4. Delete all formats -  Every database has its own data formatting. When you have all the data in place, here is how you can delete all the formatting at one go: First, select the data set. Then go to “Home”, hit “clear” followed by “clear all formats”.

5. Remove extra spaces - Extra spaces aren’t easy to spot in your data set. The best way to get rid of these extra spaces is to use the TRIM Function. Excel TRIM function takes the cell reference (or text) as the input. It removes leading and trailing spaces as well as the additional spaces between words (except single spaces). Select the cell and type the following function: =TRIM(text)