Excel Data Cleaning & Analysis Guide
1. Introduction
Excel is more than a spreadsheet—it’s a powerful data-analysis tool. Analysts use it to inspect, validate, filter, summarize, and prepare raw data before deeper analysis.
The quality of insights depends on the quality of data. That’s why data cleaning is the foundation of effective analysis.
This guide demonstrates Week 1 Excel concepts using an employee dataset with fields like employee IDs, names, departments, gender, hire dates, salaries, education level, and performance scores. The raw file contains common issues: inconsistent capitalization, blanks, duplicates, varying department names, and misformatted dates/currency.
By applying Excel’s formatting tools, text functions, filters, conditional formatting, numerical functions, and date functions, we transform messy data into an analysis-ready dataset.
2. Why Data Cleaning Matters
- Garbage in, garbage out: dashboards or predictions are misleading if data is flawed.
- Standardization makes datasets transparent, usable, and valuable.
- Examples:
- “IT”, “I.T.”, and “Information Tech” → treated as different departments unless standardized.
- Duplicate employee IDs → inflate totals.
- Blank performance scores → missing information.
- Dates saved as text → unreliable for tenure calculations.
Best practice: keep raw data separate, create a cleaned worksheet, and document your cleaning logic.
3. Dataset & Initial Inspection
- Dataset size: 877 employee rows (with duplicates).
- Issues: inconsistent formats in IDs, names, departments, salaries, and dates.
- First steps:
- Autofit Column Width for readability.
- Freeze top row for easier navigation.
- Apply a structured cleaning process.
3.1 Standardizing Employee IDs
- Remove
emp-prefix for uniform IDs. - Use Conditional Formatting to highlight duplicates.
- Action:
- Delete exact duplicates.
- Retain one ID if records differ.
- Result: reduced dataset to 870+ entries.
3.2 Cleaning Names
- Apply
=PROPER(text)→ converts names to proper case. - Example:
john→ John. - Replace raw columns with corrected values.
3.3 Standardizing Departments
- Filter and rename department names for consistency.
- Eliminates spelling variations.
3.4 Formatting Salaries & Bonuses
- Convert to KES currency format.
- Sort values for comparison.
- Replace other currencies with standardized KES.
3.5 Dates & Employment Details
- Reformat Hire Dates consistently.
- Sort newest → oldest for recruitment timeline.
- Standardize categorical fields: Gender, Performance Score, Marital Status, Education Level, Employee Type, Office Location, Remote Work Status.
- Standardize numerical fields: Experience, Project Count, Promotion Year, Training Hours, Feedback Score.
3.6 Filtering Data
Filters allow quick inspection:
- Employees in IT department.
- Female employees only.
- Salaries > 80,000.
- Blank performance scores.
Use Number Filters for thresholds (e.g., top 10 salaries).
4. Identifying Data-Quality Issues
4.1 Conditional Formatting
Highlights problems without deleting data:
- Duplicate values.
- Blank cells.
- Dates before today.
- Values above/below thresholds.
Example: Employee ID 10407 appears twice → verify before deleting.
4.2 Find & Replace (Ctrl + H)
Quick fixes for repeated issues:
- Replace “Finanace” → Finance.
- Replace “N/A” → blank.
- Replace unwanted characters in IDs.
Use carefully—preview matches before replacing.
5. Cleaning Text with Functions
Function Purpose Example=PROPER(text)
Capitalizes first letter
aNNa mWANGI → Anna Mwangi
=LOWER(text)
Converts to lowercase
JAMES → james
=UPPER(text)
Converts to uppercase
it → IT
5.1 Removing Extra Spaces
=TRIM(A2) → removes leading, trailing, and repeated spaces.
5.2 Measuring & Extracting Text
-
=LEN(text)→ counts characters. -
=LEFT(text,n)→ extracts from left. -
=RIGHT(text,n)→ extracts from right. -
=MID(text,start,n)→ extracts middle. -
=CONCAT(text1,text2,...)→ joins text.
6. Numerical & Statistical Functions
6.1 Arithmetic Operators
-
+Addition -
-Subtraction -
*Multiplication -
/Division -
^Exponent
Example:
- 5% salary increase →
=G2*0.05 - New salary →
=G2+(G2*0.05)
6.2 Aggregate Functions
-
=SUM(range)→ total -
=AVERAGE(range)→ mean -
=MEDIAN(range)→ middle value -
=MODE(range)→ most frequent -
=MIN(range)→ lowest -
=MAX(range)→ highest
7. Counting & Conditional Analysis
7.1 Count Functions
-
COUNT→ numeric cells only. -
COUNTA→ nonblank cells. -
COUNTBLANK→ empty cells.
7.2 Conditional Counting
-
COUNTIF(range,"IT")→ IT employees. -
COUNTIFS(range1,"IT",range2,"Female")→ Female IT employees.
7.3 Conditional Aggregation
-
SUMIF→ total salaries by condition. -
SUMIFS→ multiple conditions. -
AVERAGEIF→ average by condition. -
AVERAGEIFS→ average with multiple conditions.
8. Dates & Time-Based Cleaning
-
=TODAY()→ current date. -
=NOW()→ current date & time. -
=YEAR(F2)→ extract year. -
=MONTH(F2)→ extract month. -
=DAY(F2)→ extract day.
8.1 DATEDIF
- Tenure in days →
=DATEDIF(F2,TODAY(),"d") - Months →
"m" - Years →
"y"
8.2 NETWORKDAYS
- Working days between dates →
=NETWORKDAYS(F2,TODAY())
9. Recommended Cleaning Workflow
- Preserve raw data.
- Copy into cleaned worksheet.
- Inspect with filters & conditional formatting.
- Identify duplicates visually.
- Standardize text with TRIM, PROPER, LOWER, UPPER.
- Validate text with LEN, LEFT, RIGHT, MID, CONCAT.
- Check completeness with COUNT functions.
- Summarize with COUNTIFS, SUMIFS, AVERAGEIFS.
- Use date functions for tenure/workdays.
- Review, document assumptions, then analyze.
Conclusion
Data cleaning must happen before interpretation.
- Conditional formatting → detect issues.
- Filters → inspect subsets.
- Find & Replace → correct repeated values.
- Text functions → standardize labels.
- Aggregate functions → summarize numbers.
- Conditional functions → reveal group patterns.
- Date functions → calculate time-based insights.
By following a structured workflow, analysts ensure datasets are consistent, reliable, and ready for advanced analysis like PivotTables, dashboards, and statistical modeling.