Top 10 Methods of Data Cleaning in Excel
→ Data cleaning in Excel is very important for any business or professional for further analysis of data.
→ Data cleaning means removing errors, inconsistencies, and incomplete data from an Excel sheet.
→ This process may be a very time-consuming and tedious task, but we can easily do this with the help of the different functions and formulas in MS Excel.
→ There are many different methods available for data cleaning in Excel out of that we will learn different Top 10 Methods of Data Cleaning in Excel
Table of Content
- Top 10 Methods of Data Cleaning in Excel
- TRIM Function
- Remove Duplicates
- Text to Columns
- Filter Blank Cells
- CLEAN Function
- LEFT RIGHT MID Functions
- Go To Special
- UPPER LOWER PROPER Functions
- TRANSPOSE Function
- SUBSTITUTE Function
- Benefits of Data Cleaning in Excel
- Tips for Data Cleaning in Excel
1. TRIM Function
→ With the help of the TRIM_function in Excel we can remove all unnecessary spaces from text except for single spaces between words.
→ TRIM_function returns the text value or string after removing blank spaces except for single spaces between words.
→ This function is used to clean up data and make it easier to read and work with that data.
→ MS Excel TRIM_Function is the best function for us when we want to remove leading, trailing, and double spaces for our data set.
➞ The syntax of the TRIM_Function is:
=TRIM(text) or =TRIM(cell)
➞ The syntax parameters:
→ text - text means any text or string from which we need to remove leading, trailing, and double spaces.
→ cell - cell means cell reference that we need to give reference of that cell from where we need to remove leading, trailing, and double spaces.
Example of TRIM Function:
→ With the help of the TRIM_Function we can remove the leading space, trailing space, and double spaces from cell or text.
→ For your better understanding refer to the below example.
→ In the below picture we can easily see the two different methods for applying the TRIM_Function.
→ The first method is to apply the TRIM_Function with cell reference and in the second method we have given the text reference.
2. Remove Duplicates
→ MS Excel has an inbuilt feature for removing duplicate values from the table the name of this feature is Remove Duplicates.
→ This tool is located under the Data Tab located in the menu bar.
→ To use this tool, firstly we need to select either row, column, or table from where we need to remove the duplicate data.
→ Then go to the Data Tab ➞ Remove Duplicates
Example of Remove Duplicate
→ Refer to the below example of Remove Duplicate for a better understanding.
→ In the below picture we can see the easy 3-step approach to remove the duplicate data from the table or column.
Step-01: Select the table or column from where we need to remove the duplicate data
Step-02: Go to the Data Tab ➞ Remove Duplicates then click on the Remove Duplicate option.
Step-03: You have a new data set after removing the duplicate data.
3. Text to Columns
→ With the help of the Text to Columns features of Excel we can separate a text into different columns based on some delimited or any fixed width.
→ There are two options to use text to columns in Excel.
→ The first option is, we can separate the text with the help of a delimiter.
→ The very famous delimiter is a comma, space, hyphen, etc.
→ The second method for Text to Columns is we can use a fixed defined width to separate a text in the adjacent columns.
→ We can navigate the Text to Columns feature as go to the Data Tab ➞ Text to Columns
Example of Text to Columns
→ Now we take one example of Text to Columns for a better understanding.
→ Text to Columns is simple six steps of approach for separating data from one column to different columns that we can easily understand from the below pictures.
Step-01: Select the column.
Step-02: Go to the Data Tab ➞ Text to Columns
Step-03: Click on the Text to Columns options under Data Tools
Step-04: Now Select the Delimited option under Step 1 of 3 in Text to Columns Wizard
Step-05: After that under the delimiters option check on the other and mention "@" as a delimiter and click on Next under Step 2 of 3 in Text to Columns Wizard
Step-06: The final step is to mention the destination cell where we need to enter separated data and then press Finish. Now you can easily see the separated data in the columns B, and C.
4. Filter Blank Cells
→ With the help of the Filter feature of Excel we can easily get rid of the blank cells from any column.
→ The Filter option is available in the Home Tab of Excel
→ So you need to navigate as go the Home Tab ➞ Sort & Filter ➞ Filter
Example of Filter Blank Cells
→ Refer to the below example of a Filter feature of Excel for a better understanding.
→ With the help of the below picture we will understand this process in simple 5 steps.
Step-01: Select the column where we need to filter blank cells
Step-02: Go to the Home Tab ➞ Sort & Filter
Step-03: Now Home Tab ➞ Sort & Filter ➞ Filter Now click on Filter and you will get one dialogue box
Step-04: In this dialogue box uncheck on the (Blanks) option and press OK
Step-05: Now you will get the data that filters or hides the blank cells
5. CLEAN Function
→ With the help of the CLEAN_Function in Excel we can remove line breaks and all nonprintable characters from a string.
→ CLEAN_Function returns the text value or string after removing nonprintable characters and line breaks.
→ This function is used to clean up data with line breaks and all nonprintable characters from a string and make it easier to read and work with that data.
→ MS Excel CLEAN Function is the best function for us when we want to remove nonprintable characters from the data set.
➞ The syntax of the CLEAN_Function is mentioned below.
=CLEAN(text) or =CLEAN(cell)
➞ The syntax parameters:
→ text - text means any text or string from which we need to remove line breaks and all nonprintable characters from a string.
→ cell - cell means cell reference that we need to give reference of that cell from where we need to remove line breaks and all nonprintable characters from a string.
Example of CLEAN Function:
→ With the help of the CLEAN_Function we can remove line breaks and the nonprintable characters from cell or text.
→ For more clarity we will take on example.
→ From the below picture we can see different methods for applying CLEAN_Function, it's formulas, and outputs.
6. LEFT RIGHT MID Functions
→ The Microsoft Excel LEFT_Function allows us to trim certain parts of a string or text and extract a sub-portion from a text or string that is starting from the left-most character.
→ The Microsoft Excel RIGHT_Function allows us to trim certain parts of a string or text and extract a sub-portion from a text or string that is starting from the rightmost character.
→ With the help of the Microsoft Excel MID_Function we can extract a substring from a string that is starting at any position.
➞ The syntax for the LEFT_Function is:
=LEFT(text, [number_of_characters]) or =LEFT(cell, [number_of_characters])
➞ The syntax parameters:
→ text - text means the string or text from that we need to extract substring or subtext.
→ cell - cell means cell reference that we need to give reference of that cell from where we need to extract substring or subtext.
→ number_of_characters - this is an Optional parameter. It indicates the number of characters that we wish to extract starting from the left-most character. If this parameter is omitted, only 1 character is returned.
➞ The syntax for the RIGHT_Function is:
=RIGHT(text, [number_of_characters]) or =RIGHT(cell, [number_of_characters])
➞ The syntax parameters:
→ text & cell - Both are similar to the LEFT_Function.
→ number_of_characters - this is an Optional parameter. It indicates the number of characters that we wish to extract starting from the right-most character. If this parameter is omitted, only 1 character is returned.
➞ The syntax for the MID_Function is:
=MID(text, start_position, [number_of_characters]) or
=MID(cell, start_position, [number_of_characters])
➞ The syntax parameters:
→ text & cell - Both are similar to the LEFT_Function.
→ start_position - the position in the string that we will begin extracting from. The first position in the string is 1
→ number_of_characters - The number of characters that we wish to extract. It is mandatory when we are using the MID_Function in Excel.
Example of LEFT RIGHT MID Functions
→ The Microsoft Excel LEFT RIGHT and MID Function allows us to trim certain parts of a string or text and extract a sub-portion from a text or string that is starting from the left, right, and middle characters.
→ For your better understanding refer to the below example.
→ From the below picture we can see different methods for applying LEFT, RIGHT, and MID Functions, their formulas, and outputs.
7. Go To Special
→ With the help of the GO To Special feature of MS Excel we can select the blank cells and perform various activities as per our requirements.
→ Now Select the table or column or row where we need to select the blank cells.
→ Then Press Ctrl + G or F5 to display the Go To dialog box.
→ Click on the Special button.
Example of Go To Special
→ For more clarity check below case study.
→ With the help of the below picture we will understand this process in simple 5 steps.
Step-01: Select the column where we need to select blank cells
Step-02: Then press Ctrl+G or F5 to open Go To dialogue box
Step-03: Now click on the Special option on that dialogue box
Step-04: It will redirect to another dialogue box in this box select on Blanks
radio button and press OKStep-05: Now you will get the data that has all blank cells have been selected.
8. UPPER LOWER PROPER Functions
→ The Microsoft Excel UPPER_Function helps us to convert text/string to all uppercase.
→ The MS Excel LOWER_Function helps us to convert text/string to all lowercase.
→ The Microsoft Excel PROPER_Function sets the first character in each word to uppercase and the rest to lowercase
→ With all the above three functions only text/characters will change the case if other than the characters are available in the text or sting then it will stay as it is.
➞ The syntax for the UPPER, LOWER, and PROPER Functions are:
=UPPER(text) or =UPPER(cell)
=LOWER(text) or =LOWER(cell)
=PROPER(text) or =PROPER(cell)
➞ The syntax parameters:
→ text - text/string we need to convert into uppercase, lowercase, or the first character in each word will be converted to uppercase and all remaining characters converted to lowercase.
→ cell - cell means to give a cell reference instead of a text reference.
Example of UPPER LOWER PROPER Functions
→ All the above three functions allow us to change the case of the text/string to upper, lower, or proper case.
→ For more clarity, check the below case.
→ From the below picture we can see different methods for applying UPPER, LOWER, and PROPER Functions, their formulas, and outputs.
9. TRANSPOSE Function
→ The Microsoft Excel TRANSPOSE_Function returns a transposed range of cells.
→ In simple words, we can say that this function converts data verticle into horizontal and vice versa.
→ Also, we can say that this function flips the orientation of the given range or array.
→ The TRANSPOSE_Function returns a transposed range of cells.
➞ The syntax for the TRANSPOSE Function is:
=TRANSPOSE(range)
➞ The syntax parameters:
→ range - The range of cells that we want to transpose.
Note
→ The range value in the TRANSPOSE Function must be entered as an array.
→ To enter an array, enter the value and then press Ctrl+Shift+Enter.
→ This will place {} brackets around the formula, indicating that it is an array.
Example of TRANSPOSE Function
→ The Microsoft Excel TRANSPOSE Function converts data verticle into horizontal and vice versa.
→ Also, we can say that this function flips the orientation of the given range or array.
→ For your better understanding refer to the below example.
→ From the below picture we can easily understand the formula and method to apply the TRANSPOSE function, formula, and output.
10. SUBSTITUTE Function
→ The Microsoft Excel SUBSTITUTE_Function replaces one set of characters with another.
→ The SUBSTITUTE Function returns a string/text value.
→ This function is case-sensitive and does not support wildcards.
→ This function can replace one string at a time. If we want to replace more than one string at a time then we can use the nested SUBSTITUTE Function.
➞ The syntax for the SUBSTITUTE Function is:
=SUBSTITUTE(text, old_text, new_text, [nth_appearance])
➞ The syntax parameters:
→ text - The original string that we will use for substitution operation.
→ old_text - The existing characters that we need to replace with new ones.
→ new_text - The new characters to replace old_text with.
→ nth_appearance - This is an optional parameter. It is the nth appearance of the old text that we wish to replace. If this parameter is omitted that means we are not giving any value to this parameter, then all occurrences of old_text will be replaced with new_text.
Example of SUBSTITUTE Function
→ The SUBSTITUTE Function helps us to replace one set of characters with another.
→ For your better understanding refer to the below example.
→ From the below picture we can easily understand the different examples of SUBSTITUTE_Functions, formulas, and outputs.
Benefits of Data Cleaning in Excel
→ Improved accuracy: Data cleaning leads to more accurate results during analysis.
→ Increased efficiency: Clean data can be processed very fast and easily, which can save time and resources.
→ Reduced errors: Clean data can create fewer errors which leads to improve decision-making.
→ Improved productivity
→ More quick analysis can save time.
Tips for Data Cleaning in Excel
→ Start by creating a backup copy of the file that will help to protect the data if any error occurs during cleaning.
→ If possible then use the Data Validation feature to prevent errors.
→ Use macros to automate repetitive tasks where ever possible.
→ We can also use conditional formatting to identify and separate errors.
→ Save the file after each step of the cleaning process.
👉 Also Navigate Our Popular Category:
➨ Lookup & Reference Functions
Post a Comment