Data Cleansing in MS Excel

Data cleansing in Microsoft Excel involves the process of identifying and correcting errors or inconsistencies in your data to ensure accuracy and reliability. Here are some common techniques and tools you can use for data cleansing in Excel:

1. Remove Duplicates:Select the range of data.Go to the "Data" tab. Click on "Remove Duplicates."Choose the columns where you want to find duplicates. Click "OK" to remove duplicate values.
2. Filtering:Use the Auto Filter feature to filter out specific values or errors in your data. Click on the filter icon in the column header and choose the criteria you want to filter.
3. Text to Columns:Use the "Text to Columns" feature to split data in a cell into multiple columns. Select the column containing the data.Go to the "Data" tab and click "Text to Columns."Choose the delimiter (e.g., comma, space) to split the data.
4. Trimming Spaces:Use the TRIM function to remove leading and trailing spaces from text.Example: =TRIM(A1) will remove extra spaces from the text in cell A1.
5. Find and Replace:Use the "Find and Replace" feature to search for specific values and replace them with correct ones. Press Ctrl + H to open the Find and Replace dialog.
6. Correcting Case:Use the UPPER, LOWER, or PROPER functions to convert text to uppercase, lowercase, or proper case.Example: =PROPER(A1) will convert the text in cell A1 to proper case.
7. Error Checking:Excel has error-checking functions like IFERROR to handle errors in formulas.Example: =IFERROR(formula, "Error Message") will display a custom error message if the formula returns an error.
8. Conditional Formatting:Use conditional formatting to highlight cells that meet specific criteria.For example, highlight cells that contain errors or values outside a certain range.
9. Data Validation:Apply data validation rules to restrict the type of data that can be entered in a cell.Go to the "Data" tab, click on "Data Validation," and set validation criteria.
10. Check for Inconsistencies:Use functions like VLOOKUP or COUNTIF to check for inconsistencies or discrepancies in your data.

Remember to make a backup of your data before performing extensive data cleansing to avoid accidental data loss. Additionally, these steps may vary slightly depending on your version of Excel.

For a Real time application , please follow the below youtube link:

https://youtu.be/wOO0VKFqA_s