EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Formula and Functions Excel Formulas

Excel Formulas

Steffi Madari
Article bySteffi Madari
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel Formulas for Data Cleaning

Data Cleaning in Excel involves using Excel formulas to remove extra spaces, delete duplicate entries, fill in missing values, and change the data formatting to make it better.

The main aim of using Excel formulas for data cleaning is to detect and correct any errors, inconsistencies, and other presentation issues. We can also manipulate data in Excel to get accurate, reliable, and complete data.

This article provides the top 10 Excel formulas for data cleaning and manipulation.

  1. TRIM Function
  2. TEXTJOIN Function
  3. Remove Duplicates Tool
  4. Find and Replace Feature
  5. CLEAN Function
  6. Clear Formatting Feature
  7. Text to Column Tool
  8. LOWER, UPPER, PROPER Functions
  9. Go to Special Tool
  10. Paste Special Tool
You can download this Template for Excel Formulas here – Template for Excel Formulas

1. TRIM Function

You can use the TRIM function to remove all extra spaces from the beginning, middle, or end of the text (except single spaces).

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Example: Let’s say we have a list of idioms with extra spaces between them, and we want to remove those spaces.

Excel Formulas-Example 1.1

Solution:

Step 1: Select “Cell B2” and enter the Excel formula,
=TRIM(A2)

Excel Formulas-Example 1.2

Step 2: Press “Enter”.

This Excel formula removes all the extra spaces from Cell A2.

Example 1.3

Step 3: Now, drag the formula to the rest of the cells.

Result: The function successfully removes extra spaces between words and keeps only a single space between words.

Excel Formulas-Example 1.4

2. TEXTJOIN Function

The TEXTJOIN function is useful when you want to combine text from several columns or rows into one cell.

 Example: The below data is present in different rows. Here, we want to join data from 6 rows into 1 row.

TEXTJOIN Question

Solution:

Step 1:  Select “Cell B8” and enter the formula:
TEXTJOIN(“ “,TRUE,B1:B6) 

Excel Formulas-TEXTJOIN Formula

Step 2: Press” Enter”.

Result: The formula combines sentences from different cells into one.

TEXTJOIN Result

3. Remove Duplicates

You can use it to remove duplicate values from the selected range.

Example: Consider the below data of cities and their famous attractions for a travel itinerary. In this list, the data for New York appears twice. We want to eliminate this duplicate data for New York and retain only the unique city names.

Excel Formulas-Example 3.1

Solution
Step 1:
Follow the given steps:

  • Select the data
  • Go to the “Data” tab
  • Select “Remove Duplicates” under the “Data Tools” group.

Excel Formulas-Example 3.2

Step 2: A Remove Duplicates dialog box will appear.

Click the “Select All” option and select “OK”.

Example 3.3

Note: We have selected the checkbox for the “My data has headers” option on the right side. It is because we have included the headers (A1 & B1) in our selected cells A1 to B9.

Step 3: A message box will appear showing how many duplicated values are found and removed and how many unique values remain.

Click on “OK”.

Example 3.4

Result: Excel has removed New York duplicate rows from the list and displays only unique city names.

Example 3.5

4. Find and Replace

You can use the Find and Replace feature to select a specific value or text and replace it with another value.

Example: The data below shows a list of products and their inventory status. Here, we want to replace “Out of Stock” with “In Stock“.

Example 4.1

Solution:

Step 1:

  • Select “Column C”
  • Go to the “Home” tab
  • Select “Find & Select” under the “Editing” group
  • Click on “Replace”.
Note: The keyboard shortcut to open the “Replace” tab of the “Find and Replace” dialog box is  “Ctrl+H”.

Example 4.2

Step 2: A “Find and Replace” window will open.

  • Enter “Out of Stock” in “Find What”
  • Enter “In Stock” in “Replace with”
  • Then click “Replace All”.

Example 4.3

Step 3: A pop-up message highlighting the number of replacements done in the data will appear.

Example 4.4

Result: We have successfully replaced “Out of Stock” with “In Stock”, as shown below.

Example 4.5

5. CLEAN Function

You can use the CLEAN function to remove line breaks and non-printable characters (spaces, page breaks, etc.)

Example: The below data consists of contact numbers from an e-commerce website. Here, we want to remove non-printable characters from the data.

Note: In the 7-bit ASCII code, there are a total of 128 characters, out of which the first 32 (0 to 31) are non-printable characters.

Example 5.1

Solution:

Step 1: Select “Cell C2” and enter the formula:
=CLEAN(B2)

Example 5.2

Step 2: Press “Enter”.
The formula cleans the data of Cell B2.

Example 5.3

Step 3: Drag the formula to the rest of the cells.

Result: The formula removes all non-printing characters and displays the clean data.

Example 5.4

Note: As the data in Cell B5 has character no. 33, the CLEAN function cannot remove the character, as we can see below.

Example 5.5

6. Clear Formatting

If you want to clear or delete all formatting (bold, colors, font styles, etc.) from your data, you can use this feature.

Example: In the below data, we have highlighted cells with total sales over $500 in green and cells below $100 in orange. We want to remove this formatting.

Example 6.1

Solution:

Step 1:

  • Select the data
  • Go to the “Home” tab
  • Select “Conditional Formatting” under the “Styles” group

Example 6.2

Step 2: Now, select “Clear Rules from Selected cells” under the “Clear Rules” option, as shown below.

Example 6.3

Result: The feature removes all the applied formatting from the data.

Example 6.4

