Spreadsheet Discrepancy and a Suspicious Email
A Patwari maintains a spreadsheet of 500 mutation cases. Column A is the case number, B is the applicant name, C is area in hectares, D is land type, and E is revenue due. The formula in E2 is =C2*1800 and is filled down to E500. The Patwari notices that the total in E501, =SUM(E2:E500), is ₹40,000 less than the manual total. He also finds that case numbers 101 to 110 appear twice. He receives an email from an address claiming to be the tehsil IT cell, asking him to enable macros in an attached 'update.xlsm' to fix the totals. The office has a shared folder on the local network, but no off-site backup has been made for 45 days. The computer's antivirus is two years old.
1. Diagnose the most likely cause of the ₹40,000 discrepancy and state two checks the Patwari should perform.
Read the worked answer
A shortfall in the spreadsheet total compared with the manual total suggests missing or ignored values, not duplicate rows. Duplicates would normally increase a sum. The most likely causes are that the formula was not filled to the last data row, that some cells in E contain text or blanks, or that the SUM range does not cover all populated rows. The duplicate case numbers are a separate data-quality problem. Two checks: first, compare the last row containing data with the last row containing a formula, using Ctrl+End or COUNTA; second, test the E column for numbers, not text, using ISNUMBER or a filter, and inspect rows where E is blank. A third useful check is to compare E501 with a pivot-table total by land type.
2. How should the Patwari deal with the duplicate case numbers 101 to 110?
Read the worked answer
He should not delete either copy immediately, because one may be the genuine record and the other a data-entry duplicate. He should use conditional formatting or COUNTIF to list all duplicates, cross-check each pair against the physical mutation register, and record which entry is correct and why. Corrections should be logged with date and initials. If the duplicate has already affected the revenue total, he should recompute the total after correction and note the revised figure. The aim is an auditable correction, not a silent deletion.
3. Evaluate the email request and the backup situation, and recommend immediate action.
Read the worked answer
The email is a classic social-engineering risk: an unexpected attachment asking for macros to be enabled can run malicious code. The Patwari should not open or enable the attachment. He should verify the request through an official phone number or the office IT contact, report the email, and keep the original message as evidence. Separately, 45 days without an off-site backup is a serious risk for land records. He should back up the file to the approved external drive or departmental cloud location, confirm that the backup opens correctly, and ask IT to update the antivirus. Macro security should remain enabled, and any genuine update should be installed by authorised staff through a known channel.