10 Super Neat Ways to Clean Data in Excel Spreadsheets

Watch Video – 10 Ways to Clean Data in Excel

Data forms the backbone of any analysis that you do in Excel. And when it comes to data, there are tons of things that can go wrong – be it the structure, placement, formatting, extra spaces, and so on.

In this blog post, I will show you 10 simple ways to clean data in Excel.

Click here to download the FREE PDF: 10 Ways to Clean Data in Excel

#1 Get Rid of Extra Spaces

Extra spaces are painfully difficult to spot. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible. Here is a neat way to get rid of these extra spaces – Use TRIM Function.

Syntax: TRIM(text)

Excel TRIM function takes the cell reference (or text) as the input. It removes leading and trailing spaces as well as the additional spaces between words (except single spaces).

#2 Select and Treat All Blank Cells

Blank cells can create havoc if not treated beforehand. I often face issues with blank cells in a data set that is used to create reports/dashboards.

You may want to fill all blank cells with ‘0’ or ‘Not Available’, or may simply want to highlight it. If there is a huge data set, doing this manually could take hours. Thankfully, there is a way you can select all the blank cells at once.

  1. Select the entire data set
  2. Press F5 (this opens the Go To dialogue box)
  3. Click on Special… button (at the bottom left). This opens the Go To Special dialogue box
    Clean Data in Excel - Go To Dialogue Box
  4. Select Blank and Click OK
    Clean Data in Excel - Go To Dialogue Box Select Blank

This selects all the blank cells in your data set. If you want to enter 0 or Not Available in all these cells, just type it and press Control + Enter (remember if you press only enter, the value is inserted only in the active cell).

#3 Convert Numbers Stored as Text into Numbers

Sometimes when you import data from text files or external databases, numbers get stored as text. Also, some people are in the habit of using an apostrophe (‘) before a number to make it text. This could create serious issues if you are using these cells in calculations. Here is a fool proof way to converts these numbers stored as text back into numbers.

  1. In any blank cell, type 1
  2. Select the cell where you typed 1, and press Control + C
  3. Select the cell/range which you want to convert to numbers
  4. Select Paste –> Paste Special (Key Board Shortcut – Alt + E + S)
  5. In the Paste Special Dialogue box, select Multiply (in operations category)
    Clean Data in Excel - Paste Special Multiply
  6. Click OK. This converts all the numbers in text format back to numbers.

There is a lot more you can do with paste special operations options. Here are various other ways to multiply in Excel using Paste Special.

#4 – Remove Duplicates

There can be 2 things you can do with duplicate data – Highlight It or Delete It.

  • Highlight Duplicate Data:
    • Select the data and Go to Home –> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.
    • Specify the formatting and all the duplicate values get highlighted.
      Clean Data in Excel - Highlight Duplicates
  • Delete Duplicates in Data: 
    • Select the data and Go to Data –> Remove Duplicates.
    • If your data has headers, ensure that the checkbox at the top right is checked.
    • Select the Column(s) from which you want to remove duplicates and click OK.
      Clean Data in Excel - Remove Duplicates select column

This removes duplicate values from the list. If you want the original list intact, copy-paste the data at some other location and then do this.

Related: The Ultimate Guide to Find and Remove Duplicates in Excel.

#5 Highlight Errors

There are 2 ways you can highlight Errors in Data in Excel:

Using Conditional Formatting

  1. Select the entire data set
  2. Go to Home –> Conditional Formatting –> New Rule
  3. In New Formatting Rule Dialogue Box select ‘Format Only Cells that Contain’
  4. In the Rule Description, select Errors from the drop down
  5. Set the format and click OK. This highlights any error value in the selected dataset
    Clean Data in Excel - Highlight Errors

Using Go To Special

  1. Select the entire data set
  2. Press F5 (this opens the Go To Dialogue box)
  3. Click on Special Button at the bottom left
  4. Select Formulas and uncheck all options except Errors
    Clean Data in Excel - Select Errors

This selects all the cells that have an error in it. Now you can manually highlight these, delete it, or type anything into it.

#6 Change Text to Lower/Upper/Proper Case

When you inherit a workbook or import data from text files, often the names or titles are not consistent. Sometimes all the text could be in lower/upper case or it could be a mix of both. You can easily make it all consistent by using these three functions:

LOWER() –  Converts all text into Lower Case.
UPPER() – Converts all text into Upper Case.
PROPER() – Converts all Text into Proper Case.

#7 Parse Data Using Text to Column

When you get data from a database or import it from a text file, it may happen that all the text is cramped in one cell. You can parse this text into multiple cells by using Text to Column functionality in Excel.
Clean Data in Excel - Text to Column Example

  1. Select the data/text you want to parse
  2. Go To Data –> Text to Column (This opens the Text to Columns Wizard)
    • Step 1: Select the data type (select Delimited if your data in not equally spaced, and is separated by characters such as comma, hyphen, dot..). Click Next
      Clean Data in Excel - Text to Column 1
    • Step 2: Select Delimiter (the character that separates your data). You can select pre-defined delimiter or anything else using the Other option
      Clean Data in Excel - Text to Column 2
    • Step 3: Select the data format. Also select the destination cell. If destination cell is not selected, the current cell is overwritten
      Clean Data in Excel - Text to Column 3

