Excel Formula Cheat Sheet
Answers use the sample sheet (A1:D9) from the formulas topic.
| I want to... | Use | Example |
|---|---|---|
| Add numbers | SUM | =SUM(C2:C9) → 572 |
| Average | AVERAGE | =AVERAGE(C2:C9) → 71.5 |
| Highest / lowest | MAX / MIN | =MAX(C2:C9) → 92 |
| Count numbers | COUNT | =COUNT(C2:C9) → 8 |
| Count non-empty cells | COUNTA | =COUNTA(A2:A9) → 8 |
| Count empty cells | COUNTBLANK | =COUNTBLANK(A1:D9) → 0 |
| Count by condition | COUNTIF / COUNTIFS | =COUNTIF(B2:B9,"Jammu") → 3 |
| Add by condition | SUMIF / SUMIFS | =SUMIF(B2:B9,"Jammu",C2:C9) → 228 |
| Average by condition | AVERAGEIF | =AVERAGEIF(B2:B9,"Srinagar",C2:C9) → 83 |
| Decide between two results | IF | =IF(C2>=40,"Pass","Fail") |
| Many tests together | AND / OR | =AND(C2>=60,D2>=3000) |
| Hide an error | IFERROR | =IFERROR(A1/B1,0) |
| Find by key (exact) | VLOOKUP, XLOOKUP | =VLOOKUP("Eram",A2:D9,3,FALSE) → 88 |
| Find with a left lookup | INDEX + MATCH | =INDEX(A2:A9,MATCH(92,C2:C9,0)) → Charu |
| Position in a list | MATCH | =MATCH("Eram",A2:A9,0) → 5 |
| Rank | RANK.EQ | =RANK.EQ(C2,$C$2:$C$9) → 4 |
| Nth largest | LARGE | =LARGE(C2:C9,2) → 88 |
| Middle value | MEDIAN | =MEDIAN(C2:C9) → 74.5 |
| Round | ROUND | =ROUND(2.567,2) → 2.57 |
| Remainder | MOD | =MOD(17,5) → 2 |
| Weighted total | SUMPRODUCT | =SUMPRODUCT(C2:C9,D2:D9) → 1715000 |
| Join text | & or TEXTJOIN | =A2&" - "&B2 |
| Cut text | LEFT, RIGHT, MID | =MID("JKEdusphere",3,3) → Edu |
| Clean text | TRIM | =TRIM(" a b ") → a b |
| Change case | UPPER, LOWER, PROPER | =PROPER("jammu") → Jammu |
| Today's date | TODAY | =TODAY() |
| Age or gap in years | DATEDIF | =DATEDIF(B2,TODAY(),"Y") |
| Month end | EOMONTH | =EOMONTH(DATE(2026,2,10),0) → 28-Feb-2026 |
| Working days | NETWORKDAYS | =NETWORKDAYS(A1,B1) |
| Loan EMI | PMT | =PMT(9%/12,60,500000) → -10,379.18 |
| Future value | FV | =FV(8%,5,0,-100000) → 1,46,932.81 |
| Project worth | NPV, IRR | =NPV(10%,40000,50000,60000)-100000 → 22,764.84 |
| Filter rows (365) | FILTER | =FILTER(A2:C9,B2:B9="Jammu") |
| Distinct items (365) | UNIQUE | =UNIQUE(B2:B9) |
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.