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
Many students use newer functions like XLOOKUP and FILTER at the office and send the file to someone using an older Excel (2016/2019) – where it shows #NAME?. Before sharing a file, ask which version the other person has, and if needed use functions that work in all versions, like INDEX-MATCH and SUMIFS.
Ravindra Bagale's Tip – मराठी
बरेच students office मध्ये XLOOKUP, FILTER सारखी नवीन functions वापरतात आणि file जुनं Excel (2016/2019) वापरणाऱ्याला पाठवतात – तिथे #NAME? येतो. File share करण्यापूर्वी समोरच्याकडे कोणतं version आहे ते विचारा, आणि गरज असेल तर INDEX-MATCH, SUMIFS सारखी सगळ्या versions मध्ये चालणारी functions वापरा.
Ravindra Bagale's Tip – हिंदी
बहुत से students office में XLOOKUP, FILTER जैसे नए functions इस्तेमाल करते हैं और file पुराना Excel (2016/2019) चलाने वाले को भेज देते हैं – वहाँ #NAME? आता है. File share करने से पहले पूछो कि सामने वाले के पास कौन सा version है, और ज़रूरत हो तो INDEX-MATCH, SUMIFS जैसे सब versions में चलने वाले functions इस्तेमाल करो.
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.