The Sorted Stock Register: Labels That No Longer Match Their Numbers
Harpreet runs a hardware shop in Nabha and keeps stock in one Excel sheet, 'Stock.xls'. Columns are A: Item, B: Rate, C: Qty, D: Amount, with =B2*C2 in D2 filled down to D50; row 51 holds =SUM(D2:D50). On 3 July he added 'Ceiling Fan 1200mm' (Rate 1200, Qty 2) by copying the fan row at row 30 and using Insert Copied Cells at row 5. To see costly items first he then clicked A2, selected only column A down to A51, and used Data > Sort > Largest to Smallest; when Excel asked whether to expand the selection, he chose 'Continue with the current selection'. He saved, printed on A4 portrait (the Amount column was cut off at the right edge) and left. At closing time, 'Ceiling Fan 1200mm' showed Rate 45 and Qty 2, while 'Bulb 9W' showed Rate 1200 and Qty 8. The sheet total read ₹18,640, but the cash box held only ₹16,240. He asks you to repair the sheet without retyping all 50 rows.
1. Explain what the two symptoms tell you about what Harpreet actually did, and give a recovery sequence ordered from cheapest to most expensive in effort - before anyone retypes a single row.
Read the worked answer
What the symptoms prove. Choosing Continue with the current selection sorted only column A. Columns B, C and D never moved, so every item name now sits beside a different item's rate, quantity and amount. The 1200 × 2 you see against 'Ceiling Fan' belongs to whatever item originally occupied that physical row.
What the symptoms do NOT prove. Sorting one column cannot change D51, because column D was never reordered. So the ₹2,400 gap has a separate cause - do not stop at the first plausible explanation.
Recovery, cheapest first.
- If the file is still open, press Ctrl+Z repeatedly until the sort is undone. Check pairing by eye before saving.
- File > Info > Version History, or yesterday's backup copy, or the auto-recovered file. This is free if it exists - always check before manual repair.
- Only if 1 and 2 fail: sort the whole range A2:D50 (all four columns) by Item alphabetically so labels and numbers at least line up by name, then verify each row against purchase invoices, the stock register and a physical count of the fastest-moving items.
- Retype only the rows that still fail reconciliation, not the whole sheet.
- Rebuild the total below the last data row as =SUM(D2:D50) and re-add it.
2. The sheet is over by exactly ₹2,400, which is the fan's value (1200 × 2). Identify the two plausible causes, show how you would decide between them in under five minutes, and explain how to rebuild a total that cannot silently break again.
Read the worked answer
Cause A - a duplicate line. The fan was inserted with Insert Copied Cells from row 30, so a second fan row carrying 1200 × 2 = ₹2,400 may still exist further down. A duplicate adds its amount to the SUM without looking wrong in any single cell.
Cause B - the total row was inside the sorted or inserted range, so the SUM formula has been dragged to a shifted range and no longer covers every data row, or covers one row twice.
Five-minute test.
- Select A2:D50 and use Data > Remove Duplicates on a copy - a preview count of removed rows tells you at once whether a duplicate exists.
- Click the total cell and read the formula bar: the referenced range must start at the first data row and end at the last. Press Ctrl+Shift+End to see where Excel thinks the data block ends.
- Compare the fan line against the physical count of fans on the shelf.
Rebuild so it cannot break. Convert the range to an Excel Table (Ctrl+T) and tick Total Row in Table Design - Excel then writes =SUBTOTAL(109,[Amount]), which updates on insert, delete and filter. Keep the total inside the Table, never in a free-floating row that can be dragged into a sort.
3. Give the print corrections for a clean A4 portrait page, and state one structural control you would add so the same class of error cannot recur. Explain why each item works.
Read the worked answer
Print corrections.
- Select A1:D51 > Page Layout > Print Area > Set Print Area, so the wide empty grid on the right is not treated as part of the sheet.
- Orientation: Portrait; Paper size: A4. The cut-off Amount column usually means the paper size was set to Letter while the printer holds A4, so the right margin is pushed off.
- Scaling: Fit Sheet on One Page (or Fit All Columns on One Page). Scaling only affects printing, never cell values.
- Page Layout > Print Titles > Rows to repeat at top: $1, so the header appears on any second page.
- View > Freeze Panes at A2 so the header stays visible while scrolling - this is what stops mislabelled cells being read as correct.
Structural control. Add Data > Data Validation on B and C: decimal/whole number, greater than or equal to 0, with an input message. Negative or text rates are then refused at entry rather than discovered at closing. Pair it with protection of the total cell (Review > Protect Sheet) and one habit: sort by selecting the entire table, and convert to a Table so a single-column sort is no longer offered.