Excel Formulas Cheat Sheet
Quick reference for the formulas you reach for most — criteria sums, lookups, logic, and dynamic arrays. Pair with the full Excel shortcuts cheat sheet.
Keyboard shortcuts for AutoSum, absolute refs, and Show Formulas live on the main sheet.
Open shortcuts sheet →
Criteria & aggregation
| Formula | What it does | Example |
|---|
| SUMIF | Sum where criteria match | =SUMIF(A:A,"East",B:B) |
| SUMIFS | Sum with multiple criteria | =SUMIFS(C:C,A:A,"East",B:B,">100") |
| COUNTIF | Count where criteria match | =COUNTIF(A:A,">100") |
| COUNTIFS | Count with multiple criteria | =COUNTIFS(A:A,"East",B:B,">0") |
| AVERAGEIF | Average where criteria match | =AVERAGEIF(A:A,"Yes",B:B) |
Lookups
| Formula | What it does | Example |
|---|
| XLOOKUP | Modern lookup (any direction) | =XLOOKUP(E2,A:A,C:C,"N/A") |
| VLOOKUP | Lookup in first column (legacy) | =VLOOKUP(E2,A:C,3,FALSE) |
| INDEX/MATCH | Flexible two-way lookup | =INDEX(C:C,MATCH(E2,A:A,0)) |
Logic & text
| Formula | What it does | Example |
|---|
| IF | Conditional logic | =IF(B2>=60,"Pass","Fail") |
| IFS | Multiple conditions | =IFS(B2>=90,"A",B2>=80,"B",TRUE,"C") |
| TEXT | Format number as text | =TEXT(A2,"YYYY-MM-DD") |
| CONCAT | Join text | =CONCAT(A2," ",B2) |
| TEXTJOIN | Join with delimiter | =TEXTJOIN(", ",TRUE,A2:A10) |
Dynamic arrays (Microsoft 365 / 2021)
| Formula | What it does | Example |
|---|
| FILTER | Dynamic filtered array | =FILTER(A2:C100,B2:B100>50) |
| UNIQUE | Distinct values | =UNIQUE(A2:A100) |
Formula keyboard shortcuts
- Windows: AutoSum Alt+= · Absolute refs F4 · Show formulas Ctrl+`
- Mac: AutoSum ⌘+Shift+T · Absolute refs ⌘+T
Also see Excel pivot & chart shortcuts, Windows-only sheet, and Mac-only sheet.