Excel Cheat Sheet: shortcuts, formulas and menus on one page
Print this. It is the page people keep pinned next to the monitor.
Shortcuts
| Press | To |
|---|---|
| Ctrl + Arrow | Jump to the edge of data |
| Ctrl + Shift + Arrow | Select to the edge |
| Ctrl + Space / Shift + Space | Select column / row |
| F2 | Edit cell |
| F4 | Lock a reference with $ |
| Ctrl + D | Fill down |
| Ctrl + ; | Today’s date |
| Alt + = | AutoSum |
| Ctrl + 1 | Format Cells |
| Ctrl + Shift + L | Filter on/off |
| Ctrl + T | Make a Table |
| Ctrl + H | Find and Replace |
| Ctrl + Z / Ctrl + Y | Undo / Redo |
| Ctrl + Page Down | Next sheet |
| Ctrl + S | Save |
All 25: keyboard shortcuts.
Formulas
| Formula | Does |
|---|---|
=SUM(A:A) |
Add |
=AVERAGE(A:A) |
Mean |
=COUNTIF(A:A,"x") |
Count matches |
=SUMIF(A:A,"x",B:B) |
Add matches |
=IF(A1>0,"Yes","No") |
Decide |
=XLOOKUP(D1,A:A,B:B) |
Look up |
=IFERROR(x,"") |
Hide errors |
=A1&" "&B1 |
Join text |
=TRIM(A1) |
Clean spaces |
=TODAY() |
Today |
=B1-A1 |
Days between |
=ROUND(A1,2) |
Round |
All 20 with examples: formulas cheat sheet.
Menu paths
| To | Go to |
|---|---|
| Freeze headings | View › Freeze Panes |
| Drop-down list | Data › Data Validation › List |
| Remove duplicates | Data › Remove Duplicates |
| Split a column | Data › Text to Columns |
| Colour by rule | Home › Conditional Formatting |
| Pivot table | Insert › PivotTable |
| Protect cells | Review › Protect Sheet |
| Print one page | Ctrl + P › Fit Sheet on One Page |
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
