Top Excel Data Cleaning Techniques to Know in 2024

In today's data-driven world, having clean and reliable data is crucial for making informed business decisions. In this article, you will learn different techniques to clean data in Excel that will transform your excel spreadsheets from chaotic to organized. Whether you're a beginner or an experienced user, mastering these techniques can significantly enhance your data analysis skills.

We'll explore powerful tools such as Power Query to automate data cleaning tasks, conditional formatting to highlight inconsistencies, and other essential data cleaning tools that can streamline your workflow. Say goodbye to messy data and hello to accurate insights as we dive into the world of Excel data cleaning!

What is Data Cleaning in Excel

Data cleaning involves preparing your data for analysis by ensuring it's in a usable format. This includes removing blank cells, eliminating duplicates, and standardizing formats. Proper data cleaning is crucial for maintaining accuracy and reliability in your analyses. 

How to Clean Data in Excel

Excel provides some indispensable Data-Cleaning techniques to do data cleaning easily. The most widely used techniques are :

1. Remove Duplicates

Duplicate entries can sneak into your data when copying and pasting from various sources. Excel simplifies the process of removing duplicates, saving you time and effort. Excel has a built-in function to remove duplicates, which can save you a lot of time and effort. To do this, follow these steps:

Step 1: Select the data range.

Step 2: Go to the Data tab.

Step 3: Click on Remove Duplicates.

Step 4: Choose the relevant columns and hit OK.

2. Standardize Formats

Inconsistent formatting can hinder data analysis. To standardize formats (such as currency, dates, and times), use Excel’s formatting tools. Here’s how:

Step 1: Select the data range.

Step 2: Right-click and choose Format Cells.

Step 3: Adjust the format settings as needed.

3. Clean Text Data

Text data often harbors errors like typos, extra spaces, and inconsistent capitalization. Excel offers handy functions for cleaning text data:

• TRIM: Removes leading and trailing spaces.
• CLEAN: Eliminates non-printable characters.
• PROPER: Capitalizes the first letter of each word.

4. Fill Missing Values

Missing values can plague your data. Excel’s data analysis tools come to the rescue:

• Calculate the average or median of surrounding data.
• Fill in missing values accordingly.

5. Data Validation

Data validation can help to prevent errors from being entered into your data in the first place. You can use data validation to specify the type of data that can be entered into a cell, as well as the range of valid values.

6. Conditional Formatting

Highlight errors or anomalies in your data using conditional formatting. For instance, you can:

• Highlight blank cells.
• Identify invalid characters.

7. Power Query

Excel’s Power Query is a powerful tool that can be used to clean and transform your data. Power Query can be used to import data from a variety of sources, clean and transform the data, and then load the data into an Excel table.

Here’s how to use it:

• Import data from various sources.
• Clean and transform the data.
• Load the transformed data into an Excel table.

Note: Always back up your data before significant cleaning operations to avoid irreversible changes.

How to Remove Duplicates in Excel

One simple method for cleaning data in Excel involves removing duplicate entries. It's quite possible for data to unintentionally contain duplicates without the user realizing it. In such cases, you can easily eliminate these duplicate values.

For instance, let's take a basic student dataset with duplicate values. You can utilize Excel's built-in function to remove these duplicates, as demonstrated below.

Example: In this example, the entries for Student ID 1 and Student ID 3 are duplicates because they have the same values in the FirstName, LastName, Course, and Course Fee columns. You can remove the duplicated data with the following steps:

Step 1: Go to the Data tab and click on Remove Duplicates

Navigate to the Data Tab and select "Remove Duplicates" to easily eliminate identical entries

Step 2: Select All Columns and click OK

In this case, we want to remove duplicates based on all columns that's why choose "Select all Columns" and click "OK".

You will get the following pop-up window for the deletion of 1 duplicate record:

Step 3: Preview Results
 

How to Parse Data in Excel

This feature in Excel is useful when you have data in a single column that you want to split into multiple columns. This is particularly handy when dealing with data imported from external sources, such as CSV files, or when data is not organized in a way that suits your analysis.

Example: Consider a dataset where you have to split FullName into FirstName and LastName:

Step 1: Select all data then go to the Data tab and click on "Text to Columns".

We need to select the data on which we have to apply Text to Columns then navigate to the Data tab and select Text to Columns under Data Tools.

Step 2: Choose Space as delimiter and click OK

Here, we need to split out data based on the space between them. That's why chose space as a delimiter.
 

Step 3: Preview Result

TRIM Function - Remove Extra Spaces in Excel

The TRIM function in Excel is used to remove extra spaces from a text string, leaving only a single space between words and no leading or trailing spaces.

Example: From the following data we need to remove extra spaces and we can do it using the TRIM() function in Excel.

Step 1: Write the TRIM formula

The trim function will remove the extra spaces from L2 and the result will be visible in cell M2.

" =TRIM(L2) "

Step 2: Copy Formulas with the Fill Handle

Then, drag the fill handle (a small square at the bottom-right corner of the cell) down to copy the formula for the entire range. 

Step 3: Preview Result 

The final Data should look like the below

How to Use Find & Replace to Clean Data in Excel

The "Find and Replace" feature in Excel is handy for quickly locating specific data and replacing it with new values. This can be useful for correcting errors, updating information, or making changes to a large dataset.

Example: From the following data we need to remove and replace the errors.

Step 1: Enter Ctrl+H to launch the "Find and Replace" window

A window will open asking you to replace the word. You can enter the word to replace and the word with whom you need to replace.

