Cleaning up Excel data without using formulas is a common approach, especially when working with smaller datasets or when formulas are unnecessary. Here’s how you can clean up data using Excel’s built-in tools and features:
1. Remove Duplicates Go to the Data tab, click Remove Duplicates, and select the columns where you want to check for duplicates. Excel will remove any duplicate rows.
2. Trim Spaces Manually If you have cells with extra spaces, you can clean them up manually by editing the cell or using the Find & Replace tool. Go to Home > Find & Select > Replace, enter a space in the "Find what" field, and leave the "Replace with" field blank.
3. Change Text Case To change text case without a formula, you can use Flash Fill: In a blank column, type the first value in the desired format (e.g., in all caps). Start typing the second value, and Excel should automatically suggest the rest. Press Enter to apply the suggestions.
4. Use Find & Replace Replace incorrect or unwanted values
5. Text to Columns If you need to split data in a single column (e.g., separating first and last names): Select the column, go to the Data tab, and click Text to Columns. Choose Delimited if your data is separated by commas, spaces, etc., and follow the wizard to split the data into multiple columns.
6. Clear Formatting If you have inconsistent formatting, you can clear all formatting without affecting the data.
7. Use Filters You can filter data to spot and delete empty rows or rows that contain certain values.
8. Remove Blank Rows To remove blank rows manually:
9. Data Validation Apply data validation to control the type of data entered into cells.
10. Conditional Formatting Highlight specific data, such as duplicates, using conditional formatting.
11. AutoFilter for Error Checking Use AutoFilter to check for inconsistencies: Go to Data > Filter, and then filter columns for specific criteria to identify potential errors.
12. Delete Unnecessary Columns and Rows To delete unnecessary rows or columns.
These are some the task we perform on MS Excel.
