The clerk, the fill handle and the sort that scrambled 2,000 rows
A clerk maintains a workbook for 2,000 savings accounts in rows 2 to 2001. Column A holds account numbers, B names, C balances and D branch names; the annual interest rate is typed once in cell H1. She enters =C2*H1 in cell E2 and drags the fill handle down to E2001. Rows 3 to 2001 all show 0. Later she needs a headcount of accounts at the Pune branch with balances above ₹50,000. In a hurry she clicks the column-C heading - which selects the whole of column C - and presses Sort A to Z; the sheet now shows account numbers sitting next to the wrong names and balances. She has no backup and wants to know what went wrong and how to finish the remaining work correctly.
1. Explain why rows 3 to 2001 show zero, and state the single formula that should have been entered in E2 and copied down.
Read the worked answer
The reference to H1 was relative. When =C2H1 was filled down, Excel adjusted both references row by row: E3 became =C3H2, E4 became =C4*H3, and so on. Cells H2, H3, H4 are empty, and an empty cell multiplies as zero - hence a column of zeros while E2 alone was correct.
The rate lives in one fixed cell, so its row and column must both be locked:
=C2$H$1*
Entered in E2 and filled down, the C2 keeps shifting to C3, C4 … while $H$1 stays pinned. (If the rate cell were in the same row and only the column needed fixing, $H1 would do; here both parts move, so both need the dollar sign.) A quick diagnostic habit: click any zero cell and read the formula bar - if it points at an empty cell, the reference type is the fault, not the data.
2. Give the formula she needs to count Pune-branch accounts with balances above ₹50,000, and say why a single-condition COUNTIF will not do.
Read the worked answer
=COUNTIFS(D2:D2001,"Pune",C2:C2001,">50000")
COUNTIFS applies all criteria to the same row simultaneously, so it counts only rows where column D equals Pune and column C exceeds 50,000.
COUNTIF cannot do it: with one range and one criterion it can test either the branch or the balance, not both. Using two separate COUNTIFs and subtracting would only work if the two conditions were mutually exclusive, which they are not - the Pune-with-high-balance accounts sit inside both totals. Two checks worth adding before trusting the number: the ranges must be the same size and start on the same row (both D2:D2001 and C2:C2001 do), and the comparison must be quoted as text ">50000", otherwise Excel treats it as a plain number and errors.
3. Explain how the sort destroyed the data, how it should have been done, and what realistic recovery steps remain with no backup.
Read the worked answer
What happened: she selected only column C, so Sort rearranged column C alone and left A, B and D untouched. Each balance now sits on a different row from the name and account number it belongs to. Sorting is a row operation; applied to a partial selection it silently destroys row integrity.
How it should have been done: click any single cell inside the block, then Data → Sort, and accept 'Expand the selection' (or select the whole range A1:D2001 first, headers included, and tick 'My data has headers'). Sorting then moves entire rows together. Never pre-select a single column and sort.
Recovery, in order of likelihood:
- Press Ctrl+Z at once - if nothing has been saved since, the sort is undone in one step.
- Check the file's version history or any auto-recovery copy before closing the workbook.
- If the file was saved and there is no history, the original row pairing is gone and cannot be reconstructed from the sheet. Rebuild by matching column A against the core banking extract or the printed ledger, then re-import rather than retype.
Once rebuilt, screen for duplicate account numbers before sorting again, e.g. =COUNTIF($A$2:$A$2001,A2)>1 copied down, and take a dated copy of the sheet before any further bulk operation.