Formulas tab — Excel guide
Writing, naming, auditing, and recalculating formulas. Alt+M from the keyboard. The Auditing group alone justifies the tab, it's Excel's built-in debugger.
Function Library
Insert Function (the fx button) searches every function by description and walks you through the arguments. AutoSum repeats its Home-tab cameo. The category dropdowns. Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, are a browsable index when you know what you need but not what it's called.
Defined Names
Define Name turns $B$4 into TaxRate; Name Manager lists, edits, and deletes every name in the workbook; Create from Selection names whole rows or columns from their labels in one shot. Named ranges make formulas legible, =Revenue*TaxRate survives review; =$B$4*$C$9 gets you a meeting.
| Button | What it does |
|---|---|
| Define Name | Name the selected cell or range |
| Name Manager | The full list, edit, delete, spot broken names |
| Use in Formula ▾ | Drop an existing name into what you're typing |
Formula Auditing
Trace Precedents draws arrows FROM the cells that feed a formula; Trace Dependents shows who relies on this cell, run it before deleting anything. Show Formulas (Ctrl+`) reveals the whole sheet's working. Evaluate Formula steps through a nested formula piece by piece. Error Checking's dropdown tracks circular references; Watch Window pins far-away cells in a floating panel.
| Button | What it does |
|---|---|
| Trace Precedents / Dependents | Arrows: what feeds this / what depends on it |
| Show Formulas | Formulas in place of results, sheet-wide (Ctrl+`) |
| Evaluate Formula | Step through a formula one calculation at a time |
| Error Checking ▾ | Sheet-wide error scan · circular reference list |
| Watch Window | Pin any cell's live value while you work elsewhere |
Calculation
Calculation Options toggles Automatic vs Manual recalc. Manual is deliberate on huge models, and the classic mystery on inherited ones: if numbers won't update, check here first, then press F9 (recalculate everything) or Shift+F9 (this sheet only).
| Button | What it does |
|---|---|
| Calculation Options ▾ | Automatic / Manual, the 'stale numbers' switch |
| Calculate Now | F9, recalc the whole workbook |
| Calculate Sheet | Shift+F9, recalc just this sheet |
From the keyboard
Auditing sequences worth wiring into muscle memory.
| Keys | Action |
|---|---|
| Alt+M, P | Trace precedents |
| Alt+M, D | Trace dependents |
| Alt+M, A, A | Remove all arrows |
| Alt+M, H | Show formulas |