Below formulas are the backbone of Excel for Data Analysts. If you master them + PivotTables, you’ll handle 90% of real-world tasks.
| Category | Formula (Syntax) | Example | Result | What it does |
|---|---|---|---|---|
| Aggregation | =SUM(range) | =SUM(A1:A5) where A1:A5 → 2,3,5,10,20 | 40 | Adds numbers in a range |
| Aggregation | =AVERAGE(range) | =AVERAGE(10,20,30,40,50) | 30 | Mean of values |
| Aggregation | =MEDIAN(range) | =MEDIAN(10,20,30,40,50) | 30 | Middle value |
| Aggregation | =MIN(range) / =MAX(range) | =MIN(4,7,1) / =MAX(4,7,1) | 1 / 7 | Smallest / largest |
| Aggregation | =COUNT(range) | =COUNT(4,”x”,7,””,9) | 3 | Counts numeric cells |
| Aggregation | =COUNTA(range) | =COUNTA(4,”x”,7,””,9) | 4 | Counts non-empty cells |
| Aggregation | =COUNTBLANK(range) | =COUNTBLANK({“a”,””,”b”}) | 1 | Counts blanks |
| Aggregation | =ROUND(number,digits) | =ROUND(12.345,2) | 12.35 | Round to decimals (ROUNDUP/ROUNDDOWN as needed) |
| Aggregation | =SUMPRODUCT(array1,array2) | =SUMPRODUCT({2,3,5},{10,20,30}) | 220 | Weighted sums / conditional math |
| Aggregation | =SUBTOTAL(function_num,ref) | =SUBTOTAL(9,A1:A10) | (sum of visible) | Ignores filtered rows (9=sum, 1=avg, etc.) |
| Conditional | =SUMIF(range,criteria,[sum_range]) | =SUMIF(B2:B6,”>50″,C2:C6) | 1200 | Sum by one condition |
| Conditional | =SUMIFS(sum_range,crit_rng1,crit1,…) | =SUMIFS(C:C,A:A,”East”,B:B,”Q1″) | (sum) | Sum by multiple conditions |
| Conditional | =COUNTIF(range,criteria) | =COUNTIF(A:A,”>=60″) | (count) | Count by one condition |
| Conditional | =COUNTIFS(range1,crit1,…) | =COUNTIFS(A:A,”Done”,B:B,”<=31-Jan-2025″) | (count) | Count by multiple conditions |
| Conditional | =AVERAGEIF(range,criteria,[avg_range]) | =AVERAGEIF(B:B,”North”,C:C) | (avg) | Average by one condition |
| Conditional | =AVERAGEIFS(avg_range,crit_rng1,crit1,…) | =AVERAGEIFS(C:C,A:A,”Store A”,B:B,”>=01-Jul-2025″) | (avg) | Average by multiple conditions |
| Logic | =IF(test,true_value,false_value) | =IF(65>50,”Pass”,”Fail”) | Pass | Conditional output |
| Logic | =IFS(test1,result1,test2,result2,…) | =IFS(A2>=90,”A”,A2>=75,”B”,A2>=60,”C”,TRUE,”D”) | A/B/C/D | Multiple conditions without nesting |
| Logic | =AND(cond1,cond2) | =AND(A2>50,B2=”Yes”) | TRUE/FALSE | Both must be TRUE |
| Logic | =OR(cond1,cond2) | =OR(A2>50,B2=”Yes”) | TRUE/FALSE | Any TRUE returns TRUE |
| Logic | =NOT(logical) | =NOT(TRUE) | FALSE | Negates TRUE/FALSE |
| Error | =IFERROR(value,value_if_error) | =IFERROR(1/0,”NA”) | NA | Replace #DIV/0!, #N/A, etc. |
| Checks | =ISNUMBER(cell) / =ISTEXT(cell) / =ISBLANK(cell) | =ISNUMBER(A2) | TRUE/FALSE | Type checks for cleaning |
| Lookup | =VLOOKUP(lookup,table,col,FALSE) | =VLOOKUP(101,A2:D10,2,FALSE) | (e.g., “Rahul”) | Vertical lookup (legacy) |
| Lookup | =HLOOKUP(lookup,table,row,FALSE) | =HLOOKUP(“Q1”,A1:H4,3,FALSE) | (value) | Horizontal lookup (legacy) |
| Lookup | =INDEX(range,row,[col]) | =INDEX(C2:C6,3) | 3rd item in C2:C6 | Return value by position |
| Lookup | =MATCH(lookup,range,0) | =MATCH(“Banana”,B2:B6,0) | (position) | Position in a range |
| Lookup | INDEX+MATCH | =INDEX(C2:C6, MATCH(“Banana”,B2:B6,0)) | (price of Banana) | Flexible alternative to VLOOKUP |
| Lookup | =XLOOKUP(lookup,lookup_array,return_array) | =XLOOKUP(“ID103”,A2:A100,D2:D100) | (match) | Modern lookup (best practice) |
| Text | =TRIM(text) | =TRIM(” Hello World “) | Hello World | Remove extra spaces |
| Text | =CLEAN(text) | =CLEAN(A2) | (cleaned) | Remove non-printing chars |
| Text | =LEN(text) | =LEN(“Analytics”) | 9 | Character count |
| Text | =LEFT(text,n) / =RIGHT(text,n) | =LEFT(“Analytics”,4) / =RIGHT(“Analytics”,3) | Anal / ics | First / last N chars |
| Text | =MID(text,start,n) | =MID(“Analytics”,3,4) | alyt | N chars from middle |
| Text | =SEARCH(find,within) / =FIND(find,within) | =SEARCH(“lyt”,”Analytics”) | 4 | Position (SEARCH not case-sens., FIND is) |
| Text | =SUBSTITUTE(text,old,new,[instance]) | =SUBSTITUTE(“a,b,c”,”,”,”|”) | a|b|c | Replace text |
| Text | =VALUE(text) | =VALUE(“0042”) | 42 | Text → number |
| Text | =TEXT(value,”format”) | =TEXT(DATE(2025,8,22),”dd-mmm-yyyy”) | 22-Aug-2025 | Display with format |
| Text | =PROPER/UPPER/LOWER(text) | =PROPER(“data analyst”) | Data Analyst | Change case |
| Text | =CONCAT(a,b,…) / =TEXTJOIN(delim,ignore,range) | =TEXTJOIN(“, “,TRUE,A2:A5) | a, b, c, d | Join text values |
| Text | =REPT(text,n) | =REPT(“*”,5) | ***** | Repeat text |
| Date/Time | =TODAY() / =NOW() | =TODAY() | (today) | Current date / date+time |
| Date/Time | =DATE(y,m,d) / =TIME(h,m,s) | =DATE(2025,8,22) | 22-Aug-2025 | Build dates/times |
| Date/Time | =EDATE(start,months) | =EDATE(“22-Aug-2025”,1) | 22-Sep-2025 | Shift by months |
| Date/Time | =EOMONTH(start,months) | =EOMONTH(“15-Aug-2025”,0) | 31-Aug-2025 | End of month |
| Date/Time | =DATEDIF(start,end,”y”/”m”/”d”) | =DATEDIF(“01-Jan-2020″,”01-Jan-2025″,”y”) | 5 | Years/months/days difference |
| Date/Time | =NETWORKDAYS(start,end,[holidays]) | =NETWORKDAYS(“01-Aug-2025″,”31-Aug-2025”) | (workdays) | Workdays between dates |
| Date/Time | =WORKDAY(start,days,[holidays]) | =WORKDAY(“20-Aug-2025”,5) | 27-Aug-2025 | Date after N workdays |
| Date/Time | =YEAR(date) / =MONTH(date) / =DAY(date) | =YEAR(“22-Aug-2025”) | 2025 | Extract parts |
| Date/Time | =WEEKDAY(date,[type]) | =WEEKDAY(“22-Aug-2025”) | 6 | Day of week number |
| Dynamic | =UNIQUE(range) | =UNIQUE(A2:A20) | (distinct list) | Unique values (spills) |
| Dynamic | =SORT(range,[by_col],[order]) | =SORT(A2:C20,1,TRUE) | (sorted spill) | Dynamic sort |
| Dynamic | =SORTBY(range,by_array,[order]) | =SORTBY(A2:C20,C2:C20,-1) | (sorted by col C desc) | Sort by another array |
| Dynamic | =FILTER(range,include) | =FILTER(A2:C20,B2:B20=”East”) | (filtered rows) | Filter rows by condition |
| Dynamic | =SEQUENCE(rows,[cols],[start],[step]) | =SEQUENCE(5,1,1,1) | 1..5 | Generate sequences |
| Dynamic | =TAKE(range,rows) / =DROP(range,rows) | =TAKE(A2:C100,10) | (top 10 rows) | Trim tables |
| Dynamic | =LET(name,value,calc) | =LET(x,SUM(A2:A10), x/COUNT(A2:A10)) | (average) | Name sub-calcs in formula |
| Financial | =PMT(rate,nper,pv) | =PMT(10%/12,24,-50000) | -2307.38 | EMI/loan payment |
| Financial | =NPV(rate, v1, v2,…) | =NPV(12%,-100000,30000,40000,50000) | (value) | Net present value |
| Financial | =FV(rate,nper,pmt,[pv]) | =FV(8%/12,36,-5000,0) | (value) | Future value |
| Extra | =RAND() / =RANDBETWEEN(a,b) | =RANDBETWEEN(1,100) | (random int) | Create sample data |
| Extra | =ABS(number) | =ABS(-12.5) | 12.5 | Absolute value |
| Extra | =MOD(number,divisor) | =MOD(17,5) | 2 | Remainder (useful for cycles) |
| Extra | =CHAR(code) / =CODE(text) | =CHAR(10) | (line break) | Character ↔ code |