How to Do Automated Data Cleaning in Excel (10 Easy Tips)

We have a disorganized dataset containing Name, Date of Birth, Profession, and Salary of some people.

Method 1 – Use the Power Query Feature to Clean Data in Excel

Steps:

Select the cell range B4:D10.
Go to the Data tab and click on From Table/Range.

The Create Table box will open, and the dataset has already been selected.
Press OK.

The Power Query Editor will appear.
Click on Use First Row as Headers to set the header.

To change the text case, select the Name column.
Go to the Transform tab and click on Text Column, click on Format, then select Capitalize Each Word.

Click on the button below to remove rows with empty cells.
Click on Remove Empty.

Click on Close & Load and select Close & Load To.

The Import Data box will open.
Select the New worksheet option.
Click on OK.

Here’s the result in a new sheet.

Method 2 – Applying Text to Columns in Excel

We have both the first and last name of a person in the same column titled as Name. We can separate the data into two different columns.

Steps:

Select the cell range B5:B10.
Go to the Data tab, click on Data Tools, and select Text to Columns.

Click on Next.

Select Semicolon, Comma, Space and @ in Others as Delimiters.
Click on Next.

Insert Cell C5 as Destination.
Click on Finish.

The data has been divided into two columns First Name and Last Name.

Method 3 – Use the Flash Fill Feature for Automated Data Cleaning in Excel

We have different unwanted symbols in the dataset.

Steps:

Insert Jack in the First Name column. This is the first value from the cell before it.
Go to the Data tab, click on Data Tools, then click on Flash Fill.

All the other symbols have been removed.

Method 4 – Use of the SUBSTITUTE Function for Data Cleaning in Excel

We can remove unwanted symbols for automated data cleaning in Excel using the SUBSTITUTE function.

Steps:

Select Cell D5.
Insert the following formula.

=SUBSTITUTE(C5,"@", )

In the SUBSTITUTE function, we inserted Cell C5 as text, “@” as old_text and blank (“ “) as new_text.

Press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

The SUBSTITUTE function has removed the unwanted symbols from column D.

Method 5 – Applying Find & Replace in Excel

We can see two values containing “#” and two values containing “@” and “;”. We will replace this value with blank.

Steps:

Select the cell range B5:D10.
Go to the Home tab, click on Editing, click on Find & Select, then select Replace.

The Find and Replace toolbox will open.
Insert “#” in the Find what box and blank in the Replace with box.
Click on Replace.

The “#” signs have been removed.

Similarly, you can remove “@” and “;” from the dataset using the Find & Replace Feature.

Method 6 – Using the Cell Format for Automated Data Cleaning in Excel

Some of the values of the Date of Birth are not in the Date format.

Steps:

Select the cell range C5:C10.
Go to the Home tab and click on Number (or go to the group), then click on the drop-down button.

Select Short Date.

Apply different formatting based on what you need.

Method 7 – Using Remove Duplicates for Data Cleaning

We will remove the duplicate values.

Steps:

Select the cell range B4:D11.
Go to the Data tab, click on Data Tools, and select Remove Duplicates.

The Remove Duplicates dialog box will open.
Press OK.

Another box containing the information of the duplicates will appear.
Press OK.

The Remove Duplicates feature will remove the duplicate values from the dataset.

Method 8 – Applying the Go To Special Feature for Automated Data Cleaning

We will use the Go to Special feature to detect blank cell.

Steps:

Select the cell range B4:D11.
Go to the Home tab, click on Editing, click on Find & Select, then select Go To Special.

The Go To Special box will appear.
Select Blanks.
Click on OK.

The blank cells have been selected.

You can change the format of the selected cells. Go to the Home tab, click on Fill Color, and select Red as Fill Color.

Here’s the result.

Method 9 – Matching Text in a List for Data Cleaning in Excel

We are going to find out the persons who have resigned from the left side of our example. There is a list on the right side of the resigned numbers.

Steps:

Select cell D5.
Insert the following formula.

=IF(COUNTIF($F$5:$F$11,C5),"Resigned","")

The COUNTIF function of the formula will return 1 if it (bold part) matches a list value(Cell range F5:F11) with a data value (Cell C5). If this part returns 1 or more, then the IF function will return “Resigned”, otherwise nothing.

Press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

This whole formula will display the word “Resigned” if the “Member Num” in column C is found in the “Resigned Members” list. If the Member number is not found, it returns an empty string.

You will get the word “Resigned” if the “Member Num” in column C is found in the “Resigned Members” list. If the Member number is not found, it returns an empty string.