Data Cleaning add-in: a whole set of essential data scrubbing tools for bulk Excel data

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 tables quickly, when searching, manually deleting, or copying Excel functions are not a productive option?

The Data Cleaning add-in helps clean massive data sets in seconds:

  • Trim leading, trailing and extra spaces
  • Remove line breaks and non-printing characters
  • Change text case in the entire range
  • Convert numbers stored as text to actual numbers
  • Convert numbers to text values

(Note: the video may not reflect the latest upgrades. Please use the guide below.)

 

Add "Data Cleaning" to Excel 2016, 2013, 2010, 2007

Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

Download XLTools Add-in

Excel data cleaning ribbon button

 

Learn your way around:

Note: UNDO (undo the last operation) is not applicable. We recommend that you save backup copies of your files or use XLTools Version Control to keep track of changes.

Overview of XLTools Data Cleaning Functionality

 

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 Excel

  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 numbers (stored as text) to actual 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 will lose the error indicator.

How to change text to number format in Excel

 

How to convert numbers to text values

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? Please leave your comment below.

 

15 Responses to Data Cleaning add-in: a whole set of essential data scrubbing tools for bulk Excel data

  1. Steve Kossor says:

    I want to use the Excess Format Cleaner in Excel 2016 like I used to in 2013 but it doesn’t seem to have the feature of “lopping off” unused rows/columns like the older version did. How can I lop off unnecessary parts of a spreadsheet now?

    • Maria Balobanova says:

      Hello Steve,
      I am guessing, you are talking about this Inquire Add-in. This add-in is developed by Microsoft and is only available in Office Professional Plus 2013 or Office Professional Plus 2016.

      We are considering to add similar functionality to our XLTools add-in in the next versions.

      You also try cleaning all excess formatting manually (before you try, please save a backup copy of your original file):
      1. Select all columns to the right of the last column in your spreadsheet. Or simply type F5 > type the reference range, e.g. F:IV > Home tab > Editing group > Click “Clear all”.
      2. Select all rows below of the last row in your spreadsheet. Or type F5 > type the reference range, e.g. 5:65536 > Home tab > Editing group > Click “Clear all”.
      More tips from MS Support.

  2. Den Jo says:

    Does it support the version on Mac iOS?

    • Maria Balobanova says:

      Hello Den Jo,
      XLTools desktop version is only supported for Windows.
      But we have another tool – Data Cleaning Office add-in. It also works on Excel for iPad, Excel for Mac and is available in the Office Store. Note, that functionality is a bit different.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. OGWANG Ambrose says:

    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.

  8. 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 *