Data cleaning is one of the most critical skills for a Data Analyst. No matter how strong your formulas or dashboards are, unclean data can completely break your analysis. In Lecture 09 of the Data Analytics Full Course 2026, we focus entirely on cleaning raw data using Excel, the way it is done in real corporate environments.
This lecture acts as a bridge between Excel formulas and data visualization, ensuring your data is analysis-ready before moving to charts and dashboards.
Why Data Cleaning is Important in Excel?
In real-world projects, data is never perfect. You will often encounter:
- Extra spaces
- Inconsistent text formatting
- Blank cells
- Errors due to formulas
- Combined columns (e.g., Department + Region)
- Poorly formatted tables
Interviewers frequently test how well you clean data, not just how many formulas you know. This lecture prepares you for both interviews and real projects.
What You’ll Learn in This Lecture
This session covers practical Excel data cleaning techniques using hands-on tasks instead of theory.
1. Adjusting Row Height & Column Width
You learn how to:
- Manually adjust row height and column width
- Automatically fit rows and columns using AutoFit
- Improve data readability instantly
This is often ignored but is essential for clean reporting.
2. Data Validation (Dropdown Lists)
You’ll understand how to:
- Create dropdown lists using Data Validation
- Use them for task tracking and status columns
- Prevent incorrect data entry
This is commonly used in to-do lists, trackers, and operational dashboards.
3. Text Formatting Using Excel Functions
LOWER() Function
Converts text to lowercase for consistency.
UPPER() Function
Converts text to uppercase when needed.
PROPER() Function
Formats names correctly by capitalizing only the first letter of each word.
These functions are extremely useful when dealing with client names, employee records, and contact data.
4. Removing Unwanted Text (Find & Replace)
You learn how to:
- Remove text inside parentheses
- Use wildcards (
*) effectively - Clean thousands of rows in seconds
This technique is widely used in CRM and customer datasets.
5. Removing Extra Spaces Using TRIM()
The TRIM() function helps:
- Remove leading and trailing spaces
- Fix spacing issues between words
- Prepare text for accurate matching and analysis
A must-know function for every data analyst.
6. Splitting Columns (Text to Columns)
You’ll learn how to:
- Separate combined data like
Department_Region - Use Text to Columns with custom delimiters
- Avoid overwriting existing columns
This is a very common task in raw business datasets.
7. Handling Blank Cells with NA
You’ll use:
- Filters to identify blank values
- Replace blanks with NA
- Ensure consistency across numeric and text columns
Clean handling of blanks prevents errors in calculations and dashboards.
8. Replacing Errors Using IFERROR()
Errors like #VALUE! or #DIV/0! can break reports.
Using IFERROR(), you’ll learn how to:
- Replace errors with NA or 0
- Keep calculations clean and readable
- Improve dashboard reliability
9. Formatting Data as a Table
You’ll see how to:
- Convert raw data into Excel Tables
- Enable built-in sorting and filtering
- Improve structure and presentation
Formatted tables are easier to analyze and scale.
10. Sorting & Filtering Data
Finally, you’ll learn how to:
- Sort data by profit, revenue, or any metric
- Use filters for quick insights
- Prepare data for charts and dashboards
Who Should Learn This?
This lecture is perfect for:
- Aspiring Data Analysts
- Students preparing for Excel interviews
- Professionals working with Excel reports
- Anyone transitioning into analytics roles
What’s Next?
In the next and final lecture, you’ll learn:
- All major Excel chart types
- When to use which chart
- How to visualize clean data effectively
This completes the Excel foundation for Data Analytics.
Final Thoughts
Data cleaning is not optional — it is a core responsibility of a Data Analyst. Mastering these Excel techniques will:
- Improve your confidence
- Strengthen interview performance
- Make your analysis more accurate and professional
Practice along with the provided datasets and you’ll be job-ready with Excel.