◆ Formulas I · drill · Spreadsheet Formulas & Data track
Pin the reference
F4 cycles the anchors — $A$1 locks both, A$1 the row, $A1 the column; one formula fills the grid.
The pricing desk wants the tier sheet quoted at all three price points before it goes to the committee. Volumes run down the side, the proposed prices run across the top, and every cell in the grid is that tier’s volume at that price. One formula does the whole block if its two references are pinned the right way round — the volume must always come from the volume column, the price always from the price row. Dollar the finished grid, box it, and save. The grid lands somewhere new every run.
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+).
F4⌘T · fn F4 on macCycle absolute / relative refs ($) while editing
Cycles $A$1 → A$1 → $A1 → A1 while editing — pin the row, the column, or both before filling across a grid.
Alt h a n⌥ h a n on macAlign middle (vertical)
Middle-align vertically when rows get tall — wrapped headers stay centered.
Alt h b s⌥ h b s on macOutside borders
OUTSIDE border (S for outSide) — ONE frame around the selection’s perimeter, interior clean. The finished-table look; A is the grid of lines, S is the frame.
The optimal line
the par-setting sequence, straight through:
C4 · =B4 F4×3 → $B4 · * · C3 F4×2 → C$3 · ↵ · C4:C6 ctrl+d · C4:E6 ctrl+r · alt h a n · alt h b s · ctrl+s
More Formulas I drills