10 Super Neat Ways to Clean Data in Excel Spreadsheets

Microsoft Excel offers a great platform for all businesses across the world to operate with their data, reports and maintaining other important reports. However, some misspelled words, unwanted prefixes and some duplicate rows can mess up your complete data and create a bad impression before clients.

Importing data from external data source

Opting to convert Excel spreadsheet to web application free is the best way out to deal with this issue. Excel provides multiple ways to deal with this situation like for all the misspelled words, use spell check function to correct your data in columns that contain comments or descriptions. By using the Remove Duplicates dialog box, you can remove duplicate rows.

Eliminate Extra Spaces

Using the Trim function in Excel, you can simply get rid of all the extra spaces and even the trailing spaces, which are hardly visible, can also be cleared with this. 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.

Manage Blank Cells

Blank cells can prove to be a chaos if not treated well in time. To mark all your blank cells with N/A, you just have to select the entire data set and press F5 which opens to dialogue box, you will find a “special” button on the bottom left corner which takes you to special dialogue box, then simply select blanks and click OK. However, choosing to convert spreadsheet to web app is the best solution for all such issues.

Find and Replace text

Sometimes, you may want to remove a common leading string like a label followed by a colon and a space or parenthetic phrase at the end of the end of the string, you just need to find instances for that text and then replacing it with no text or any other you want.

Removing Duplicates

With duplicate rows and columns, there are two options available to you, one is to delete them and the other is to highlight them. For highlighting, Select the data and go to home- conditional formatting-highlight cells rule-duplicate values. Then specify the formatting and it is done.

Checking the type of Data in a Cell

Using the Type function, you can easily check the kind of data available in a cell. The function is one of a group of information functions. If the function return with 1, the data type is numeric. If it’s returning with 2, the data type is text.

Highlighting Errors

Opting to convert spreadsheet to web app is the best way out but still, for conditional formatting, select the entire data set, and go to Home-Conditional Formatting-New Rule. In New Formatting Rule Dialogue Box select ‘Format Only Cells that Contain’. Then in the Rule Description, select Errors from the drop down, set the format and click OK. This highlights any error value in the selected dataset.

Changing Text to Lower/Upper/Proper Case

When you import data from text files, the names and titles are not often consistence. For this, LOWER () – Converts all text into Lower Case.

UPPER () – Converts all text into Upper Case.

PROPER () – Converts all Text into Proper Case.

Removing Non-printable Characters

Using the Clean () formula, you can simply remove non printable characters.

Remove Unnecessary Characters from text

Using a macro-enabled workbook helps to keep special characters of a string. Download this Workbook to remove alpha, numeric, or non-numeric characters in seconds.