DATA CLEANING

Act as a senior data analyst and Excel data-cleaning specialist. I will upload a student academic performance dataset. Your objective is to audit, clean, standardize, validate, and restructure it so it is fully ready fo…

Act as a senior data analyst and Excel data-cleaning specialist. I will upload a student academic performance dataset. Your objective is to audit, clean, standardize, validate, and restructure it so it is fully ready for dashboard creation. Do NOT build the dashboard yet.
STEP 1: UNDERSTAND THE DATA
Identify available fields such as Student Name, Student ID, Class, Subject, Assessment, Marks, Maximum Marks, Percentage, Grade, Pass/Fail, Term, Semester, Date, or Assessment Period. Determine whether the dataset is wide format, long format, or another structure. Do not guess unclear columns.
STEP 2: AUDIT DATA QUALITY
Check for duplicate records, spelling variations, blank rows or columns, missing names or marks, marks stored as text, inconsistent student/subject names, extra spaces, invalid numeric values, negative marks, percentages above 100%, marks exceeding maximum marks, inconsistent grades or Pass/Fail labels, merged cells, subtotal rows, and formatting that interferes with analysis.
Do not invent missing marks or academic information.
STEP 3: CLEAN & STANDARDIZE
Where appropriate, remove exact duplicates and unnecessary blank rows/columns, trim spaces, standardize names, subjects, capitalization, dates, and assessment periods, convert numeric fields correctly, and calculate percentages only when Marks Obtained and Maximum Marks are available.
Do NOT invent grading thresholds, pass marks, assessment weights, academic rules, or missing scores.
STEP 4: PREPARE FOR DASHBOARD
Ensure the cleaned dataset can support:
- overall class performance
- subject averages
- individual student performance
- student vs class average
- highest and lowest scores
- grade distribution if available
- pass/fail analysis if valid criteria exists
- ranking if fair comparison is possible
- trends if multiple assessments exist
If needed, restructure the data so each row represents one clear student-subject-assessment observation.
STEP 5: VALIDATE
Verify total students, subjects, valid score records, missing values, duplicates, minimum/maximum scores, invalid percentages, and student-subject relationships. Compare with the original dataset to ensure valid records were not accidentally removed.
STEP 6: CREATE OUTPUT FILE
Create a downloadable Excel workbook containing:
1. Cleaned_Data
2. Data_Quality_Report
3. Data_Dictionary
In Data_Quality_Report, summarize issues detected, corrections made, unresolved issues, affected records, and assumptions avoided.
Keep the original file unchanged.
IMPORTANT:
If the dataset is already clean, correctly structured, and dashboard-ready, do not make unnecessary changes. Clearly state: 'Your data is already suitable for dashboard creation.'
If cleaning is required, perform it and provide the cleaned Excel file for download.
FINAL OUTPUT:
Give me a brief summary of the dataset, problems detected, changes made, unresolved issues, confirmation that the data is dashboard-ready, and the cleaned Excel file if modifications were required.
Do not build the dashboard yet.

Exact prompt text captured from the public post/author comments on facebook.com/kelasmultimediaofficial. · Attribution: Kelas Multimedia

More Data analysis prompts