Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Ten Ways to Clean Data in Excel

Ten Ways to Clean Data in Excel

This 10 Ways to Clean Data in Excel tutorial is suitable for users of Excel 2013/2016/2019 and Microsoft 365.  

Objective 

Use Excel’s functions and utilities to clean a raw dataset, removing errors and making it consistent ready for analysis.  

Cleaning Data Explained 

Data is the foundation of any analysis you do in Excel. If you receive a dataset from someone or maybe you download it from another system and import it in Excel, there are tons of things that can go wrong. Blank rows, inconsistent case, spelling errors, duplicates – all these need to be fixed before performing an analysis. If you do not take the time to clean data in Excel, you will achieve inconsistent and inaccurate results when you attempt to analyze it.  

Excel has many tools, functions, and utilities that can help you clean your data efficiently. In this post, we are going to explore ten of them.  

Video Tutorial 

Removing Blank Rows and Populating Blank Cells 

Removing Blank Rows 

Blank cells and blank rows can cause havoc if left, particularly if you intend to analyze your dataset with a Pivot Table. If you have a large amount of data with blank rows and cells throughout, removing them can be tedious and time-consuming. Fortunately, Excel has a neat way of highlighting all blanks for you to remove them in one go.  

  1. Select the data 
  1. From the Home ribbon, in the Editing group, click Find & Select 
  1. Select Go To Special from the menu 
  1. Select Blanks 

The blanks rows in the range selected will be highlighted. You can now remove them all.  

  1. From the Home ribbon, in the Cells group, click the lower half of the Delete button 
  1. Click Delete Sheet Rows 

Populating Blank Cells 

If your worksheet contains blank cells, you might want to populate them all at once with a value, as opposed to removing them. When you remove blank cells, Excel will shift the values to fill the space, which can throw off your dataset—populating the blanks with a value such as a ‘0’ can prove to be more beneficial.  

  1. Select the data 
  1. From the Home ribbon, in the Editing group, click Find & Select 
  1. Select Go To Special from the menu 
  1. Select Blanks 
  1. Type ‘0’ and press CTRL+Enter 

Removing Duplicate Entries 

Duplicate entries can occur in large datasets. You will want to remove these before analysis.  

  1. Select the data 
  1. From the Data ribbon, in the Data Tools group, click Remove Duplicates 

The checkboxes allow you to specify which fields you are searching for duplicates. If you want to remove complete duplicates, ensure all the checkboxes are selected.  

Get Rid of Extra Spaces 

Blank spaces in front of text or values in cells can cause issues when analyzing data. Sometimes, these erroneous spaces are hard to see, mainly if they are at the end of a text string or value. It is always worth checking for and removing extra spaces before analyzing. We can do this using a text function in Excel.  

In this example, there are extra spaces in column B that we need to remove. To do this, I am going to insert a ‘Helper’ column to the right of column B.  

  1. Select column C 
  1. Right-click and select Insert 
  1. Use the TRIM function to remove the extra spaces 
  1. Use the Auto-fill handle to copy the formula down 

We now need to remove column B. Deleting column B will produce an error because the TRIM formula in column C refers to column B. To get around this, we need to copy and paste the values only to ‘throw-away’ the underlying formulas.  

  1. Select column C 
  1. Press CTRL+C to Copy 
  1. From the Home ribbon, in the Clipboard group, click the lower half of the Paste button 
  1. Select Paste Values 

You can now go ahead and safely delete column B.  

Change the Case of Text 

It is good to ensure that your case is consistent throughout your dataset. There are three functions in Excel, UPPER, LOWER, and PROPER that can help you switch the case to upper case, lower case, or proper case where the first letter of each word is capitalized.  

In the example below we have inconsistent case in column B. To fix this, I am going to insert a ‘Helper’ column to the right of column B.  

  1. Select column C 
  1. Right-click and select Insert 
  1. Use the PROPER, UPPER or LOWER function to change the case of the text. 
  1. Use the Auto-fill handle to copy the formula down 

Once again, you will need to copy and paste the values before deleting column B.  

