Thursday, September 24, 2020

This is an age of data and information. Before working on data, we need to make sure it is accurate and reliable. The health of data has to be maintained to make effective use of it. Because unclean data may be feasible for a while, but in the future, when it gets bigger and bigger, these small problems can create huge problems.

These issues may arise due to missing values, spaces, duplicates, and typos, etc. there are many solutions available in the market for a wide range of data cleaning solutions. But we can make use of data effectively and quickly using excel features and functions.
A few ways to clean your data in excel are discussed below. Read and enjoy.

Blank Spaces:

You can add extra space in the data accidentally. These need to be removed to make data in excel clean. In big data, they are difficult to spot, even if you spot spaces between words and numbers, it is challenging to locate and remove the spaces at the end of the lines, i.e., trailing spaces.
But to locate these spaces and remove them, Excel provides the TRIM function. It is used as TRIM(cell or text). This function will remove all the double and trailing spaces in the text except the single spaces.


In big data sets, there is a frequent problem of duplication. It increases the size of the database and can affect your final results too. Excel provides you in-built feature to remove duplication. Manually it takes a good deal of time to locate duplicates, and due to human nature, you may overlook a few while finding them.
No worries Excel provides a Remove Duplicate feature to solve this problem. First, you need to find out which data is duplicated and where. So In the HOME tab, click on conditional formatting then go to HIGHLIGHT CELL RULES then click on DUPLICATE VALUES. The values will be highlighted, then if you want to delete them, select the values, and then click on the Data tab. Click on REMOVE DUPLICATES button to get rid of duplication.

Blank Cells:

The blank cell can wreak havoc in Excel when you are entering any formula or any calculation. The blank cell may cause a lot of trouble. You need to remove them before using data for any function. The effective way to counter a blank cell is to enter 0 if you are dealing with numbered data. Manually entering one by one in all blank cells may take hours.
So to reduce cleaning time, Select the data, and press F5. Click on the SPECIAL and then select BLANKS and then click OK. After that, all blank spaces will be selected. Enter text or 0 once and then press CTRL and ENTER together to replicate the value in all the blank cells.

Find and Replace:

Find and Replace function in Excel makes it easy to locate and change what is required without a hassle. Click on the HOME tab and Select Find and Replace OR Press CTRL+F to open the dialog box, which will perform the aforementioned function.

Data Types:

Sometimes when we import files or accidentally press an apostrophe, the data in numbers are converted into text format. To convert back text data into its original form, i.e., numerical or numbered data, type 1 in an empty cell. Select that cell and press CTRL and C together.  Then select the data that you want to convert and go to Paste and click on Paste Special. A dialog box will appear. In that box under operations heading, select the MULTIPLY option, then press OK, and you are done.


Due to Excel functionality, when we import a file at times, it confines itself in one cell. You can allocate this data into multiple cells by clicking on the Data tab and then on TEXT TO COLUMNS. A dialog box will appear. Select the data type, delimiter, and format to complete the process.


You may require at some stage to remove formatting and add a new one according to specific needs. Do it by yourself in a few seconds. First, select the whole or part of data from where you want to remove formatting the go to HOME tab. Click on SELECT; a drop-down menu will open select Clear Formats, and you are done.

These are the basic techniques which save you a lot of time on your workday and a chunk of money too. Because you do not have to hire professionals to look out for basic mistakes in your Excel data set. Do it yourself and happy cleaning.

By: Qasim Saleem

« Back