Cleaning Messy Survey Data as a Data Analyst (Excel Edition)

---

Title:

Cleaning Messy Survey Data as a Data Analyst – Step by Step in Excel

---

Introduction

Have you ever received a dataset that looks like a total nightmare?
Text, numbers, typos, missing values, inconsistent formats — the kind of data that makes you want to close Excel forever.

Welcome to real-life data cleaning, a core skill for any data analyst.

In my recent project, I worked on a survey about the invisible workload of working mothers, and I learned firsthand how to turn messy survey data into analysis-ready Excel tables. Here’s my process.

---

Step 1: Prepare Your Workspace

Always start by preserving the original raw data.

• Copy your raw Excel file:

survey_responses_raw.xlsx → survey_responses_cleaned.xlsx

• Freeze the top row for easy scrolling (View → Freeze Top Row)

• Turn on filters (Data → Filter) to inspect and clean efficiently

Why: This ensures that your raw data is always intact, so mistakes can’t break your analysis.

---

Step 2: Create Clean Columns

Never overwrite raw columns.
For each column you clean, create a new one with a _clean suffix:

• employment_type → employment_type_clean

• paid_hours → paid_hours_clean

Why: Traceability. You can always compare raw vs. cleaned values.

---

Step 3: Standardize Text

• Remove extra spaces: TRIM()

• Convert text to lowercase: LOWER()

• Fix typos manually or with a lookup table

Example: Full time, fulltime, FT → full_time

---

Step 4: Normalize Categories

Use a lookup table to standardize categories like employment type, support type, and yes/no fields.

Why: Consistent categories are key for analysis and visualization.

---

Step 5: Convert Yes/No Fields

• Standardize binary columns:

Y, YES, yes → 1
N, No → 0

• Use IF() and OR() formulas to automate this.

---

Step 6: Extract Numbers from Text

Many survey responses include text in numeric fields:

• "40 hrs" → 40

• "45+" → 45

• "<5" → 5

Tip: Use VALUE(), LEFT(), TEXTBEFORE() formulas in Excel.

---

Step 7: Convert Ranges to Averages

Survey answers sometimes come as ranges:

• "15-20" → 17.5

• "10-12" → 11

Document your assumptions — every step counts for reproducibility.

---

Step 8: Handle Missing Values

Decide per column:

• Numeric → leave blank or fill with median

• Categories → "unknown"

• Binary → NULL

Tip: Document all decisions for your portfolio or blog readers.

---

Step 9: Create Derived Columns

This is where analysis power comes from:

• total_unpaid_hours = household_hours + childcare_hours

• total_work_hours = paid_hours + total_unpaid_hours

• burnout_flag = IF(burnout_clean="often",1,0)

---

Step 10: Validate Your Data

• Use filters, sorting, and conditional formatting

• Look for negative numbers, unrealistic totals, missing critical fields

• Fix errors carefully and systematically

---

Step 11: Document Everything

Create a README.md inside your cleaned data folder:

• What tools/formulas you used

• Cleaning steps and assumptions

• Derived metrics and reasoning

Why: This shows professionalism and is portfolio-ready.

---

Step 12: Commit & Push to GitHub

git add data/cleaned/survey_responses_cleaned.xlsx data/cleaned/README.md
git commit -m "Clean survey data and document assumptions"
git push

• Frequent commits = clean workflow

• GitHub shows recruiters your real data skills

---

Step 13: Next Steps

After cleaning, you’re ready for:

• SQL Analysis: Aggregate, filter, and summarize data

• Tableau Dashboards: Visualize invisible workload patterns

• Storytelling: Turn insights into actionable reports or blog posts

---

Conclusion

Cleaning messy survey data is not glamorous, but it’s the foundation of all good data analysis.

By keeping raw data safe, documenting assumptions, and working systematically, you can:

• Learn real-world Excel cleaning skills

• Build a portfolio-ready project

• Impress recruiters with professional workflow