Six Thousand Rows, Zero Total: A Beneficiary Sheet That Will Not Add Up
A panchayat office has one desktop with a spreadsheet programme and a single shared login named 'panchayat'. An operator has keyed 6,240 beneficiary rows for a housing scheme. Problems surface one day before the file is due at the block: (i) the Amount column shows entries like '₹ 25,000' and '25000 ' and the formula =SUM(F2:F6241) returns 0; (ii) the block reports 41 Aadhaar numbers appearing twice, and the operator proposes selecting the whole sheet and using Data > Remove Duplicates; (iii) ward names were typed into merged cells, so sorting by ward scrambles rows; (iv) a junior clerk has been emailed the 'panchayat' password so he can work from home; (v) the only copy of the file is on the desktop. The Mukhiya wants the sheet delivered tomorrow and asks you, the computer-literate secretary, to sort it out without retyping anything.
1. Explain why SUM returns 0 and give a safe fix that does not retype 6,240 rows.
Read the worked answer
SUM returns 0 because the cells are text, not numbers. The currency symbol, the non-breaking or ordinary spaces and the thousands comma make the spreadsheet treat the entry as a label; SUM ignores labels, so the total is zero even though the column looks full.
The safe fix works on a copy. First save the original as 'master' and never edit it. Then insert a helper column and use a formula that strips the symbol, the spaces and the comma and converts the remainder: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(F2),"₹",""),",","")," ","")). Fill it down and total the helper column. Check the total against a manual sum of ten sampled rows before trusting it. Only then copy the helper column and Paste Special > Values over the original column, and keep the master file as the fallback. The principle: convert on a copy, verify on a sample, paste values, keep the original.
2. Is the operator's plan to use Remove Duplicates on the whole sheet sound? What would you do instead, and what could go wrong either way?
Read the worked answer
No. Remove Duplicates deletes rows permanently, keeps the first match by default, and judges on all selected columns - so two rows that differ in any ward or name field will both survive, and two rows that are genuinely the same person may lose the entry carrying the correct bank details. On a scheme list the deleted row is a beneficiary, and there is no undo after saving.
Instead, flag before deleting. In a new column put =COUNTIF($D$2:$D$6241,D2) and filter for values above 1, or use conditional formatting on the Aadhaar column. Examine each flagged pair: same person entered twice, or two family members sharing a number by keying error? Resolve by comparing name, father's name and bank account. Record the decision in a remark column and a change log, and keep the master file untouched. Report the 41 cases to the block with the remark, rather than silently dropping 41 rows from a list the block will verify.
3. The Mukhiya wants the file sent tonight. Address the merged-cell problem, the shared password and the missing backup, in order of risk.
Read the worked answer
Rank by consequence, not by convenience.
Password first. A shared login emailed to a home computer means every entry is attributable to nobody, and the file can be altered outside the office. Change the password, create separate user accounts so each entry carries a name, and tell the clerk the password is not to be sent by email or messaging. Accountability is the whole point of a login.
Backup second. Before any editing, copy the file to an external drive and to an official cloud or block-level repository, and note which version is being sent. One copy on one desktop is one power cut from total loss.
Merged cells third. Unmerge the ward column, fill the ward name down every row, and only then sort. Sorting with merged cells moves blocks of rows as a unit and mixes beneficiaries across wards. After unmerging, select the range, sort by ward, and check the row count is still 6,240. Send the block a covering note stating the version and the 41 flagged entries.