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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Read More: How to Remove Partial Data from Multiple Cells in Excel


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.

Read More: Using Excel to Clean and Prepare Data for Analysis


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.

Read More: How to Clean Up Raw Data in Excel


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.

Read More: How to Clean Survey Data in Excel


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


Download Practice Workbook


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. Thank you!


Related Articles:

<< Go Back To Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo