Keyboard shortcuts worth burning in
| Shortcut | What it does |
|---|---|
Ctrl + ; |
Insert today’s date |
Ctrl + Shift + L |
Toggle filters |
Ctrl + Arrow |
Jump to edge of data region |
Ctrl + Shift + Arrow |
Select to edge of data region |
Alt + = |
AutoSum selection |
F4 |
Cycle absolute/relative references while editing |
Ctrl + PgUp/PgDn |
Previous / next worksheet |
Alt + Enter |
Line break within a cell |
Ctrl + T |
Convert range to a Table (do this constantly) |
F12 |
Save As |
Ctrl + ` |
Show all formulas |
Alt, H, O, I |
Auto-fit column width |
Ten functions that do the heavy lifting
- XLOOKUP — retire VLOOKUP:
=XLOOKUP(key, lookup_col, return_col, "not found"). No column counting, no silent wrong-column bugs, built-in miss handling. - SUMIFS / COUNTIFS — multi-condition totals:
=SUMIFS(amounts, dates, ">="&start, accounts, "4*"). - FILTER — pull every matching row, live:
=FILTER(tbl, (tbl[Status]="Open")*(tbl[Amount]>10000)). - UNIQUE + SORT — instant distinct lists:
=SORT(UNIQUE(tbl[Vendor]))— the backbone of a summary tab. - TEXTJOIN —
=TEXTJOIN(", ", TRUE, range)for readable exception lists. - IFERROR — wrap lookups, not logic: catch misses, never mask math errors.
- EOMONTH — period-end dates without drama:
=EOMONTH(TODAY(), -1)= last month-end. - LET — name your intermediate steps so the next reviewer can read the formula.
- SUMPRODUCT — the old-guard multi-condition workhorse; still unbeatable for weighted calcs.
- TEXTSPLIT / TEXTBEFORE / TEXTAFTER — parse imported strings without Text-to-Columns ceremonies.
Habits of reviewable workbooks
- Tables everywhere.
Ctrl+Tgives you structured references, auto-expanding formulas, and formats that survive inserts. - One direction of flow. Inputs → calculations → outputs, left to right, tab by tab. No formula reaches “backward.”
- No hardcoded numbers inside formulas. Every rate, threshold, and date lives in a labeled assumptions cell.
- Color code by convention. Blue = input, black = formula. Every reviewer instantly knows what they may touch.
- Trace before you trust.
Ctrl+[jumps to precedents; Formulas → Evaluate Formula steps through logic. - Power Query for anything you clean twice. If you fixed an export’s formatting last month and will fix it again next month, record it once in Power Query and refresh forever.
Signs you’re ready for the next level
You nest more than three IFs (learn IFS/SWITCH or LET) · you copy-paste the same cleanup weekly (Power Query) · your file exceeds 50 MB (data model / Power Pivot) · you type the same report monthly (dynamic arrays + one refresh).
Our Excel courses pick up exactly here — see the Excel CPE hub for live and self-study options.