Marksheet Data Cleaning Before Result Preparation
Avoid result errors by cleaning names, roll numbers, subjects and marks data.
Typical problems in marks data
| Problem | Example | Why it breaks results |
|---|---|---|
| Extra spaces | “Asha ” vs “Asha” | Lookups and duplicates fail |
| Numbers stored as text | “78” left-aligned with a green triangle | SUM ignores them, totals wrong |
| Mixed absent codes | AB, A, Abs, -, blank | COUNT/AVERAGE treat them differently |
| Impossible marks | 910 instead of 91, 105 out of 100 | Wrong percentage and rank |
| Inconsistent class/section | 10A, 10-A, X-A | Pivot tables split one class into three |
| Duplicate students | Same roll number twice | Double counting |
| Merged cells in headings | Merged “Term 1” over three columns | Sorting and filtering break |
A 10-step cleaning routine
- Keep the original file untouched; work on a copy named with the date.
- One heading row only; unmerge cells (Home → Merge & Center → Unmerge).
- Trim names: helper column
=TRIM(PROPER(B2)), then Copy → Paste Special → Values. - Standardise class: Find & Replace (Ctrl + H) “10A” → “10-A”, or use a dropdown (see dropdown lists).
- Convert text numbers: select the column → Data → Text to Columns → Finish (or
=VALUE(C2)). - Decide one absent code, e.g. AB, and replace all others with it.
- Find impossible marks: Conditional Formatting → Highlight Cells → Greater Than 100 (and Less Than 0).
- Find duplicates by roll number (Conditional Formatting → Duplicate Values) – see remove duplicates.
- Freeze the header and add Data Validation (whole number 0–100 or AB) for future entry.
- Spot-check 5 students against the original answer sheets/registers.
Handling absentees in formulas
| Goal | Formula |
|---|---|
| Total ignoring AB | =SUM(C2:E2) (SUM skips text automatically) |
| Count subjects attempted | =COUNT(C2:E2) |
| Count absents | =COUNTIF(C2:E2,"AB") |
| Average of attempted subjects | =IFERROR(AVERAGE(C2:E2),"AB in all") |
| Result with absent rule | =IF(COUNTIF(C2:E2,"AB")>0,"ABSENT",IF(COUNTIF(C2:E2,"<33")=0,"PASS","FAIL")) |
Follow your board’s rules for absentees and grace marks; the formulas only apply the rule you choose. Next step: Excel formulas for school results.
Privacy of student data
- Share result sheets only with people who need them; avoid posting full lists with phone numbers in big WhatsApp groups.
- Remove phone numbers, addresses and Aadhaar from files you share; keep a separate master file.
- Password-protect files sent by email (Excel: File → Info → Protect Workbook → Encrypt with Password) and share the password separately.
Marks data की आम समस्याएँ
| समस्या | उदाहरण | Result क्यों बिगड़ता है |
|---|---|---|
| Extra spaces | “Asha ” बनाम “Asha” | Lookups और duplicates fail |
| Text के रूप में numbers | हरे त्रिकोण के साथ बाईं ओर “78” | SUM इन्हें छोड़ देता है, totals ग़लत |
| अनुपस्थिति के मिले-जुले codes | AB, A, Abs, -, ख़ाली | COUNT/AVERAGE इन्हें अलग मानते हैं |
| असंभव marks | 91 की जगह 910, 100 में से 105 | ग़लत percentage और rank |
| Class/section अलग-अलग लिखा | 10A, 10-A, X-A | Pivot tables एक class को तीन में बाँट देते हैं |
| Duplicate students | एक roll number दो बार | दोहरी गिनती |
| Headings में merged cells | तीन columns पर merged “Term 1” | Sorting और filtering टूटते हैं |
10 steps की सफ़ाई routine
- मूल file को न छुएँ; date वाले नाम की copy पर काम करें।
- सिर्फ़ एक heading row; cells unmerge करें (Home → Merge & Center → Unmerge)।
- नाम trim करें: helper column
=TRIM(PROPER(B2)), फिर Copy → Paste Special → Values। - Class एक जैसी करें: Find & Replace (Ctrl + H) “10A” → “10-A”, या dropdown (dropdown lists देखें)।
- Text numbers बदलें: column चुनें → Data → Text to Columns → Finish (या
=VALUE(C2))। - एक absent code तय करें, जैसे AB, और बाक़ी सब उससे बदलें।
- असंभव marks ढूँढें: Conditional Formatting → Highlight Cells → Greater Than 100 (और Less Than 0)।
- Roll number से duplicates ढूँढें (Conditional Formatting → Duplicate Values) – duplicates हटाएँ देखें।
- Header freeze करें और भविष्य की entry के लिए Data Validation (0–100 whole number या AB) लगाएँ।
- 5 students को मूल answer sheets/registers से मिलाकर जाँचें।
Formulas में अनुपस्थित students
| लक्ष्य | Formula |
|---|---|
| AB छोड़कर total | =SUM(C2:E2) (SUM text अपने-आप छोड़ता है) |
| दिए गए subjects गिनें | =COUNT(C2:E2) |
| Absents गिनें | =COUNTIF(C2:E2,"AB") |
| दिए subjects का average | =IFERROR(AVERAGE(C2:E2),"AB in all") |
| Absent नियम के साथ result | =IF(COUNTIF(C2:E2,"AB")>0,"ABSENT",IF(COUNTIF(C2:E2,"<33")=0,"PASS","FAIL")) |
Absentees और grace marks के लिए अपने board के नियम मानें; formulas सिर्फ़ आपका चुना नियम लगाते हैं। अगला कदम: School result के लिए Excel formulas।
Student data की privacy
- Result sheets सिर्फ़ ज़रूरत वाले लोगों से share करें; phone numbers वाली पूरी lists बड़े WhatsApp groups में न डालें।
- Share की जाने वाली files से phone numbers, addresses और Aadhaar हटाएँ; अलग master file रखें।
- Email से भेजी files पर password लगाएँ (Excel: File → Info → Protect Workbook → Encrypt with Password) और password अलग से भेजें।
Frequently Asked Questions
Why does Excel not add some marks?
Those cells contain numbers stored as text (often from copying out of PDFs or portals). Convert them with Data → Text to Columns → Finish or =VALUE().
How should I enter absent students?
Use one consistent code such as AB in every mark cell. SUM and AVERAGE skip text, and COUNTIF(range,"AB") counts absents.
How do I remove extra spaces from names?
Use =TRIM(B2) in a helper column, copy it, and paste back as values. In Google Sheets use Data → Data cleanup → Trim whitespace.
