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

Looking for ways for automated data cleaning in Excel? Then, this is the right place for you. Sometimes, the data we get is not clean and ready for analysis. In those cases, we need to clean and prepare data by following some steps. Here, you will find 10 different step-by-step explained ways to get knowledge about automated data cleaning in Excel.


Download Practice Workbook


10 Ways for Automated Data Cleaning in Excel

Here, we have a disorganized dataset containing Name, Date of Birth, Profession, and Salary of some people. Now, we will show you ways for automated data cleaning in Excel using this dataset.

Ways for Automated Data Cleaning in Excel


1. Use of Power Query Feature to Clean Data in Excel

In the first method, we will use the Power Query Feature for automated data cleaning. Follow the steps to do it on your own dataset.

Steps:

  • Firstly, select Cell range B4:D10.
  • Then, go to the Data tab >> click on From Table/Range.

Use of Power Query Feature to Clean Data in Excel

  • Now, the Create Table box will open and the dataset has already been selected.
  • After that, press OK.

Opening Create Table Box for Automated Data Cleaning

  • Next, the Power Query Editor will appear.
  • Then, click on Use First Row as Headers to set the header.

  • Afterward, to change the text case, select the Name column.
  • Now, go to the Transform tab >> click on Text Column >> click on Format >> select Capitalize Each Word.

  • Next, click on the button below to remove rows with empty cells.
  • After that, click on Remove Empty.

  • Then, click on Close & Load >> select Close & Load To.

  • Now, the Import Data box will open.
  • Next, select the New worksheet option.
  • After that, click on OK.

Opening Import Data Box for Automated Data Cleaning in Excel

  • Thus, you can clean data automatically using Power Query Editor.


2. Applying Text to Columns Feature in Excel

Here, in the dataset, 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 using the Text to Columns feature for automated data cleaning in Excel.

Here are the steps.

Steps:

  • In the beginning, select Cell range B5:B10.
  • Then, go to the Data tab >> click on Data Tools >> select Text to Columns.

Applying Text to Columns Feature for Automated Data Cleaning in Excel

  • After that, click on Next.

  • Next, select Semicolon, Comma, Space and @ in Others as Delimiters.
  • Then, click on Next.

  • Now, insert Cell C5 as Destination.
  • After that, click on Finish.

  • Finally, you can see that the data have been divided into two columns First Name and Last Name.


3. Use of Flash Fill Feature for Automated Data Cleaning in Excel

Now, you will find a way to use the Flash Fill Feature for automated data cleaning in Excel. Here, we have different unwanted symbols in the dataset. You can remove them by following the steps given below.

Steps:

  • Firstly, insert Jack in the First Name column.
  • After that, go to the Data tab >> click on Data Tools >> click on Flash Fill.

Use of Flash Fill Feature for Automated Data Cleaning in Excel

  • Now, you will see that all the other symbols have been removed.


4. Use of SUBSTITUTE Function for Data Cleaning in Excel

Next, we will show you how you can remove unwanted symbols for automated data cleaning in Excel using the SUBSTITUTE function.

Follow the steps given below to do it on your own.

Steps:

  • In the beginning, select Cell D5.
  • Then, insert the following formula.
=SUBSTITUTE(C5,"@", )

Use of SUBSTITUTE Function for Automated Data Cleaning in Excel

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

  • After that, press ENTER and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

  • Finally, the SUBSTITUTE function has removed the unwanted symbols from column D.

Automated Data Cleaning in Excel Using SUBSTITUTE Function


5. Applying Find & Replace Feature in Excel

In the fifth method, we will clean data using the Find and Replace option in Excel. Here, we can see two values containing “#” and two values containing “@” and “;”. We will replace this value with blank by applying Find and Replace option.

Steps:

  • First, select Cell range B5:D10.
  • Then, go to the Home tab >> click on Editing >> click on Find & Select >> select Replace.

Applying Find & Replace Feature for Automated Data Cleaning in Excel

  • Now, the Find and Replace toolbox will open.
  • Next, insert “#” in the Find what box and blank in the Replace with box.
  • After that, click on Replace.

Opening Find and Replace Box for Automated Data Cleaning in Excel

  • Then, you will see that “#” has been removed.

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


