Excel Functions: Text, Date & Time, Statistical, and Mathematical Functions
1. Text Functions in Excel.
Overview
Text functions in Excel help manipulate, format, and process text data efficiently. These functions are useful for cleaning data, extracting information, and formatting text.
Common Text Functions.
| Function | Description | Example & Output |
|---|---|---|
| LEFT | Extracts a specific number of characters from the start of a string. | =LEFT("Excel", 2) → Ex |
| RIGHT | Extracts a specific number of characters from the end of a string. | =RIGHT("Excel", 2) → el |
| MID | Extracts a portion of text from the middle of a string. | =MID("Excel", 2, 2) → xc |
| LEN | Returns the length of a text string. | =LEN("Excel") → 5 |
| TRIM | Removes extra spaces from text. | =TRIM(" Excel ") → Excel |
| CONCAT | Joins multiple text values together. | =CONCAT("Hello", " World") → Hello World |
| TEXT | Formats numbers and dates as text. | =TEXT(1234.5, "$#,##0.00") → $1,234.50 |
| SUBSTITUTE | Replaces text in a string. | =SUBSTITUTE("Excel 2024", "2024", "2025") → Excel 2025 |
| UPPER | Converts text to uppercase. | =UPPER("excel") → EXCEL |
| LOWER | Converts text to lowercase. | =LOWER("EXCEL") → excel |
| PROPER | Capitalizes the first letter of each word. | =PROPER("hello world") → Hello World |
2. Date & Time Functions in Excel
Overview
Date and time functions help users handle and manipulate date and time data efficiently.
Common Date & Time Functions
Excel Date & Time Functions
| Function | Description | Example & Output |
|---|---|---|
| TODAY | Returns the current date. | =TODAY() → 2025-04-03 (Example output) |
| NOW | Returns the current date and time. | =NOW() → 2025-04-03 12:30 PM |
| DATE | Creates a date from year, month, and day. | =DATE(2025,4,3) → 2025-04-03 |
| DAY | Extracts the day from a date. | =DAY("2025-04-03") → 3 |
| MONTH | Extracts the month from a date. | =MONTH("2025-04-03") → 4 |
| YEAR | Extracts the year from a date. | =YEAR("2025-04-03") → 2025 |
| HOUR | Extracts the hour from a time. | =HOUR("12:30 PM") → 12 |
| MINUTE | Extracts the minute from a time. | =MINUTE("12:30 PM") → 30 |
| SECOND | Extracts the second from a time. | =SECOND("12:30:45") → 45 |
| EOMONTH | Returns the last day of a given month. | =EOMONTH("2025-04-03",0) → 2025-04-30 |
3. Statistical Functions in Excel
Overview
Statistical functions allow users to analyze numerical data, compute averages, and perform statistical calculations.
Common Statistical Functions
Excel Statistical Functions
| Function | Description | Example & Output |
|---|---|---|
| AVERAGE | Returns the average of numbers. | =AVERAGE(10, 20, 30) → 20 |
| MEDIAN | Returns the median of a range. | =MEDIAN(10, 20, 30, 40, 50) → 30 |
| MODE | Returns the most frequently occurring number. | =MODE(1,2,2,3,4,4,4,5) → 4 |
| COUNT | Counts numeric values in a range. | =COUNT(A1:A10) → Depends on data |
| COUNTA | Counts non-empty cells in a range. | =COUNTA(A1:A10) → Depends on data |
| COUNTIF | Counts cells that meet a condition. | =COUNTIF(A1:A10, ">50") → Depends on data |
| VAR.S | Returns sample variance. | =VAR.S(10, 20, 30) → 100 |
| STDEV.S | Returns sample standard deviation. | =STDEV.S(10, 20, 30) → 10 |
| LARGE | Returns the nth largest value. | =LARGE(A1:A10,2) → Second highest value |
| SMALL | Returns the nth smallest value. | =SMALL(A1:A10,2) → Second smallest value |
4. Mathematical Functions in Excel
Overview
Mathematical functions perform arithmetic calculations and advanced mathematical operations.
Common Mathematical Functions
Excel Functions Table
| Function | Description | Example & Output |
|---|---|---|
| SUM | Adds values in a range. | =SUM(10,20,30) → 60 |
| PRODUCT | Multiplies values. | =PRODUCT(2,3,4) → 24 |
| ROUND | Rounds a number to a specified decimal place. | =ROUND(12.3456, 2) → 12.35 |
| ROUNDUP | Rounds a number up. | =ROUNDUP(12.3456, 2) → 12.35 |
| ROUNDDOWN | Rounds a number down. | =ROUNDDOWN(12.3456, 2) → 12.34 |
| ABS | Returns the absolute value. | =ABS(-15) → 15 |
| MOD | Returns the remainder of division. | =MOD(10,3) → 1 |
| POWER | Raises a number to a power. | =POWER(2,3) → 8 |
| SQRT | Returns the square root. | =SQRT(16) → 4 |
| EXP | Returns e raised to a power. | =EXP(1) → 2.718 |
| LOG | Returns the logarithm of a number. | =LOG(100,10) → 2 |