Related: Extract username from email id using text to column.

#8 Spell Check

Nothing lowers the credibility of your work than a spelling mistake.

Use the keyboard shortcut F7 to run a spell check for your data set.

Here is a detailed tutorial on how to use Spell check in Excel.

#9 Delete all Formatting

In my job, I used multiple databases to get the data in excel. Every database had it’s own data formatting. When you have all the data in place, here is how you can delete all the formatting at one go:

  1. Select the data set
  2. Go to Home –> Clear –> Clear Formats
    Clean Data in Excel - Clear Formats

Similarly, you can also clear only the comments, hyperlinks, or content.

 #10 Use Find and Replace to Clean Data in Excel

Find and replace is indispensable when it comes to data cleansing. For example, you can select and remove all zeros, change references in formulas, find and change formatting, and so on.

Read more about how Find and Replace can be used to clean data.

These are my top 10 techniques to clean data in Excel. If you would like to learn some more techniques, here is a guide by the MS Excel team – Clean Data in Excel.

If there are any more techniques that you use, do share with us in the comments section!

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • Number 11: Sometimes there are unprintable characters. Use Clean() to get rid of those. I like using it with TRIM, i.e. TRIM(CLEAN()) or CLEAN(TRIM()) work for me.

  • Gary

    Thanks Sumit. I need to share this with my colleagues who send me the data 😉

    • Thanks for commenting Gary.. I too shared it with a lot of my colleagues. And it made my life a lot easier 🙂

  • RMR

    Fantastic! I can’t wait to get into work on Monday and try some of these babies out. Thanks for all the effort you put into this blog as a way to share your knowledge.

    • Thanks for the kind words.. I am glad you like the techniques here 🙂

      • RMR

        I think it’s wonderful. The thing I love about the internet is people sharing their knowledges and passions. The two things I use the internet for the most is excel tips and Real Housewives (reality TV show!) recaps. Strange combination, I know. Thanks again. I love the passion and the knowledge that goes into your site.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as   or &#160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as   or &#160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as   or &#160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as   or &#160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as   or &#160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as nbsp or character 160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as nbsp or character 160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as nbsp or character 160. These have caused me much grief.

  • One thing I would mention is the removal of non-breaking spaces, symbolized as nbsp or character 160. These have caused me much grief.

    • Sumit Bansal

      Thanks for commenting.. Non-breaking spaces could be a real pain, especially when I copy data from a webpage

    • Thanks for commenting.. Non-breaking spaces could be a pain when it comes to data in Excel

  • bijay tamang

    Very Nice Tricks..

    • Thanks Bijay.. Glad you found these useful 🙂

  • JenniP

    Thank you for this. Although I consider myself an advanced user I still learnt some useful tips in these 10. One question I have is how to quickly find and remove cells where users have merged cells. This can be so irritating when it stops you creating pivots or filters.
    I find your site one of the most useful and easiest to follow solutions to any problems. Thank you again.

    • jim

      select the whole sheet and press ctrl-F1 to format cells; clear the merge cells tick box and click OK

      • You mean Ctrl-1. Ctrl-F1 hides/shows the ribbon 🙂

    • jim

      if you only want to find and select them, use Find but leave the “Find what” text empty, expand the Options>> and click Format…, select the Alignment tab and check Merge cells then click OK
      Press alt-A to Find All, then ctrl-A to leave all those cells selected when you quit the Find dialogue box

  • sandeep

    Dear.sir I need subtotal & grand total in next column of number in pivot table

    • jim

      subtotalling works better in rows – try that
      Grand totalling (rows and/or columns) is available on the pivot table options

  • jim

    caveat for No 3 – cells must not be formatted as text
    my method is as follows:
    1 select the cells you wish to convert
    2 format as General (Ctrl-sh-~ does it for me) or whatever non-text format you fancy
    3 on another sheet, copy a blank cell (then you don’t have to reselect)
    4 back with your original selection, paste special as above but ADD the value (ie zero)

  • endalamaw

    For me it was amazing

  • Branislav Kollár

    Sort both ways (A>>Z; Z>>A) to see extremes of your data
    fast and furious. Also filter dropdown is useful, to see unique values
    at glance.

    Applying General formatting unhides most of the issues with incorect data types.

    The conversion of text numbers to regular numbers is even less work, if
    you select blank cell and use “Add” or “Subtract” methods.

  • gcp1250

    Please tell me this has nothing to do with our current douchebag in chief

  • David Simpson

    I don’t know if I’m asking on the correct website or not, but here goes… I’m trying to upload an excel file to my website, normally the excel spreadsheet is in nice and neat columns, but the new spreadsheet I’ve been sent just seems to be a mess (Almost everything in under category A column). Is there a way to separate all columns from one column into several?