19. Keyboard Shortcuts and Function Cheat Sheet
19.2 Function Cheat Sheet
| Category | Function | Syntax (short) | Example on our data | Version |
|---|---|---|---|---|
| Math | SUM / AVERAGE / MIN / MAX | =SUM(range) |
=SUM(G2:G11) → 2,870 |
All |
| Math | ROUND / ROUNDUP / ROUNDDOWN | =ROUND(n, digits) |
=ROUND(11.875,1) → 11.9 |
All |
| Math | SUMPRODUCT | =SUMPRODUCT(arr1, arr2) |
weighted avg mins → 11.88 | All |
| Math | SUBTOTAL / AGGREGATE | =SUBTOTAL(109, range) |
sum of visible rows | All / 2010+ |
| Conditional | SUMIF / SUMIFS | =SUMIFS(sum, rng1, crit1, …) |
Pune Delivered → 324 | All |
| Conditional | COUNTIF / COUNTIFS | =COUNTIFS(rng1, crit1, …) |
Delivered orders → 7 | All |
| Conditional | AVERAGEIFS | =AVERAGEIFS(avg, rng1, crit1) |
avg Pune amount | All |
| Conditional | MINIFS / MAXIFS | =MAXIFS(max, rng, crit) |
biggest Nashik order | 2019+ |
| Logical | IF / AND / OR / NOT | =IF(test, yes, no) |
=IF(G2>=500,"High","") |
All |
| Logical | IFS / SWITCH | =IFS(t1, v1, t2, v2, TRUE, v3) |
High/Medium/Low | 2019+ |
| Logical | IFERROR / IFNA | =IFERROR(x, alt) |
=IFERROR(G2/H2,0) |
All |
| Lookup | VLOOKUP / HLOOKUP | =VLOOKUP(val, table, col, FALSE) |
manager for store | All |
| Lookup | INDEX + MATCH | =INDEX(ret, MATCH(val, look, 0)) |
left lookup | All |
| Lookup | XLOOKUP / XMATCH | =XLOOKUP(val, look, ret, "Not found") |
store platform | M365 / 2021+ |
| Lookup | CHOOSE / OFFSET / INDIRECT | =INDIRECT("Pune!G2") |
sheet by name (volatile) | All |
| Text | LEFT / RIGHT / MID / LEN | =LEFT(text, n) |
=LEFT(A2,3) → BLK |
All |
| Text | FIND / SEARCH / SUBSTITUTE | =SUBSTITUTE(text, old, new) |
remove "Rs." | All |
| Text | TRIM / CLEAN / PROPER / UPPER / LOWER | =PROPER(TRIM(x)) |
" pune " → Pune | All |
| Text | TEXT / VALUE | =TEXT(date, "dd-mm-yyyy") |
14-03-2026 | All |
| Text | CONCAT / TEXTJOIN | =TEXTJOIN(", ", TRUE, range) |
list of areas | 2019+ |
| Text | TEXTBEFORE / TEXTAFTER / TEXTSPLIT | =TEXTAFTER(email, "@") |
example.com | M365 / 2024 |
| Date | TODAY / NOW | =TODAY() |
current date (volatile) | All |
| Date | DATE / YEAR / MONTH / DAY | =DATE(2026,3,14) |
14-03-2026 | All |
| Date | EOMONTH / EDATE | =EOMONTH(date, 0) |
31-03-2026 | All |
| Date | WEEKDAY / NETWORKDAYS / DATEDIF | =WEEKDAY(date, 2) |
6 = Saturday | All |
| Dynamic array | UNIQUE / SORT / SORTBY / FILTER | =FILTER(tbl, cond, "None") |
Solapur orders | M365 / 2021+ |
| Dynamic array | SEQUENCE / LET | =LET(x, expr, calc) |
31 dates of October | M365 / 2021+ |
| Dynamic array | TAKE / DROP / CHOOSECOLS / VSTACK / HSTACK | =TAKE(array, 5) |
top 5 rows | M365 / 2024 |
| Dynamic array | LAMBDA | =LAMBDA(x, calc)(value) |
named DELIVERYFEE | M365 / 2024 |
| Statistics | MEDIAN / STDEV.S / QUARTILE.INC / PERCENTILE.INC | =QUARTILE.INC(range, 1) |
IQR outliers | All |
| Statistics | RANK.EQ / LARGE / SMALL | =LARGE(range, 1) |
largest order | All |
| Pivot | GETPIVOTDATA | =GETPIVOTDATA("Sum of Amount", A3, "City", "Pune") |
KPI card | All |
Version key: All = Excel 2016 and later (most also older); 2019+ = Excel 2019, 2021, 2024 and Microsoft 365; M365 / 2021+ = Microsoft 365 and Excel 2021 or later; M365 / 2024 = Microsoft 365 and Excel 2024.
Ravindra Bagale's Tip
Khup students office madhe XLOOKUP, FILTER sarkhe navin functions vapartat aani file juni Excel (2016/2019) vaparnaryala pathavtat – tithe #NAME? yeto. File share karnyapurvi samorchyakade konta version aahe te vichara, aani garaj asel tar INDEX-MATCH, SUMIFS sarkhe saglya versions madhe chalnare functions vapra.
Thodkyaat sangaycha tar (quick recap)
- Roj 3 navin shortcuts; mouse kami, keyboard jasta.
- Cheat sheet madhe version column nakki bagha – M365 functions juni Excel madhe chalat nahit.
Aata pudhe jaauya – glossary.