Data Cleaning in Excel | 8 Tricks to Make You a Pro User

Introduction

In this tutorial, we will cover eight essential data cleaning tricks in Excel that will elevate your skills and make you more efficient as an analyst. Whether you're preparing data for a report or cleaning up a dataset from your work at Amazon, these techniques will help ensure your data is accurate and well-organized.

Step 1: Separate Text with Text to Columns

• Select the column containing the text you want to separate.
• Go to the "Data" tab in the ribbon.
• Click on "Text to Columns."
• Choose "Delimited" if your data is separated by commas, spaces, or other characters, or "Fixed width" if the data is aligned in columns.
• Follow the prompts to specify the delimiter and finish the process.

Step 2: Remove Duplicates

• Select the range of cells or the entire column where duplicates may exist.
• Go to the "Data" tab and click on "Remove Duplicates."
• A dialog box will appear where you can choose which columns to check for duplicates.
• Click "OK" to remove duplicates, and Excel will notify you how many duplicates were removed.

Step 3: Standardize Text with Proper Function

• Use the PROPER function to correct inconsistent capitalization in your dataset.
• In a new column, enter the formula:

  =PROPER(A1)
  

• Drag the fill handle down to apply the formula to other cells.

Step 4: Remove Extra Spaces with TRIM

• Use the TRIM function to eliminate unnecessary spaces from text entries.
• In a new column, enter the formula:

  =TRIM(A1)
  

• Fill down to apply the formula to the entire column.

Step 5: Automatically Separate Data with Flash Fill

• Start typing the desired format of your data in the adjacent column.
• Excel will recognize the pattern and suggest a fill.
• Press "Enter" to accept the Flash Fill suggestion or continue typing to guide Excel.

Step 6: Convert Numbers to Percentages

• Select the cells containing numbers you want to convert to percentages.
• Right-click and choose “Format Cells.”
• Select “Percentage” and specify the number of decimal places you prefer.
• Click "OK" to apply the formatting.

Step 7: Convert Text to Values

• If you have numbers formatted as text, you can convert them to numerical values.
• Select the cells, then click on the warning icon that appears.
• Choose “Convert to Number” from the options provided.

Step 8: Remove Blank Cells

• Highlight the range of cells from which you want to remove blanks.
• Go to the "Home" tab, click on "Find & Select," and select "Go To Special."
• Choose "Blanks" and click "OK."
• Right-click on any of the highlighted blank cells and choose "Delete" to remove them.

Conclusion

By mastering these eight data cleaning tricks in Excel, you can enhance your ability to work with datasets effectively. This will not only improve your analysis but also make your reports look professional and polished. Consider practicing these techniques on real datasets to solidify your skills. Happy data cleaning!