Save 5 minutes a day by using ASAP Utilities to quickly clean up your data in Excel.
"Easily optimize and clean your dataset"
One of the first things you often need to do, when you import a file or copy information, is clean it up before you can analyse the data. There are many things that can be wrong, such as trailing spaces, unrecognized numbers, irregular formatting, hyperlinks, etc..
Cleaning up your data helps makes sure that everything is correct, so that you can make better sense of it.
For a quick start, just select the cells and then choose the following tool in the Excel menu and choose your cleaning options:
ASAP Utilities » Format » Clean data and formatting...
Our "Clean data and formatting" tool allows you to choose from the following actions to do on your selected range:
- Remove all hyperlinks
- Trim spaces (removes leading and trailing spaces)
- Removes leading and trailing spaces.
- Also handles non-breaking spaces (this so called character, char 160, is commonly used in Web pages as the HTML entity, ).
- Empties cells that only contain one or more spaces.
- Turns all empty cells into truly empty cells that Excel recognizes if needed.
- Remove all cell colors
This will set the cell color to nothing/no fill in the selected cells. - Remove all font colors
This will set the font color to the default/automatic font color.
- Remove all borders
- Unmerge cells
- Apply standard font
- Apply standard font size
- No wrap-text
This will turn off the "wrap text" alignment. - Fix (text)numbers
- Removes leading and trailing spaces around cells with numbers.
- Removes apostrophes in front of numbers.
- If the number format in the cells with numbers is "Text" then it will be changed to "General" in these cells.
- Handles non-breaking spaces (this so called character, char 160, is commonly used in web pages as the HTML entity, ).
- Removes leading zeros.
- Preserves long numbers such as credit cards. Unlike Excel, it will not change the last digit to a zero such as from credit card numbers, which is what Excel's built-in "Convert Text to Number" does.
- Turns all empty cells into truly empty/blank cells that Excel recognizes as being empty if needed.
- Autofit columns, with the option to set a maximum width in case a column contains cells with long values
- Autofit rows, with the option to set a maximum height in case a row contains cells with long values
- Remove all objects/images from your entire worksheet
As a standard bonus, this tool also quickly resets the last cell and minimizes the used range if required.
Do you recognize any of these situations?
- I copied some numbers on a web page and pasted them onto an excel spreadsheet. When I use the sum formula, or average formula, excel is not recognizing the numbers in the cell, and won't add up my rows. I've tried reformatting the field as a number, but it's not working. If I re-type the numbers in the field, then Excel recognizes them. This is frustrating!
- I've copied a column of data (numbers) from a website and pasted it in Excel. However, the numbers are not recognized as numeric values (we can't sum them, etc.). We have tried all of the "easy" solutions such as changing their format to general or number, copying a 1 from another cell in a clean workbook and using the paste special/multiply function... We've tried the text to columns thing, basically have tried everything in the MS KB, but nothing works except for retyping the value in each cell, which is a bummer since we've got about 2000 cells. Any solutions?
- Excel doesn't recognize some cells as being empty. That happens often with imported data.
- I tried to remove the spaces by using Excel's =TRIM() function but it didn't remove the spaces in my case. I found that this can happen with so called "non-breaking" spaces. See also this article from Microsoft that describes how to remove these unwanted spaces.
- My workbook contains data imported from another application and unfortunately many of these cells have padded leading blanks added.
I need to remove these, but 4000+ cells individually will take hours. - I have difficulty sorting my data due to leading spaces. Manually removing these spaces takes hours. Is there some magic trick for this?
- I like to use the shortcuts Control+Home and Control+End to quickly move to the top or end of my worksheets. In one sheet, the end point is for reasons unknown several thousand rows below the actual last row. How can I change the end point so that the Control+End command will take me to where I want to go?
Just select the cells and then choose the following tool in the Excel menu and choose your cleaning options:
ASAP Utilities » Format » Clean data and formatting...
Bonus tips, also interesting
How much time will it save?
It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.
Download
In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page