Data Cleaning in Excel

A whole set of essential data scrubbing tools for spreadsheets

How much time do you spend scrubbing and cleansing data before you actually begin spreadsheet analysis? Redundant spaces, non-printing symbols, inconsistent text case… That’s inevitable for everyone who has ever imported data into Excel or combined multiple workbooks. So how can you clean large data sets – when copying and pasting Excel functions is not an option?

The Data Cleaning add-in saves you hours of painstaking work:

  • Trim leading, trailing and extra spaces
  • Remove line breaks and non-printing characters
  • Change text case
  • Convert text to numbers
  • Convert numbers to text

Overview of XLTools Data Cleaning Functionality


Add XLTools Data Cleaning to Excel 2016-2007

Works with: Microsoft Excel 2016, 2013, 2010, 2007, and desktop version of Office 365.

Download XLTools Add-in

Learn your way around:


How to trim leading, trailing and extra spaces in cells

  1. Select a range > Click the ‘Data Cleaning’ button.
  2. On the left sidebar select one or both operations:
    •     Trim spaces at the beginning/end of text
    •     Trim excess spaces
  3. Scroll down and click ‘Apply’ > Done, all redundant spaces in text strings are deleted.

How to trim spaces, line breaks, nonprinting characters in Excel


How to remove line breaks in cells

  1. Select a range > Click the ‘Data Cleaning’ button.
  2. On the sidebar select ‘Remove line breaks’.
  3. Click ‘Apply’ > Done, any line breaks (or carriage returns, or new lines) are removed.

How to remove non-printing characters from cells

  1. Select a range > Click the ‘Data Cleaning’ button.
  2. On the sidebar select ‘Remove non-printing characters’.
  3. Click ‘Apply’ > Done, nonprintable characters are deleted.

How to change text case in cells

  1. Select a range > Click the ‘Data Cleaning’ button.
  2. On the sidebar select ‘Change characters case’ and choose the letter case:
    •     Proper Case
    •     Sentence case
    •     lower case
    •     UPPER CASE
  3. Click ‘Apply’ > Done, text case is unified throughout the range.

How to change text case in Excel cells


How to convert text to numbers

When a cell with a number has an error indicator in the upper-left corner – it means the number is actually stored as text. So Excel will treat it as text – and ignore in calculations. That is why it’s critical to convert such text to number format:

  1. Select a range > Click the ‘Data Cleaning’ button.
  2. On the sidebar select: Text to numbers
  3. Click ‘Apply’ > Done > Each converted cell will get number format and lose the indicator.

How to change text to number format in Excel


How to convert numbers to text

When you want numbers in cells to be treated as text, you can do this by adding an apostrophe (a single quote) in front of the value:

  1. Select a range > Click the ‘Data Cleaning’ button.
  2. On the sidebar select: Numbers to text
  3. Click ‘Apply’ > Done > Each converted cell will get text format and an indicator in the upper-left corner that the number is stored as text.

How to change numbers to text format in Excel

 

Any questions or suggestions? Contact us or leave your comment below.

 

11 Responses to Data Cleaning in Excel

  1. Susan says:

    When I combine data with a separator selected, such as a comma, it always adds to commas there and makes the data like ‘ Barnes,, Lisa’. Is there a way to add just one comma there? And, is there way to ‘undo’ or ‘go back’ like we use often in Excel and Word? The undo button is inactive when the XLtools is active. Thanks!

    • Maria Balobanova says:

      Hello Susan, thank you for contacting us!

      1. Combine Cells Data feature will add a separator of your choice between (comma, comma+space, etc.) the contents of the combined cells.
      I guess you want to combine 3 cells: ‘Barns’, ‘blank cell’, ‘Lisa’ > So you get two commas because of a blank cell: ‘Barnes, , Lisa’
      To disregard empty cells, please tick the box “Skip Empty cells” > You will get ‘Barnes, Lisa’

      2. XLTools is a COM type of add-in. The drawback of all add-ins of this type is that Excel does not allow UNDO on their operations. Note, you can still UNDO any standard Excel operations, but not the add-in’s operations. The best way around it is to back up your spreadsheet and keep the original just in case. Or try XLTools Version Control, that allows you to save history of a workbook.

  2. joshua venable says:

    can this be included in the automation?

    • Maria Balobanova says:

      Hi Joshua, not yet. That’s a very good idea, thank you. Data Cleaning Automation will be supported in the next releases.

  3. Tyler says:

    Unfortunately the whole “change text case”section doesn’t work. Bummer. Would have been really useful.

    • Maria Balobanova says:

      Hello Tyler, I have tested it this option in Data Cleaning just now – seems to work fine. I will send you an email, so we could figure out why it’s not working on your PC.

  4. David Ghikas says:

    Downloaded XLTools and it worked like a charm. Very sharp.

    • Maria Balobanova says:

      Thank you, David! In a few days Data Cleaning will also be available (and free) as an App in the Microsoft Office Store.

  5. i need to know more about cleaning with a pivot table

    • Maria Balobanova says:

      Ogwang, Data Cleaning does not work with Excel generated pivot tables – yet. You can apply this feature and clean your original/primary data first, and then create a PivotTable report.

  6. Oscar Tong says:

    This is a very helpful tip. I’ve never really known what to do with that button, being rather new to excel myself.

Leave a Reply

Your email address will not be published. Required fields are marked *