What is Data Cleansing in Excel?

EM Admin
Apr 14, 2020
0 Comments

Also called data cleaning, data cleansing is the process of removing every inconsistency or oddity from databases.

You may have a list of incomplete email ids or some duplicate profiles can be there bringing in inconsistencies that you want to filter out. Web scraping can also cause typos and formatting errors. Besides, you cannot control them from getting in when you migrate or convert any PDF or Tiff or any other file in your digital warehouse. These and a lot more data management, mining or processing practices can put several barriers in your way to catch up with the correct information.

So, it is really important to get them out of your excel files if you want to analyse correctly or if you need to know the best strategy that can actually work well. Excel functions can help you to comb those oddities without facing any challenge.

Let’s get started to get through some commonly used excel functions that can make the difference:

De-duplicating with EXACT function

The values in your spreadsheet can be a clone entry when you append or migrate or convert files. Here, the Exact function can help you to test if two or more text/ strings are exactly equal. It returns “true” as its value once this function is executed.

You just need to put the reference cells as its parameters, as =EXACT (cellref1, cellref2) . Hold the CTRL and SHIFT keys while hitting down the ENTER to get the accurate result.

Integrating tables with VLOOKUP

How do you collate various tables from different locations?

Certainly, you switch to and from the location where the tables are one after another to copy and paste it. It’s tedious, for sure. Sometimes, you forget putting a few tables or you feel like all messed up, which eventually causes errors.

Here, Vlookup fu nction can be the best bet you can have. It helps you to retrieve and get the exact match that you look for. Put values as = VLOOKUP (value, table, col_index, [range to lookup])

This way, you can quickly integrate new data into already classified fields or columns.

Trimming Text, Removing Space

An extra space in the email id can show it up as an invalid address. Despite being correct, you may take it as a wrong one. There may have some instances when you put an extra space while making a data entry. Such meager mistake can cost you on leads, which can be all yours through email marketing.

Here, the TRIM function can help you to get off that prospective challenge. You can put the value inside this function as =TRIM(cellref1), to remove all extra spaces in between.

Standardise with PROPER & LOWER function

When you make entries in a flow, you oftentimes forget putting text in the upper or lower case. Manually, it may suck hours and efforts.

You can apply PROPER function to capitalize the text strings, as =PROPER (cell ref).This will give you the view of standardized text wherein initials of every word would be a capital letter.

If you want the flip of this result, i.e. to show up values in the lower case, use =LOWER(cell ref). As you put values inside and hit the enter button, the text will change into lower case.

Combine text with “&” operator

Let’s say, you many have names in a column and the surnames in another one. Now, you want to combine them together. However, you have “merge” function to do so. But, it can convert them into a mess.

So, you should try to pair them up with =cellref1&“ ”cellref2. It will keep the space in between the two texts and the final look of the content would be properly aligned or standardized. The incomplete, irrelevant, duplicate and unformatted content in the Excel file or spreadsheet need to swipe them with consistent values. Whatever functions or hacks you opt in, they make it error free and valuable.

Data

Data Cleansing