Note: You can also use the “Clear formats” option in Excel to clear all formats in the data. For that, go to the “Home” tab and select the “Clear” option in the “Editing” group.

Example 6.5

7. Text to Column

You can use Text to Column to split the content of a single cell into multiple different columns.

Example: Suppose we have some Instagram users. We want usernames, followers, and places in different columns.

Example 7.1

Solution:

Step 1: Insert three new columns, as shown below.

Example 7.2

Step 2: 

  • Select the data.
  • Select the “Data” Tab
  • Click the “Text to Column” in the “Data Tools” group.

Excel Formulas-Example 7.3

Step 3: A dialog box for “Convert Text to Column Wizard” will appear.

  • Go to the “Original Data Type” section
  • Select the “Delimited” option
  • Click “Next”.
Note: As the content in our data is separated by commas, we use the delimited method.

Excel Formulas-Example 7.4

Step 4: Now, select “Comma” from the Delimiters.

Note: The “Data preview” at the bottom of the window will show how the result will look.

Excel Formulas-Example 7.5

Step 5:

  • Click on “General” under “Column data format”
  • Select the “Destination”
  • Click on the “Finish” button, as shown below.

Excel Formulas-Example 7.6

Result: We have successfully separated username, followers, and place using the “Text to Column” option.

Example 7.7

8. LOWER, UPPER, PROPER Functions

You can change the capitalization of any letter(s) in a text string as per your requirement using the following functions:

=LOWER (text)    = Convert to lower case,

=UPPER(text)     = Convert to upper case,

=PROPER (text) = Convert to sentence case (capitalize the first letter for all words in a sentence).

Example: We will convert the sentence “cLEaR aS CrYStaL” into lower, upper, and sentence cases using the LOWER, UPPER, and PROPER functions, respectively.

Solution:
Enter the below formula in the respective cell and press “Enter”.

=LOWER (A3) in Cell C3
=UPPER (A3) in Cell E3
=PROPER (A3) in Cell G3

Result: The final conversion will be as follows:

Excel Formulas-Example 8

9. Go to Special Tool

Go to Special is a special tool for data cleaning in Excel that allows you to find and select various random cells depending on the data it has. That is, you can find cells that are blank, have Excel formulas, constants, conditional formatting, etc.

Example: Consider the following monthly expenditure ($) from January to May. We want to find and delete blank rows.

Example 9.1

Solution:

Step 1:

  • Select the data table
  • Go to the “Home” tab
  • Select the “Find & Select” option under “Editing” group
  • Click on the “Go to Special” option.

Excel Formulas-Example 9.2

Step 2: A “Go To Special” dialog window will appear.

  • Select the checkbox next to the “Blanks” option
  • Click “OK”.

Excel Formulas-Example 9.3

This highlights all the blank cells in the data, as shown below.

Example 9.4

Step 3: Now, to delete blank cells,

  • Go to the “Home” tab
  • Select “Delete” under the “Editing” group
  • Click on “Delete Sheet Rows”.

Excel Formulas-Example 9.5

Result: The Excel formula successfully removed all blanks from the data.

Excel Formulas-Example 9.6

10. Paste Special

You can use the Paste Special method to convert numbers stored as text into numbers.

Example: In the below data, students’ scores are present in text format. We have to convert the text format of the score into the numeric format.

Excel Formulas-Example 10.1

Solution:

Step 1: Select “Cell D2” and enter 1.

Example 10.2

Step 2: Copy Cell D2 and select B2: B6 cell range.

Example 10.3

Step 3: Right-click the selected range and select the “Paste Special” option, as shown below.

Excel Formulas-Example 10.4

Note: The keyboard shortcut to open the “Paste Special” window is “Ctrl +Alt +V”.

A “Paste Special” window will appear.

Step 4: Click on the “All” option under “Paste” and “Multiply” under the “Operation” section.

Excel Formulas-Example 10.5

Step 5: Click “OK”.

Result: The format of the data changed to a numeric format.

Example 10.6

These were the top 10 tools, Excel formulas, and functions you can use for data cleaning and manipulation.

Frequently Asked Questions (FAQs)

Q1. Can you automate data cleaning in Excel?
Answer: Yes, you can automate data cleaning in Excel using the power query in Excel. Using this tool, you simply perform the steps once, and the query records your every action. Thus, whenever you run the query, it will automatically perform all the actions in the same order.

Q2. How do I remove incomplete data in Excel?
Answer: You can use several tools to remove incomplete data, like extra spaces, missing information, or incorrect information in Excel. The best features are the Go to Special and Paste Special dialog box. These tools have numerous functions and operations that you can combine to perform several cleaning functions.

Q3. What are the benefits of data cleaning?
Answer: Data cleaning in Excel makes data organized, clear, and presentable. Here are some advantages of cleaning data in Excel:

  • With organized data tables, you can quickly and easily find any piece of information
  • When the data is neat and presentable, you clearly know what decisions can benefit your business.
  • With clean and orderly data, there is a low chance of any repetitions, missing information, and other errors.

Recommended Articles

This article is a complete guide to the top 10 Excel formulas for data cleaning in Excel. It provides a detailed explanation with examples and a downloadable Excel template. To learn more, check out our other articles below.

  1. Basic Excel Formulas
  2. Advanced Excel Formulas and Functions
  3. Data Manipulation Tools
  4. Count Characters in Excel
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Template for Excel Formulas

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Template for Excel Formulas

EDUCBA

डाउनलोड Template for Excel Formulas

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more