Find and Replace 

You can utilize the Find and Replace feature in Excel to find specific values or text strings and replace them with something else.  

In this example, I want to replace the word ‘United Kingdom’ in column A with the shortened form of ‘UK’.  

  1. Select column A 
  1. Press CTRL+F to show the Find and Replace dialog box 
  1. On the Replace tab, type the word or value to replace in the ‘Find What’ field and the word of value to replace it within the ‘Replace With’ field. 
  1. Click Replace All 

Splitting Text 

When you import data from a database or another file, it may be that the text is all in one cell. There are numerous functions and utilities available in Excel that can help you split your data into separate columns.  

Text to Columns 

The Text to Columns utility in Excel splits up text into columns using a delimiter. The delimiter is a special character such as space, tab, comma, semi-colon, etc. that denotes where you want the split to occur.  

  1. Select the column you want to split 
  1. From the Data tab, in the Data Tools group, select Text to Columns 
  1. Step through the wizard, ensuring you select the correct delimiter for your data 

Flash Fill 

Flash Fill is another utility available in Excel 2013 and above that makes splitting data quick and easy. I am going to insert a ‘Helper’ column to the right of column B.  

  1. Select column C 
  1. Right-click and select Insert 
  1. Type the text you want to separate out into the first cell 
  1. From the Data tab, in the Data Tools group, click Flash Fill 
  1. Repeat this process to separate the remaining text.  

Joining Text Together 

If you import data, you may find that text is in different columns that you’d like in one column. 

In this example, I want to see the text ‘Kensington – Luxury’ in one cell. I can join the text in columns B and C by using the CONCAT function. This function takes the text in cell B2, the delimiter ( specified in quotations), and then the text in cell C2 and joins them together.  

I could also use the Flash Fill method outlined previously to join values and text strings together.  

Applying Number Formatting 

It is essential to ensure that you have the correct formatting applied to each column in your dataset. For example, if you have a column that contains dates, and you do not have the column formatted as a short date or long date, the field will not display when adding a timeline slicer. That is just one example of how incorrect number formatting can cause issues at a future point in time.

  1. Select a column 
  1. From the Home tab, in the Number group, click the Number Format drop-down menu. 
  1. Select the correct format for the type of data in the column

Spell Check 

Nothing lowers the credibility of your work more than spelling errors. Spelling errors can also cause problems when trying to analyze data. For example, if you have the word, ‘Kensington’ spelled correctly in one cell and incorrectly in another, Excel will see that as two separate items. This spelling issue is problematic when it comes to things like filtering data or analyzing with PivotTables.  

  1. Press the keyboard shortcut F7 to run a spell check on your dataset, OR 
  1. From the Review tab, in the Proofing group, select Spelling 

Clear all Formatting 

If you import data from an external source, it can come across with the formatting from the system or application it was created in. To start with a blank canvas so you can apply your formatting, you should clear any problematic formatting from the dataset.  

  1. Select the data you want to remove the formatting from 
  1. From the Home tab, in the Editing group, select the lower half of the Clear button 
  1. Select Clear Formats 

These are just ten of the utilities available in Excel to assist with cleaning data ready for analysis. For more tips related to cleaning data, please check out the following links: 

Microsoft – Top Ten ways to clean your data 

Software Advice – Techniques for Data Cleaning and Integration in Excel 

Other Excel classes you might like:

  • Excel Crash Course – Learn Pivot Tables in 1 Hour
  • Protecting WorkBooks in Excel
  • Logical Functions in Excel
  • Designing Better Spreadsheets in Excel
  • Introduction to Power Pivot & Power Query in Excel

To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Set Up Customers and Jobs in QuickBooks 2019

Excel Crash Course – Learn Pivot Tables in 1 Hour

How to Insert GIF into PowerPoint? 3 Easy Ways

How to Change a Task Duration in Microsoft Project

How to Hide Formula Bar in Excel (and Show)? 3 Easy Ways

What is Daily Scrum or Daily Scrum Meeting? [2022]

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)