Excel functions are powerful tools that save time, reduce mistakes, and help you analyze data faster.
Whether youโre new to Excel or want to refresh your skills, this guide explains the most useful Excel formulas with clear examples and simple explanations.
Mathematical & Statistical Functions
These functions help you calculate totals, averages, and other numeric results quickly.
| Function | Description | Example | Explanation |
|---|---|---|---|
| =SUM(range) | Adds all numbers in a range | =SUM(A1:A10) | Adds all numbers from A1 to A10 |
| =AVERAGE(range) | Calculates the average | =AVERAGE(B1:B10) | Returns the average of selected cells |
| =MIN(range) | Finds the smallest value | =MIN(C1:C10) | Returns the lowest number |
| =MAX(range) | Finds the largest value | =MAX(D1:D10) | Returns the highest number |
| =COUNT(range) | Counts numbers | =COUNT(A1:A10) | Counts only numeric cells |
| =COUNTA(range) | Counts non-empty cells | =COUNTA(A1:A10) | Counts all filled cells (including text) |
| =COUNTBLANK(range) | Counts blank cells | =COUNTBLANK(A1:A10) | Counts all empty cells |
| =ROUND(number, digits) | Rounds to set decimals | =ROUND(3.14159, 2) โ 3.14 | Rounds number to 2 decimals |
| =ROUNDUP(number, digits) | Always rounds up | =ROUNDUP(3.141, 1) โ 3.2 | Rounding up |
| =ROUNDDOWN(number, digits) | Always rounds down | =ROUNDDOWN(3.141, 1) โ 3.1 | Rounding down |
| =INT(number) | Converts to integer | =INT(5.9) โ 5 | Removes decimal part |
| =ABS(number) | Returns positive value | =ABS(-8) โ 8 | Removes negative sign |
| =MOD(number, divisor) | Returns remainder | =MOD(10,3) โ 1 | Remainder after division |
| =PRODUCT(range) | Multiplies values | =PRODUCT(A1:A3) | Multiplies all selected numbers |
| =POWER(number, power) | Raises to a power | =POWER(2,3) โ 8 | Exponentiation |
| =SQRT(number) | Square root | =SQRT(9) โ 3 | Returns square root |
| =RAND() | Random number (0โ1) | โ | Changes with every recalculation |
| =RANDBETWEEN(bottom, top) | Random whole number | =RANDBETWEEN(1,100) | Generates number between limits |
| =PI() | Returns ฯ (pi) | โ | Useful in geometry and trigonometry |
| =SIN(number) | Sine of an angle (radians) | =SIN(PI()/2) โ 1 | Calculates sine value |
| =COS(number) | Cosine of an angle | =COS(0) โ 1 | Calculates cosine value |
| =TAN(number) | Tangent of an angle | =TAN(PI()/4) โ 1 | Calculates tangent value |
| =ASIN(number) | Inverse sine | =ASIN(0.5) | Returns angle in radians |
| =ACOS(number) | Inverse cosine | =ACOS(0.5) | Returns angle in radians |
| =ATAN(number) | Inverse tangent | =ATAN(1) | Returns angle in radians |
| =DEGREES(radians) | Converts radians to degrees | =DEGREES(PI()) โ 180 | Converts radians to degrees |
| =RADIANS(degrees) | Converts degrees to radians | =RADIANS(180) โ 3.14159 | Converts degrees to radians |
| =LOG(number,[base]) | Logarithm | =LOG(100,10) โ 2 | Log to given base |
| =LN(number) | Natural logarithm | =LN(2.718) โ 1 | Natural log function |
| =EXP(number) | Exponential function | =EXP(1) โ 2.718 | e raised to the power of number |
Lookup & Reference Functions
Use these to find, search, or extract data from other cells or tables.
| Function | Description | Example | Explanation |
|---|---|---|---|
=VLOOKUP(value, table, col_index, [range_lookup]) | Vertical lookup | =VLOOKUP(101, A2:C10, 2, FALSE) | Finds a value in the first column |
=HLOOKUP(value, table, row_index, [range_lookup]) | Horizontal lookup | =HLOOKUP("Q1", A1:D4, 2, FALSE) | Searches across the top row |
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) | Modern lookup | =XLOOKUP("Apple", A2:A10, B2:B10, "Not found") | Easier alternative to VLOOKUP |
=INDEX(array, row_num, [column_num]) | Get value at position | =INDEX(A2:C5, 2, 3) | Returns a value from a specific cell |
=MATCH(lookup_value, lookup_array, [match_type]) | Finds the position of a value | =MATCH(50, A1:A10, 0) | Finds position of a value |
=OFFSET(reference, rows, cols, [height], [width]) | Shifted reference | =OFFSET(A1, 2, 1) | Moves 2 rows down, 1 column right |
=CHOOSE(index_num, value1, [value2], โฆ) | Choose from list | =CHOOSE(2, "Red", "Blue", "Green") โ Blue | Selects a value by index |
=TRANSPOSE(array) | Switch rows and columns | =TRANSPOSE(A1:B2) | Converts vertical data to horizontal |
Forecasting & Data Analysis Functions
These are helpful for predicting trends, analyzing data, and building reports.
| Function | Description | Example | Explanation |
|---|---|---|---|
=TREND(known_yโs, [known_xโs], [new_xโs]) | Linear trend | =TREND(B2:B10, A2:A10, A11:A15) | Predicts future values |
=FORECAST(x, known_yโs, known_xโs) | Linear forecast | =FORECAST(2025, B2:B10, A2:A10) | Predicts based on known data |
=GROWTH(known_yโs, [known_xโs], [new_xโs]) | Exponential growth | โ | Predicts exponential trends |
=LINEST(known_yโs, known_xโs) | Regression statistics | โ | Gives slope, intercept, etc. |
=CORREL(array1, array2) | Correlation | =CORREL(A1:A10, B1:B10) | Measures relationship strength |
=COVARIANCE.P(array1, array2) | Covariance | โ | Shows how two variables change together |
=STDEV.P(range) | Std deviation (population) | =STDEV.P(A1:A10) | Measures data variation |
=STDEV.S(range) | Std deviation (sample) | =STDEV.S(A1:A10) | For sample data |
=VAR.P(range) | Variance (population) | โ | Shows spread of full dataset |
=VAR.S(range) | Variance (sample) | โ | Spread of sample dataset |
Logical Functions
Use these to test conditions and make decisions in your formulas.
| Function | Description | Example | Explanation |
|---|---|---|---|
=IF(condition, true_value, false_value) | Returns value based on condition | =IF(A1>10, "Yes", "No") | Basic conditional check |
=IFS(condition1, result1, condition2, result2, โฆ) | Multiple IFs | =IFS(A1>90,"A",A1>80,"B") | Simplifies nested IFs |
=IFERROR(value, value_if_error) | Avoids formula errors | =IFERROR(VLOOKUP(...),"Not Found") | Handles #N/A or #DIV/0 errors |
=AND(condition1, condition2) | TRUE if all true | =AND(A1>10, B1<5) | Checks multiple conditions |
=OR(condition1, condition2) | TRUE if any true | =OR(A1>10, B1<5) | Either condition works |
=NOT(condition) | Reverses TRUE/FALSE | =NOT(A1>5) | Returns TRUE if A1 โค 5 |
Text Functions
These help clean, join, or extract text from cells.
| Function | Description | Example | Explanation |
|---|---|---|---|
=CONCAT(text1, text2,โฆ) | Joins text | =CONCAT(A1," ",B1) | Combines cell values |
=TEXTJOIN(delimiter, ignore_empty, text1,โฆ) | Joins with separator | =TEXTJOIN(", ", TRUE, A1:A3) | Joins text with commas |
=LEFT(text, num_chars) | Extracts from left | =LEFT(A1, 4) | Gets first 4 characters |
=RIGHT(text, num_chars) | Extracts from right | =RIGHT(A1, 3) | Gets last 3 characters |
=MID(text, start, num_chars) | Extracts middle text | =MID(A1, 3, 2) | Gets 2 characters from 3rd |
=LEN(text) | Counts characters | =LEN(A1) | Counts spaces too |
=TRIM(text) | Removes extra spaces | =TRIM(" Hello ") โ Hello | Cleans up imported data |
=UPPER(text) / =LOWER(text) / =PROPER(text) | Changes case | โ | Converts to upper, lower, or title case |
Date & Time Functions
Excel treats dates as numbers, so these functions help calculate or extract date parts.
| Function | Description | Example | Explanation |
|---|---|---|---|
=TODAY() | Current date | โ | Updates automatically |
=NOW() | Date & time | โ | Shows current timestamp |
=DAY(date) / =MONTH(date) / =YEAR(date) | Extract date parts | =MONTH("10/10/2025") โ 10 | Extracts parts of a date |
=DATEDIF(start_date, end_date, unit) | Calculates date difference | =DATEDIF(A1,B1,"y") | Years/months/days between dates |
=EDATE(start_date, months) | Adds months | =EDATE(A1, 3) | Adds 3 months to date |
=EOMONTH(start_date, months) | End of month | =EOMONTH(A1, 1) | Gets last day of next month |
=WEEKDAY(date) | Day of week | =WEEKDAY(A1) | Sunday = 1 by default |
=WORKDAY(start_date, days, [holidays]) | Skips weekends | โ | Finds future workdays |
=NETWORKDAYS(start_date, end_date, [holidays]) | Counts working days | โ | Excludes weekends & holidays |
Final Thoughts
Learning these most used Excel functions will help you:
- Automate daily tasks
- Clean and organize data
- Analyze and visualize information
- Create dashboards and reports
- Make smart, data-based decisions
If youโre just starting out, practice these formulas step by step. With regular use, youโll master Excel faster and boost your productivity at work or study.
Leave a Reply