◆ Formulas II · drill · Spreadsheet Formulas & Data track
Error triage — #REF! #DIV/0! #VALUE!
an error names its own disease — #REF! lost its range, #DIV/0! lost its base, #VALUE! lost an argument.
A segment tab came across mid-quarter with three errors on it, and more red cells than that — the Total costs row lost the rows its SUM covered, and the EBIT line under it is reading the wreckage. One quarter’s share of revenue divides by the blank row under the total instead of the total. And the opex plan is switched to the third case while the formula only carries two. Read each error, work out what it was meant to say, and rebuild it.
The shortcuts in this drill
Alt walks: tap Alt, release, then the letters in sequence — each key steps one ribbon menu. On a Mac, tap ⌥ and use the same letters (KeyTips, Excel 2024+).
Ctrl+C⌘C on macCopy
Copy starts almost every hand-off — grab a finished block once, then place it in the summary, the deck, or next quarter’s column.
Ctrl+V⌘V on macPaste
Plain paste brings everything: values, formulas, formats. Reach for Paste Special when you only want part of it.
Ctrl+B⌘B on macBold
Bold marks structure: titles, total rows, anything the reviewer’s eye should land on first.
Alt h b p⌥ h b p on macTop border
Top border above a total row — the accounting ruling that says “this line sums the ones above.”
functions you'll type: CHOOSE()
The optimal line
the par-setting sequence, straight through:
the Total costs row → a live SUM over the three cost lines (carry the Total revenue row down, or fill one across) · ctrl+b · alt h b p · the Q1 share → repoint the denominator at Total revenue · the case read → add the third case · ctrl+s
More Formulas II drills