Cleaning a School Shop Sales Sheet Before the Monthly Report
The student-run stationery shop at a government high school keeps its sales record in a spreadsheet. The sheet has 180 rows and columns: Receipt No., Date, Item, Quantity, Rate, Amount, Payment Mode, and Buyer Mobile. Before submitting the monthly report, a volunteer finds four problems. Some Rate cells contain text such as Rs 25 and 25/-, so Amount was typed manually and is blank in 14 rows. Three receipt numbers appear twice because the same sale was entered by two volunteers. Buyer Mobile numbers beginning with 0 have lost the leading zero. Payment Mode is entered as UPI, upi, and U.P.I. The shop needs the total sales for the month, the number of UPI transactions, and a clean list for the teacher. The volunteer suggests merging all cells item-wise and colouring duplicates, then adding the amounts with a calculator. The teacher wants a repeatable spreadsheet method and an audit trail. The report is due tomorrow, and the original paper bills are available in a bundle.
1. State the first steps you would take to clean the sheet, in order, and justify why each must come before calculating totals.
Read the worked answer
- Preserve and copy the file first. Save the original as read-only and work on a copy, because the paper bills are the only evidence and a cleanup mistake may not be reversible.
- Standardise Payment Mode before counting. Replace 'upi' and 'U.P.I.' with 'UPI' using Find & Replace or a helper column. COUNTIF treats 'UPI' and 'upi' as the same in many spreadsheets, but 'U.P.I.' is a different string, so the UPI count would be too low if punctuation is not fixed.
- Convert Rate text to numbers. Use Find & Replace to remove 'Rs', '/-', and spaces, then set the column to Number/Currency. Check at least five rows against paper bills. If totals are calculated before this, text rates are ignored or cause errors.
- Handle duplicates before deleting. Use a helper column to flag repeated Receipt No. values, then match each flagged row to the paper bill. Only remove a row when the bill shows it is a duplicate entry, not a genuine repeat sale. Deleting first would destroy the audit trail.
- Set Buyer Mobile as Text before re-entering leading zeros, because a number format cannot restore a zero that was already dropped. Doing this late would mean rework. The order matters: evidence, standardisation, numeric conversion, duplicate verification, then totals.
2. Write the formulas or functions you would use to get total sales and the UPI transaction count, naming assumed ranges. Explain why manual Amount typing should be replaced.
Read the worked answer
Assume cleaned data in rows 2–181: Quantity in D, Rate in E, Amount in F, Payment Mode in G. In F2 put =IF(OR(D2='',E2=''),'',D2*E2) and fill down. This makes Amount a calculated field, so a changed quantity or rate updates automatically and blank inputs remain visible instead of becoming zero. Total sales: =SUM(F2:F181). UPI count after standardisation: =COUNTIF(G2:G181,'UPI'). If punctuation is not yet cleaned, use =COUNTIF(G2:G181,'UPI')+COUNTIF(G2:G181,'U.P.I.'), but that is a temporary fix. Manual typing should be replaced because 14 blanks already prove it is unreliable, it cannot be audited, and it hides whether an amount is missing or genuinely zero. Add a check column =IF(F2='','CHECK','OK') and filter for CHECK before submitting.
3. The volunteer wants to merge cells for each item and colour duplicates. Give two risks and a better way to make duplicates visible.
Read the worked answer
Risk 1: merging cells for each item destroys a flat data table. Sorting, filtering, pivot tables and formulas then read only the top-left value and may treat blank merged cells as empty. Risk 2: colouring duplicates is not data. Colours are lost on copy, are not counted by formulas, and may be applied inconsistently by another volunteer. A third risk is that merged cells make it hard to import the sheet into another system. Better method: keep one row per sale, add a helper column =COUNTIF($A$2:$A$181,A2)>1 to return TRUE for repeated receipt numbers, and use conditional formatting on that helper. Then filter TRUE and verify against paper bills. This gives a repeatable, visible audit trail without changing the table structure.