8 Data Cleaning Techniques in Excel for Data Analysis

Data cleaning is the process of fixing or removing incorrect, corrupted, incorrectly formatted, duplicate, or incomplete data within a datasets. Data cleaning reduces errors and improves data quality. Correcting errors in data and eliminating bad records can be a time-consuming and tedious process, but it cannot be ignored.

Here we about some of the fundamental and straightforward Excel Data Cleaning procedures.

Remove Duplicates

Any value that occurs more than once in that column is a duplicate.

Select the data and Go to Data –> Remove Duplicates.

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.

Delete All Formatting

Every database had its own data formatting. When you have all the data in one place, here is how you can delete all the formatting.

Select the “clear” option and click on the “clear formats” option. This will clear all the formats applied on the table.

The final data table will appear as shown below.

Get Rid of Extra Spaces (TRIM Function)

TRIM function allows you to remove unwanted spaces or characters from text.

Select the data cells with excessive blank spaces and tab spaces. Now, select a new cell adjacent to the first cell. Apply the TRIM() function and drag the cell as shown below.

Spell Check

Select the data cell, column, or sheet where you want to perform the spell check. Now, go to the review option as shown below.

Microsoft Excel will automatically show the correct spelling in the dialogue box, as shown below. You can replace the words as per the requirement as shown below.

Parse Data Using Text to Column

Select the data, click on the data option in the toolbar and then select “Text to Column”, as shown below.

In the new page dialogue box, you will see an option to select the type of delimiter your data has.

In the last dialogue box, select the column data format as “General”, and the next step should be to click on the finish, as shown in the following image.

The final resultant data will be available, as shown below.

Change Case — Lower/Upper/Proper

You can manipulate the data in the Excel worksheet in terms of character cases as per the requirements.

Function:-

=UPPER(text)

=LOWER(text)

=PROPER(text)

Highlight Errors

Highlighting errors in an Excel spreadsheet is helpful to find out the erroneous data with ease. You can do error Highlighting with the help of conditional formatting in Excel.

Select “Home”, and in the Styles group, select conditional formatting, as shown below.

In the conditional formatting option, select the highlight option, and in the next drop-down, select the option as shown below.

Find and Replace

Find and Replace will help you fetch and replace data in the entire worksheet.

The “find and replace” option is present in the home ribbon in the editing group, as shown below.

Click on the option, and a new window will open, where you can enter the data to be fetched and enter the text you need to replace, as shown below.

Thanks for reading this article so far. If you like then please share them with your friends and colleagues. If you have any questions or feedback, then please drop a note.