Mastering Excel functions is the key to becoming an efficient MIS analyst, data professional, or even a power user in any business role. From simple calculations like SUM() to advanced lookups like XLOOKUP() and FILTER(), Excel provides a wide range of tools to analyze, clean, and transform data.
This Excel Functions Cheat Sheet organizes functions into Basic, Intermediate, and Advanced categories—so whether you’re just starting out or preparing for an interview, you’ll find ready-to-use syntax, usage, and examples at your fingertips.
Basic Excel Functions Cheat Sheet
| Function | Syntax | Usage | Example |
|---|---|---|---|
| SUM | =SUM(number1, [number2], …) | Adds numbers, cells, or ranges | =SUM(A1:A10) → Adds values in A1 to A10 |
| AVERAGE | =AVERAGE(number1, [number2], …) | Returns the average of numbers | =AVERAGE(B1:B5) → Average of B1 to B5 |
| COUNT | =COUNT(value1, [value2], …) | Counts numeric values only | =COUNT(A1:A10) → Counts numeric cells |
| COUNTA | =COUNTA(value1, [value2], …) | Counts all non-empty cells (numbers + text) | =COUNTA(A1:A10) → Counts non-blank cells |
| MIN | =MIN(number1, [number2], …) | Returns the smallest number in a range | =MIN(C1:C10) → Smallest value in range |
| MAX | =MAX(number1, [number2], …) | Returns the largest number in a range | =MAX(C1:C10) → Largest value in range |
| LOWER | =LOWER(text) | Converts text to lowercase | =LOWER(“HELLO”) → hello |
| UPPER | =UPPER(text) | Converts text to uppercase | =UPPER(“hello”) → HELLO |
| PROPER | =PROPER(text) | Capitalizes first letter of each word | =PROPER(“excel tips”) → Excel Tips |
| TRIM | =TRIM(text) | Removes extra spaces from text | =TRIM(” Excel Tips “) → Excel Tips |
| SUBSTITUTE | =SUBSTITUTE(text, old, new, [instance]) | Replaces text within a string | =SUBSTITUTE(“2025/09/22″,”/”,”-“) → 2025-09-22 |
| TODAY | =TODAY() | Returns today’s date | =TODAY() → 22-09-2025 |
| NOW | =NOW() | Returns current date & time | =NOW() → 22-09-2025 18:25 |
Intermediate Excel Functions Cheat Sheet
| Function | Syntax | Usage | Example |
|---|---|---|---|
| IF | =IF(logical_test, value_if_true, value_if_false) | Returns one value if condition is TRUE, another if FALSE | =IF(A1>50,”Pass”,”Fail”) |
| AND | =AND(condition1, condition2, …) | Checks if all conditions are TRUE | =IF(AND(A1>40,B1>50),”Yes”,”No”) |
| OR | =OR(condition1, condition2, …) | Checks if any condition is TRUE | =IF(OR(A1=”HR”,A1=”IT”),”Valid”,”Invalid”) |
| IFERROR | =IFERROR(value, value_if_error) | Returns custom result if formula gives error | =IFERROR(1/0,”Error”) → Error |
| IFNA | =IFNA(value, value_if_na) | Handles #N/A errors | =IFNA(VLOOKUP(5,A1:B10,2,FALSE),”Not Found”) |
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) | Searches value in first column and returns result | =VLOOKUP(102,A2:C10,3,FALSE) |
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index, [range_lookup]) | Searches value in first row and returns result | =HLOOKUP(202,A1:Z5,3,FALSE) |
| INDIRECT | =INDIRECT(ref_text) | Returns reference from text string | =INDIRECT(“A”&5) → Value from A5 |
| OFFSET | =OFFSET(reference, rows, cols, [height], [width]) | Returns range offset from a cell | =SUM(OFFSET(A1,0,0,5,1)) → Sum of A1:A5 |
| ROUND | =ROUND(number, num_digits) | Rounds number to specific digits | =ROUND(12.345,2) → 12.35 |
| CONCATENATE | =CONCATENATE(text1, text2, text3,…) | Joins multiple text values | =CONCATENATE(“Smart”, ” “, “Tutorials”) |
| SUBTOTAL | =SUBTOTAL(function_num, ref1, [ref2], …) | Returns subtotal ignoring filters/hidden rows | =SUBTOTAL(9,A1:A10) → Sum of visible rows |
| AGGREGATE | =AGGREGATE(function_num, options, ref1, …) | More flexible version of SUBTOTAL | =AGGREGATE(9,5,A1:A10) → Sum ignoring errors |
Advanced Excel Functions Cheat Sheet
| Function | Syntax | Usage | Example |
|---|---|---|---|
| IFS | =IFS(condition1,result1, condition2,result2, …) | Multiple IFs without nesting | =IFS(A1<40,”Fail”,A1<60,”Average”,A1>=60,”Pass”) |
| SWITCH | =SWITCH(expression, value1,result1, …, [default]) | Matches value with cases | =SWITCH(A1,”HR”,1,”IT”,2,”FIN”,3,”Other”) |
| SUMIF | =SUMIF(range, criteria, [sum_range]) | Adds values meeting criteria | =SUMIF(A1:A10,”HR”,B1:B10) |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, …) | Adds values with multiple criteria | =SUMIFS(C1:C20,A1:A20,”HR”,B1:B20,”Jan”) |
| COUNTIF | =COUNTIF(range, criteria) | Counts cells meeting single condition | =COUNTIF(A1:A10,”IT”) |
| COUNTIFS | =COUNTIFS(range1, criteria1, range2, criteria2, …) | Counts cells with multiple conditions | =COUNTIFS(A1:A10,”HR”,B1:B10,”>50″) |
| AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) | Averages values meeting one condition | =AVERAGEIF(A1:A10,”>50″,B1:B10) |
| AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criteria1, …) | Averages with multiple conditions | =AVERAGEIFS(C1:C20,A1:A20,”HR”,B1:B20,”Jan”) |
| INDEX | =INDEX(array, row_num, [col_num]) | Returns value from table using row & column | =INDEX(B2:D10,3,2) |
| MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | Returns position of value in a range | =MATCH(50,A1:A10,0) |
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) | Modern replacement of VLOOKUP & HLOOKUP | =XLOOKUP(101,A2:A10,B2:B10,”Not Found”) |
| FILTER | =FILTER(array, include, [if_empty]) | Filters data dynamically based on conditions | =FILTER(A2:C100,B2:B100=”HR”) |
| TEXTBEFORE | =TEXTBEFORE(text, delimiter, [instance]) | Extracts text before a delimiter | =TEXTBEFORE(“Rajesh-Kumar”,”-“) → Rajesh |
| TEXTAFTER | =TEXTAFTER(text, delimiter, [instance]) | Extracts text after a delimiter | =TEXTAFTER(“Rajesh-Kumar”,”-“) → Kumar |
| TEXTSPLIT | =TEXTSPLIT(text, col_delimiter, [row_delimiter]) | Splits text into multiple cells | =TEXTSPLIT(“A,B,C”,”,”) |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, …) | Joins values with delimiter | =TEXTJOIN(” – “,TRUE,A1:A3) → A – B – C |
| CHOOSE | =CHOOSE(index_num, value1, [value2], …) | Returns value from list based on index | =CHOOSE(2,”HR”,”IT”,”FIN”) → IT |
Conclusion
Excel is more than just rows and columns—it’s a complete data analysis powerhouse. By practicing these functions, you’ll not only save time but also impress managers, colleagues, and interviewers with your problem-solving skills.
Use this cheat sheet as a quick reference guide whenever you’re stuck. And remember, the best way to learn Excel is by applying these formulas on real business problems like sales reports, employee tracking, or financial dashboards.
👉 Keep this page bookmarked—it will grow as we add more advanced Excel tips and tricks!