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 |
About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Aamir Khan) are made up for this lesson on function cheat sheet.
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.
Loading practice tasks...
- AutoSum (Alt + =) (Easy): Add up the Amount column.
- Count the rows (Ctrl + Down) (Easy): How many rows are in the Rep column?
- Total units (Easy): Add up the Units column.
- Largest sale (Easy): What is the largest value in the Amount column?
- Filter one rep (Ctrl + Shift + L) (Medium): How many rows have Rep "Kabir Joshi"?
- Amount for one rep (Medium): Add up Amount where Rep is "Kabir Joshi".
- Amount for one city (Medium): Add up Amount where City is "Berlin".
- Average units (status bar) (Medium): Find the average of the Units column, rounded to 1 decimal.
- Locked share (F4) (Medium): What share of the total Amount is the first row? Round to 3 decimals.
- Big sales by one rep (Hard): How many rows have Rep "Kabir Joshi" and Amount of 1000 or more?
- Who made the top sale? (Hard): Which Rep has the largest Amount?
- Price per unit (Hard): For the first row, divide Amount by Units. Round to 0 decimals.
Worked example — function cheat sheet
Why
Aarti Kulkarni in Pune and Sophie Martin abroad both need a clear, repeatable way to use function cheat sheet on a FreshGo Mart sheet without breaking totals.
What
Function Cheat Sheet is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for function cheat sheet.
- Enter a small made-up value for Aarti Kulkarni using $ where money appears.
- Add a second value for Sophie Martin using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once function cheat sheet is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
