excel course singapore
January 8, 2021

Best ways to clean data in excel

By Martinez

Data is forming the backbone for any analysis which you are doing in Excel. And when it is coming to the data then there are tons of things that can go wrong like placement, structure, extra spaces, formatting, and so on.

In this blog, you will see the simple ways to excel in data cleaning.

This Tutorial will cover the below points:

  1. Selecting and treating all the blank cells

You can select the full data set then Press F5, then click on the special button at the bottom left. This will open the special Go to dialogue box, then select the blank and click OK.

Then you can type by pressing Control+Enter and your required value is inserted.

  1. Getting rid of extra spaces

TRIM Function is been used for deleting extra spaces between numbers and words.

excel course singapore

  1. Removing duplicates: In two ways you can delete the duplicate data, either highlight it or delete it.
  2. Converting stored numbers as text back to numbers

Select any blank cell and then type 1. Then select the cell where you have type 1 and then press Control + C. Then select the range/cell where you want to convert to numbers and then select paste special. Then select Multiply in the dialogue box of paste special. Then click OK. This will convert all the numbers in text format to the number format.

  1. Highlighting errors
  2. Changing text to Proper/Upper/Lower Case
  3. Spell Check
  4. Parsing data from text to column
  5. Deleting all Formatting
  6. Using replace and find for excel data cleaning