Formulas and Functions are the true power of Excel. From simple arithmetic to advanced lookups, they help you perform calculations, analyze data, and automate repetitive tasks. Whether you’re adding numbers, searching values, or creating logical tests, mastering formulas & functions will make your Excel work faster and smarter.
This guide gives you a complete overview of functions in Excel. Bookmark this page—this will be your go-to hub for everything related to Excel calculations.
What is a Function in Excel?
A function is a predefined formula built into Excel. It saves time by performing common calculations automatically.
- Example: `=SUM(A1:A10)` → adds all values from A1 to A10.
How to Use a Function in Excel?
1. Start with `=`.
2. Type the function name.
3. Add arguments inside brackets. Example: `=AVERAGE(A1:A10)`
Categories of Excel Functions & Formulas:
Excel has over 450+ functions and many more formulas, they can be grouped into major categories. Below is the list with short explanations. Each category will link to detailed articles for individual functions & Formulas.
Full List of Excel Functions & Formulas (by Category)
1) Mathematical & Trigonometric Functions
- SUM — add numbers
- SUMIF / SUMIFS — conditional sum
- PRODUCT — multiply numbers
- QUOTIENT — integer part of division
- POWER — raise to a power
- SQRT — square root
- MOD — remainder
- ROUND / ROUNDUP / ROUNDDOWN — round to digits
- INT — round down to integer
- TRUNC — truncate decimals
- CEILING / FLOOR — round to specified multiple
- ABS — absolute value
- EXP — e to a power
- FACT — factorial
- PI — π constant
- RAND — random 0–1
- RANDBETWEEN — random in range
- LOG — logarithm with base
- LOG10 — base-10 logarithm
- SIN / COS / TAN — trig functions
- ASIN / ACOS / ATAN — inverse trig
- DEGREES / RADIANS — angle conversions
- MROUND — round to nearest multiple
- EVEN / ODD — round to next even/odd
- SIGN — sign of a number (-1/0/1)
2) Statistical Functions
- AVERAGE — mean
- AVERAGEIF / AVERAGEIFS — conditional average
- MEDIAN — middle value
- MODE — most frequent
- COUNT — count numbers
- COUNTA — count non-blank
- COUNTBLANK — count blanks
- COUNTIF — conditional count with one condition
- COUNTIFS — conditional count with multiple conditions
- MAX / MIN — largest/smallest
- LARGE / SMALL — kth largest/smallest
- RANK / RANK.EQ / RANK.AVG — rank position
- STDEV.S / STDEV.P — standard deviation
- VAR.S / VAR.P — variance
- PERCENTILE / .INC / .EXC — kth percentile
- QUARTILE — quartile value
- FREQUENCY — frequency distribution
- CORREL — correlation coefficient
- COVAR — covariance
- NORM.DIST / NORM.S.DIST — normal distribution
- NORM.INV / NORM.S.INV — inverse normal
- FORECAST — predicted value
- TREND — linear trend
3) Text Functions
- CONCATENATE / CONCAT — join text
- TEXTJOIN — join text with delimiter
- LEFT / RIGHT / MID — extract text
- LEN — length of text
- TRIM — remove extra spaces
- UPPER / LOWER / PROPER — change case
- REPLACE — replace by position
- SUBSTITUTE — replace by match
- FIND — position, case-sensitive
- SEARCH — position, case-insensitive
- EXACT — case-sensitive compare
- VALUE — text → number
- TEXT — number → formatted text
- CHAR — character by code
- CODE — code of first character
- CLEAN — remove non-printables
- REPT — repeat text
- UNICODE — Unicode code point
- T — text from value
4) Date & Time Functions
- TODAY — current date
- NOW — current date & time
- DATE / TIME — build date/time
- DATEVALUE / TIMEVALUE — text → date/time
- DAY / MONTH / YEAR — extract parts
- WEEKDAY / WEEKNUM / ISOWEEKNUM — day/week info
- HOUR / MINUTE / SECOND — time parts
- EOMONTH — end of month
- NETWORKDAYS / .INTL — working days count
- WORKDAY / .INTL — next/prev workday
- DATEDIF — difference in units
- YEARFRAC — fraction of year
5) Logical Functions
- IF / IFS — conditional result
- AND / OR / NOT — logical tests
- IFERROR / IFNA — error handling
- SWITCH — choose by expression
- TRUE / FALSE — logical constants
- XOR — exclusive OR
6) Lookup & Reference Functions
- VLOOKUP / HLOOKUP — table lookup
- XLOOKUP — modern flexible lookup
- XMATCH / MATCH — position in range
- INDEX — return by row/column
- LOOKUP — approximate lookup
- OFFSET — reference offset (returns reference)
- CHOOSE — pick by index
- ROW / ROWS — row number/count
- COLUMN / COLUMNS — column number/count
- ADDRESS — cell address as text
- INDIRECT — reference from text (returns reference)
- FORMULATEXT — formula as text
- HYPERLINK — clickable link (returns link/text)
7) Financial Functions
- PMT / IPMT / PPMT — payment/interest/principal
- FV / PV — future/present value
- NPV / IRR / XIRR / MIRR — investment metrics
- RATE / NPER — rate/periods
- CUMIPMT / CUMPRINC — cumulative interest/principal
- EFFECT / NOMINAL — effective/nominal rate
- SLN / SYD / DDB / VDB — depreciation methods
8) Engineering Functions
- CONVERT — unit conversion (returns number/text)
- BIN2DEC / DEC2BIN / DEC2HEX / HEX2DEC / OCT2DEC / … — base conversions (returns text/number)
- FACT — factorial
9) Information Functions
- ISNUMBER / ISTEXT / ISBLANK / ISERROR / ISERR / ISNA / ISLOGICAL / ISFORMULA — type checks
- ERROR.TYPE — error code
- N / NA — convert to number / N/A (returns number/error)
- TYPE — data type code
- CELL / INFO — cell/workbook info (returns text/number)
- SHEET / SHEETS — sheet index/count
10) Array (Dynamic) Functions
- FILTER — filter by criteria
- SORT / SORTBY — sort data
- UNIQUE — distinct values
- SEQUENCE — number sequence
- RANDARRAY — random array
11) Database Functions
- DSUM / DAVERAGE / DMIN / DMAX / DPRODUCT — database aggregates
- DCOUNT / DCOUNTA — counts
- DVAR / DVARP / DSTDEV / DSTDEVP — variance/stdev
- GETPIVOTDATA — value from PivotTable
Excel formulas and functions are the backbone of spreadsheets. Start with basics like SUM, AVERAGE, and IF, then move toward advanced tools like VLOOKUP/XLOOKUP, INDEX-MATCH, and dynamic arrays. This guide is your one-stop hub—dive into detailed articles for each item and become an Excel power user step by step.