Excel Common Formulas & Shortcuts Cheat Sheet
Microsoft Excel is the world’s most widely used spreadsheet software. Mastering common formulas and shortcuts can greatly improve data processing efficiency.
Essential Functions
| Category | Function | Description | Example |
|---|---|---|---|
| Math | SUM(range) |
Sum of values | =SUM(A1:A10) |
| Statistics | AVERAGE(range) |
Average of values | =AVERAGE(B1:B10) |
| Logic | IF(cond, T, F) |
Conditional logic | =IF(C1>60, "Pass", "Fail") |
| Lookup | VLOOKUP(...) |
Vertical lookup | =VLOOKUP("ID01", A:B, 2, 0) |
| Text | CONCAT(t1, t2) |
Combine text strings | =CONCAT(D1, "@mail.com") |
| Count | COUNTIF(r, c) |
Count based on criteria | =COUNTIF(E:E, ">100") |
Keyboard Shortcuts
Win (Ctrl), Mac (Cmd)
| Operation | Windows | macOS |
|---|---|---|
| Save File | Ctrl + S |
Cmd + S |
| Select All | Ctrl + A |
Cmd + A |
| Undo | Ctrl + Z |
Cmd + Z |
| Insert Current Date | Ctrl + ; |
Ctrl + ; |
| Format Cells | Ctrl + 1 |
Cmd + 1 |
| AutoSum (Alt+=) | Alt + = |
Cmd + Shift + T |
| Fill Down | Ctrl + D |
Cmd + D |
| Fill Right | Ctrl + R |
Cmd + R |
Data Management
| Category | Description |
|---|---|
| Freeze Panes | View -> Freeze Panes (keep headers visible) |
| Conditional Formatting | Home -> Conditional Formatting (auto-color based on value) |
| Pivot Table | Insert -> Pivot Table (summarize large data) |
| Data Validation | Data -> Data Validation (limit input, e.g., dropdown) |
| Text to Columns | Data -> Text to Columns (split cell by delimiter) |
Cell Referencing
A1: Relative Reference. Row/Col changes when copied.$A$1: Absolute Reference. Row/Col stays fixed when copied.A$1/$A1: Mixed Reference. Only Row or Only Col is fixed.- Shortcut: Press F4 (Win) or Cmd+T (Mac) in formula to toggle modes.
Common Chart Types
- Bar Chart: Compare categorical data.
- Line Chart: Show trends over time.
- Pie Chart: Show proportional relationships.
- Scatter Plot: Show correlation between variables.