Data Diaries by Shama

  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...

  1️⃣ Prepare Workspace Copy survey_responses_raw.xlsx → survey_responses_cleaned.xlsx Freeze top row to keep headers visible Turn on filters for easy inspection 2️⃣ Create Clean Columns Never overwrite raw columns Add _clean suffix for every column you clean Example: employment_type → employment_type_clean 3️⃣ Standardize Text Remove extra spaces → TRIM() Convert to lowercase → LOWER() Fix typos manually or using lookup table 4️⃣ Normalize Categories Map all variants to standard values using a lookup table Example: Full time , fulltime , FT → full_time 5️⃣ Convert Yes/No Fields Standardize binary columns → 1 = Yes, 0 = No Use Excel formula with IF() and OR() 6️⃣ Extract Numbers from Text Remove units, symbols, or extra characters Convert strings like 40 hrs , 45+ , <5 → numeric values 7️⃣ Convert Ranges to Averages Examples: 15-20 → 17.5, 10-12 → 11 Document assumptions for ambiguous values 8...

  PHASE 1: Foundation (DONE / ALMOST DONE) ✅ 1. GitHub Setup Repository created Folder structure created README.md added (paste the content we prepared) 📌 Goal: Project looks real and professional from day one. PHASE 2: Define the Problem (VERY IMPORTANT) 2. Write Survey Questions ( survey/survey_questions.md ) You will define: Who the survey is for What you are measuring How each question maps to analysis Sections to include: Demographics (age range, number of kids, job type) Paid work hours Unpaid work hours Mental load (planning, remembering, coordinating) Support system Well-being (stress, burnout, sleep) Career & dreams 📌 This controls your entire analysis. PHASE 3: Data Collection 3. Create Survey Tool: Google Forms or Typeform Make responses anonymous Use scales (1–5) where possible 4. Collect Data Share quietly (DMs, small groups) No pressure on numbers Target: 50–100 responses 5. Sav...