The variance column that won't go red
Priya maintains a 240-row regional expense sheet. Column F, Variance, is Actual (D) minus Budget (E). Policy requires negatives in red inside parentheses and zeros shown as an en dash. She applied the custom format #,##0;Red;"–" to F2:F240, but about 30 cells still align left and show no parentheses. Row 1 holds a title merged across A1:F1; when she tries Data > Sort by Region, Excel refuses with 'This operation requires the merged cells to be identical in size.' A conditional format meant to shade whole rows where F is negative was applied only to A2:A240 with formula =$F2<0, so only column A turns pink.
1. Diagnose the two display faults (missing parentheses, shading only in column A) and give exact steps to fix each.
Read the worked answer
Fault 1 - text, not numbers. A numeric custom format does not right-align text, so left alignment is the tell. The ~30 cells were almost certainly entered as text (leading apostrophe, or a column pre-formatted as Text). Confirm with =ISNUMBER(F2) or compare =COUNT(F2:F240) against =COUNTA(F2:F240).
Fix in place: select F2:F240 > Data > Text to Columns > Finish, or type 1 in a blank cell, copy it, then Paste Special > Multiply on the range. Then re-apply #,##0;[Red](#,##0);"–" (section 1 positives, section 2 negatives red in parentheses, section 3 zero as a literal en dash, hence the quotes).
Fault 2 - range/formula mismatch. =$F2<0 is absolute in column F and relative in row. The rule must be applied to a block that starts on the same row as the formula's anchor row. Select A2:F240, then enter =$F2<0. Applied only to A2:A240 it can only ever test column A.
Verify with −500 in F5: expect red parentheses and a pink row spanning A to F.
2. Explain why the sort fails and propose the least disruptive fix that keeps the title looking centred across A1:F1.
Read the worked answer
Excel cannot sort a range containing a merge that spans several columns, because the merged block would have to move as a unit and the rows inside it cannot be reordered. If the sort range is A1:F240 and A1:F1 is merged, the merge blocks the operation.
Least disruptive fix that preserves the look: unmerge A1:F1, then Format Cells > Alignment > Horizontal > Center Across Selection. The title displays across all six columns, but no merge exists, so sorting, filtering, fill-down and row insertion all work.
Alternatives, in order of preference:
- Keep the merge but restrict the sort to A2:F240 (title stays out of the sort).
- Move the title to a genuine header row above the data and never include it in the range.
Whatever you choose, confirm the header row is excluded and that no two column labels are identical, or Excel will sort on the wrong key.
3. A colleague argues that typing −1200 into a cell formatted with that custom code is 'the same as' storing text(-1200). Is it? Give one test and one consequence of confusing them.
Read the worked answer
No. A custom format changes only what is displayed; the stored value remains the number −1200 and behaves as a number. Text (leading apostrophe, or a Text-formatted cell) stores characters, is ignored by SUM/AVERAGE, and sorts after all numbers.
Test: =ISNUMBER(F2) returns TRUE for the typed number and FALSE for the text entry. =SUM(F2:F240) will silently omit the text cells - the visible column can look correct while the total is short.
Consequence worth flagging: comparisons treat text as greater than any number, so =$F2<0 does not fire on text('−1200'). The red formatting and the pink row both disappear, and a reader concludes the variance is non-negative. Always check the status bar count against COUNTA after tidying a numeric column.