6. Using Number Format for Automated Data Cleaning in Excel

Now, you will learn how to change Number Format for automated data cleaning in Excel. Here, some of the values of the Date of Birth are not in Date format. Go through the steps given below to clean data on your own.

Steps:

  • Firstly, select Cell range C5:C10.
  • Then, go to the Home tab >> click on Number Format >> click on the drop-down button.

Using Number Format for Automated Data Cleaning in Excel

  • After that, select Short Date.

  • Thus, you can change the Number Format of the dataset to clean data automatically in Excel.


7. Using Remove Duplicates Option for Data Cleaning

Now, we will remove the duplicate values by using the Remove Duplicates feature for automated data cleaning in Excel.

Steps:

  • Firstly, select Cell range B4:D11.
  • Then, go to the Data tab >> click on Data Tools >> select Remove Duplicates.

Using Remove Duplicates Option for Automated Data Cleaning in Excel

  • Now, the Remove Duplicates dialog box will open.
  • After that, press OK.

Opening Remove Duplicates Dialog Box for Automated Data Cleaning in Excel

  • Next, another box containing the information of the duplicates will appear.
  • Then, press OK.

  • Finally, the Remove Duplicates feature will remove the duplicate values from the dataset.


8. Applying Go To Special Feature for Automated Data Cleaning

Now, we will use the Go to Special feature to detect blank cell ways for automated data cleaning in Excel.

Here are the steps.

Steps:

  • In the beginning, select Cell range B4:D11.
  • Then, go to the Home tab >> click on Editing >> click on Find & Select >> select Go To Special.

Applying Go To Special Feature for Automated Data Cleaning in Excel

  • Now, the Go To Special box will appear.
  • After that, select Blanks.
  • Next, click on OK.

Opening Go To Special Box for Automated Data Cleaning in Excel

  • Then, you will see that the blank cells have been selected.

  • Now, you can change the format of the selected cells according to your choice.
  • Here, we will go to the Home tab >> click on Fill Color >> select Red as Fill Color.

  • Thus, you can detect blank cells to clean data in Excel automatically.


9. Matching Text in List for Data Cleaning in Excel

You may have some data and you want to check this data against another available list. See the following screenshot of our example. 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.

The above screenshot shows a simple example. The data is in the range B5:D22. The goal is to identify the rows in the data zone which are appearing in the Resigned Members list, in column F. You can delete these unnecessary rows later.

Steps:

  • Firstly, select Cell D5.
  • Then, insert the following formula.
=IF(COUNTIF($F$5:$F$11,C5),"Resigned","")

Matching Text in List for Automated Data Cleaning in Excel

Here, 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.

  • After that, 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.

  • Now, 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 can sort the list by column D, the rows for all Resigned Members will appear together and can be quickly deleted.

  • To sort by column D, just select Cell D5 to D22.
  • Then, choose HomeEditingSort & Filter Sort Z to A.

  • Next, the Sort Warning box will open.
  • After that, select Expand the selection.
  • Now, click on Sort.

Opening Sort Warning Box for Automated Data Cleaning in Excel

  • Then, our Excel sample file will be like this one.

This technique can be adapted to other types of list-matching tasks.


10. Using Spell Check for Cleaning in Excel

In the final step, we will show you how to Spell Check for automated data cleaning in Excel. Follow the steps given below to do it on your own.

Steps:

  • In the beginning, select Cell range C5:C10.
  • Then, to go to the Review tab >> click on abc Spelling.

Using Spell Check for Automated Data Cleaning in Excel

  • Now, the Spelling box will open.
  • After that, select Manager.
  • Then, click on Change.

Opening Spelling Box for Automated Data Cleaning in Excel

  • Next, select Receptionist.
  • Afterward, click on Change.

  • Now, select Clerk.
  • After that, select Change.

  • Next, a Microsoft Excel warning box will appear.
  • Then, click on OK.

  • Thus, you can use Spell Check to clean data in Excel.


Practice Section

In the article, you will find an Excel workbook like the image below to practice independently.

Practice Section


Conclusion

So, in this article, we have shown you ways for automated data cleaning in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo