Top 10 Excel formulas every analyst uses.
One page. Plus 20 shortcuts on the next one.
If you know these cold, most Excel test rounds feel easy.
Save it. Screenshot it. Keep it next to your laptop.
Top 10 formulas
Excel cheat sheet
| Formula | Example |
|---|---|
| XLOOKUP | =XLOOKUP(A2, IDs, Names, "Not found") |
| SUMIFS | =SUMIFS(Sales, Region, "North", Month, ">=4") |
| COUNTIFS | =COUNTIFS(Status, "Closed", Owner, A2) |
| IFERROR | =IFERROR(A2/B2, 0) |
| UNIQUE | =UNIQUE(Customers) |
| FILTER | =FILTER(Data, Region="North") |
| TEXTAFTER | =TEXTAFTER(A2, "@") |
| INDEX + MATCH | =INDEX(Price, MATCH(A2, Item, 0)) |
| SORTBY | =SORTBY(Names, Sales, -1) |
| LET | =LET(t, SUM(B:B), B2/t) |
Excel shortcuts you will use daily
| # | How do I... | Shortcut (Windows) |
|---|---|---|
| 1 | Autosum a column | Alt + = |
| 2 | Insert today's date | Ctrl + ; |
| 3 | Insert current time | Ctrl + Shift + ; |
| 4 | Select entire column | Ctrl + Space |
| 5 | Select entire row | Shift + Space |
| 6 | Insert row or column | Ctrl + Shift + + |
| 7 | Delete row or column | Ctrl + - |
| 8 | Open Format Cells | Ctrl + 1 |
| 9 | Convert range to a table | Ctrl + T |
| 10 | Turn filters on/off | Ctrl + Shift + L |
| 11 | Jump to the edge of data | Ctrl + Arrow |
| 12 | Fill down | Ctrl + D |
| 13 | Flash Fill | Ctrl + E |
| 14 | Lock a reference ($) | F4 |
| 15 | Edit the active cell | F2 |
| 16 | New line inside a cell | Alt + Enter |
| 17 | Show all formulas | Ctrl + ` |
| 18 | Instant chart | Alt + F1 |
| 19 | Paste Special | Ctrl + Alt + V |
| 20 | Go To | Ctrl + G |
Free PDF
Top 10 Excel formulas every analyst uses
Download the PDFOpens in Google Drive. No sign-up.