Step 2: Click on Replace all after entering the data.

Enter Derk in "Find" and Dark in "Replace with". Click "Replace" to replace the word.

Step 3: Repeat Step 1 and Step 2

Step 3: Preview Result

How to Select & Treat all Blank Cells

Removing blank rows in Excel is a straightforward process and can be done using filters or a special function.

Example: Consider the following data which contains four blank spaces. It can be removed by following steps:
 

Step 1: Go Home tab then navigate to Sort & Filter and choose Filter.

You need to select the whole data and then click on the Home tab. After that choose "Filter" under "Sort & Filter". An arrow sign will appear on each column heading.

Step 2: Deselect all the columns and only select the (Blanks) column. Click Apply.

We need to check the blank cells in the data. For this purpose select the blanks checkbox.

Step 3: Select and delete the rows in blank rows.

Records with blank cells will appear. Select the data and delete them to get rid of the blank records.

Step 4: Choose Select All and click Apply

To see the records left after deleting the blank cells click on Select All and Apply the changes.

Step 5: Preview Result

How to Use Data Validation in Excel

Data validation in Excel is a powerful tool that allows you to set rules or criteria for the data entered into a cell or range of cells. This can be particularly useful for ensuring data accuracy and consistency.

Example: Consider the following data in which the Age column contains -ve and invalid decimal age value. We can solve this using the following steps:

Step 1: Go to the Data tab and select "Data Validation".

Select the records in the Age column and navigate to Data Validation under the Data tab to add validation to the Age columns.
 

Step 2: Enter the Validations

Choose "Whole Number" and set the range of Age "Between", and range of minimum and maximum to "14-30". This will allow users to enter ages of 14 to 30 only. If the user tries to enter age beyond this an error message will appear.
 

Step 3: Give Input Message

This message will appear when the user hovers on any cell to enter their age. This will guide them on what value is expected in the cell.

Step 4: Give an Error Alert and Click on "Apply"

This Error Alert will appear after the user enters the wrong input in the cell.

How to Convert Numbers Stored as Text into Numbers in Excel

It refers to the process of changing numerical data that is stored as text in a digital format into actual numeric values. Sometimes the numeric data is stored as text due to formatting issues or data import/export processes. This can lead to issues when performing calculations or analyses that require numeric data.

Example: Assume the following numbers are in cells A1 to A6. By default, the data of these numbers are as Text.

Step 1: Enter the following function in cell B2

" =VALUE(A1) "

This will change the numerical value in cell A1 wrongly entered as text to Number.

Step 2: Drag the formula to copy it to other cells

Then, drag the fill handle (a small square at the bottom-right corner of the cell) down to copy the formula for the entire range.

Step 3: Preview Result

The final Data should look like below:

How to Highlight Errors in Excel

In Excel, you can easily highlight errors in your spreadsheet to quickly identify and correct them. Errors can include things like #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, or #NULL!. These errors can cause issues when performing calculations or analyses that require numeric data. It is better to deal with these errors before proceeding with further analysis. You can Highlight Errors with the help of Conditional Formatting in Excel. Follow the below steps to Highlight errors in Excel:

Example: Assume you have a column of numbers with some intentional errors. Here's a sample dataset in column A.

Step 1: Go to the Home tab, click on Conditional Formatting, and then select New Rule.

To highlight the cells with error select the data go to the Home tab and choose New Rule under Conditional Formatting.

Step 2: Select the Formatting and click on Done

A window will appear where you can apply the formatting. Choose "Highlight Cells With" and "Errors" under Rule Type. Add the formatting "Light red fill with dark red text". Click "Apply"
 

Step 3: Preview Result

How to Change Text to Lower/Upper/Proper Case

In Excel, you can easily change the case (lowercase, uppercase, or proper case) of text using built-in functions or formulas. This improves the readability of your data.

Example: Suppose we need to convert the following data to Uppercase/Lowercase/Propercase.

1. UPPER Function

Use the UPPER function to convert text to uppercase. Follow the below example:

2. LOWER Function

Use the LOWER function to convert it to lowercase. Follow the below example:
 

3. PROPER Function

If you want to convert text to proper case (capitalizing the first letter of each word), use the PROPER function.

How to Use the Spell Check Feature in Excel

Spell checking in Excel is a useful feature for data cleaning, especially when dealing with text data. It helps identify and correct spelling errors in your spreadsheet.

Example: Consider the below example where the A1 cell has data with the wrong spelling. We can correct it by the following steps:

Step 1: Go to the Review tab and select Spelling

Select the data (A1) which you want to check for spelling. Go to the "Review" tab then select the "Spelling" option. This will provide you with the correct spelling of that word.
 

Step 2: Click on the appropriate spelling

The below dialog box will appear after you choose Spelling under the Review tab. Choose the appropriate spelling to replace it with the correct spelling.
 

Step 3: Preview Result

Conclusion

In conclusion, effective data cleaning is not just a technical necessity; it's the foundation for making sound business decisions and deriving actionable insights. By mastering the various techniques outlined in this article, such as utilizing Power Query, conditional formatting, and other essential data cleaning tools, you can ensure that your data is accurate, consistent, and reliable.

Investing time in learning these Excel data cleaning techniques will empower you to transform messy datasets into structured, insightful information, paving the way for more informed analysis and strategic planning. Remember, clean data leads to better decisions, and with the right tools and methods at your disposal, you can elevate your data management skills to new heights. Embrace the power of data cleaning in Excel, and watch how it enhances your productivity and efficiency in your data-driven tasks!