Basic Excel Functions Guide
Master the foundational Excel functions every beginner needs. Learn syntax, real-world examples, and copy-paste ready formulas for daily work.
🧮Math & Trig Basic Functions
29 functionsSUM
★ EssentialAdds all numbers in a range of cells.
=SUM(number1, [number2], ...)=SUM(A2:A100)RANDBETWEEN
Returns a random integer number between the numbers you specify.
=RANDBETWEEN(bottom, top)=RANDBETWEEN(1, 100)SEQUENCE
★ EssentialGenerates an array of sequential numbers, such as 1, 2, 3, 4.
=SEQUENCE(rows, [columns], [start], [step])=SEQUENCE(10, 1, 100, 10)PRODUCT
Multiplies all the numbers given as arguments.
=PRODUCT(number1, [number2], ...)=PRODUCT(A2:A5)INT
Rounds a number down to the nearest integer.
=INT(number)=INT(8.9)POWER
Returns the result of a number raised to a power.
=POWER(number, power)=POWER(5, 2)SQRT
Returns a positive square root of a number.
=SQRT(number)=SQRT(144)AGGREGATE
Performs aggregate calculations (SUM, AVERAGE, MAX) while ignoring hidden rows and error values.
=AGGREGATE(function_num, options, ref1, ...)=AGGREGATE(9, 6, A2:A100)TRUNC
Truncates a number to a specified number of fractional digits by discarding remaining digits.
=TRUNC(number, [num_digits])=TRUNC(8.927, 2)MROUND
Returns a number rounded to the desired multiple.
=MROUND(number, multiple)=MROUND(10, 3)CEILING
Rounds a number up, away from zero, to the nearest multiple of significance.
=CEILING(number, significance)=CEILING(2.5, 1)FLOOR
Rounds a number down, toward zero, to the nearest multiple of significance.
=FLOOR(number, significance)=FLOOR(2.5, 1)SUMSQ
Returns the sum of the squares of the arguments.
=SUMSQ(number1, [number2], ...)=SUMSQ(3, 4)QUOTIENT
Returns the integer portion of a division, discarding the remainder.
=QUOTIENT(numerator, denominator)=QUOTIENT(10, 3)SIGN
Returns the sign of a number (1 for positive, 0 for zero, -1 for negative).
=SIGN(number)=SIGN(-15)LOG10
Returns the base-10 logarithm of a number.
=LOG10(number)=LOG10(1000)PI
Returns the mathematical constant Pi accurate to 15 digits (3.14159265358979).
=PI()=PI()RAND
Returns an evenly distributed random real number greater than or equal to 0 and less than 1.
=RAND()=RAND()CEILING.MATH
Rounds a number up to the nearest integer or multiple of significance.
=CEILING.MATH(number, [significance], [mode])=CEILING.MATH(4.3)FLOOR.MATH
Rounds a number down to the nearest integer or multiple of significance.
=FLOOR.MATH(number, [significance], [mode])=FLOOR.MATH(4.8)SUMIFS
★ EssentialAdds the cells in a range that meet multiple specified criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=SUMPRODUCT(QtyRange, PriceRange)SUMIF
★ EssentialAdds the cells specified by a single given condition or criterion.
=SUMIF(range, criteria, [sum_range])=SUM(A2:A100)ROUND
★ EssentialRounds a number to a specified number of digits.
=ROUND(number, num_digits)=ROUND(3.14159, 2)ROUNDUP
Rounds a number up, away from zero.
=ROUNDUP(number, num_digits)=ROUNDUP(3.14159, 2)ROUNDDOWN
Rounds a number down, toward zero.
=ROUNDDOWN(number, num_digits)=ROUNDDOWN(3.14159, 2)ABS
Returns the absolute value of a number (without its sign).
=ABS(number)=ABS(-15)MOD
Returns the remainder after a number is divided by a divisor.
=MOD(number, divisor)=MOD(10, 3)SUMPRODUCT
★ EssentialReturns the sum of the products of corresponding arrays or ranges.
=SUMPRODUCT(array1, [array2], ...)=SUMPRODUCT(B2:B50, C2:C50)SUBTOTAL
Returns a subtotal in a list or database. Can ignore hidden rows.
=SUBTOTAL(function_num, ref1, ...)=SUBTOTAL(9, A2:A100)🔎Lookup & Reference Basic Functions
19 functionsTRANSPOSE
Flips the orientation of a range or array from horizontal to vertical, or vice versa.
=TRANSPOSE(array)=TRANSPOSE(A1:D1)LOOKUP
Looks up a value in a single-row or single-column range and returns a value from the same position in another range.
=LOOKUP(lookup_value, lookup_vector, [result_vector])=LOOKUP(A2, D2:D10, E2:E10)CHOOSE
Returns a value from a list of arguments based on an index number.
=CHOOSE(index_num, value1, [value2], ...)=ROW(A5)COLUMN
Returns the column number of a cell reference.
=COLUMN([reference])=COLUMN(C1)SORTBY
Sorts the contents of a range or array based on the values in a corresponding range or array.
=SORTBY(array, by_array1, [sort_order1], ...)=SORTBY(A2:B10, B2:B10, -1)ADDRESS
Creates a cell reference as text from given row and column numbers.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])=ROWS(A1:C10)COLUMNS
Returns the total number of columns in a reference or array.
=COLUMNS(array)=COLUMNS(A1:C10)SORT
Sorts the contents of a range or array automatically in ascending or descending order.
=SORT(array, [sort_index], [sort_order], [by_col])=SORT(A2:B20, 2, -1)XLOOKUP
★ EssentialSearches a range or array for a match and returns an item from a corresponding second range. Replaces VLOOKUP, HLOOKUP, and INDEX/MATCH.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=VLOOKUP(101, A2:D50, 3, FALSE)INDEX
★ EssentialReturns a value or reference to a value from within a table or range at a given row and column position.
=INDEX(array, row_num, [column_num])=INDEX(C2:C100, 5)MATCH
★ EssentialSearches for a specified item in a range of cells and returns the relative position of that item.
=MATCH(lookup_value, lookup_array, [match_type])=VLOOKUP(101, A2:D50, 3, FALSE)VLOOKUP
★ EssentialLooks up a value in the first column of a table and returns a value in the same row from another column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP(101, A2:D50, 3, FALSE)HLOOKUP
Searches top row of a table and returns value in same column from specified row.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])=HLOOKUP(101, A1:H5, 2, FALSE)ROW
Returns the row number of a cell reference.
=ROW([reference])=ROW(A5)ROWS
Returns number of rows in a reference or array.
=ROWS(array)=ROWS(A2:A100)INDIRECT
Returns reference specified by a text string.
=INDIRECT(ref_text, [a1])=INDIRECT("A" & B2)OFFSET
Returns reference to range that is specified number of rows and columns from a cell.
=OFFSET(reference, rows, cols, [height], [width])=OFFSET(A1, 2, 3)FILTER
★ EssentialFilters a range or array based on a condition you define.
=FILTER(array, include, [if_empty])=FILTER(A2:C50, B2:B50>100)UNIQUE
★ EssentialReturns a list of unique values from a range or array.
=UNIQUE(array, [by_col], [exactly_once])=UNIQUE(A2:A50)⚖️Logical Basic Functions
20 functionsISNUMBER
Checks whether a cell contains a numeric value.
=ISNUMBER(value)=NOT(A2>50)IFNA
Returns a value you specify if a formula evaluates to the #N/A error value; otherwise returns the result of the formula.
=IFNA(value, value_if_na)=RANDARRAY(5, 2, 1, 100, TRUE)ISTEXT
Checks whether a cell contains text value.
=ISTEXT(value)=XOR(A2>0, B2>0)TRUE
Returns the logical value TRUE.
=TRUE()=TRUE()FALSE
Returns the logical value FALSE.
=FALSE()=FALSE()ISNONTEXT
Checks whether a cell contains a value that is not text (blank cells are nontext).
=ISNONTEXT(value)=ISNONTEXT(A2)ISLOGICAL
Checks whether a value is a logical value (TRUE or FALSE).
=ISLOGICAL(value)=ISLOGICAL(A2)ISERR
Checks whether a value is any error except #N/A.
=ISERR(value)=ISERR(A2/B2)ISNA
Checks whether a value is the #N/A error.
=ISNA(value)=ISNA(VLOOKUP(A2, B:C, 2, FALSE))ISEVEN
Returns TRUE if the number is even, or FALSE if odd.
=ISEVEN(number)=ISEVEN(4)ISODD
Returns TRUE if the number is odd, or FALSE if even.
=ISODD(number)=ISODD(7)IF
★ EssentialChecks whether a condition is met and returns one value if TRUE and another if FALSE.
=IF(logical_test, value_if_true, [value_if_false])=IF(A2>100, "Over Budget", "OK")IFS
★ EssentialChecks whether one or more conditions are met and returns a value corresponding to the first TRUE condition.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)=IFS(A2>=90, "A", A2>=80, "B", TRUE, "F")AND
Returns TRUE if all of its arguments evaluate to TRUE.
=AND(logical1, [logical2], ...)=AND(A2>10, B2<20)OR
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
=OR(logical1, [logical2], ...)=OR(A2="Red", A2="Blue")NOT
Reverses the logical value of its argument.
=NOT(logical)=NOT(A2>100)IFERROR
★ EssentialReturns a value specified if a formula evaluates to an error; otherwise returns the formula result.
=IFERROR(value, value_if_error)=IFERROR(A2/B2, 0)XOR
Returns a logical Exclusive OR of all arguments.
=XOR(logical1, [logical2], ...)=XOR(A2>5, B2>5)ISBLANK
Checks whether a cell is blank and returns TRUE or FALSE.
=ISBLANK(value)=ISBLANK(A2)ISERROR
Checks whether a value is any error.
=ISERROR(value)=ISERROR(A2/B2)🔤Text Basic Functions
25 functionsPROPER
Capitalizes the first letter of each word in a text string and converts all other letters to lowercase.
=PROPER(text)=WORKDAY(A2, 10, H2:H5)VALUE
Converts a text string that represents a number into a true numeric value.
=VALUE(text)=CLEAN(A2)EXACT
Checks whether two text strings are exactly identical (case-sensitive).
=EXACT(text1, text2)=CHAR(10)VSTACK
★ EssentialReturns the specified number of characters from the start of a text string.
=LEFT(text, [num_chars])=LEFT(A2, 3)RIGHT
Capitalizes the first letter of each word in a text string and converts all other letters to lowercase.
=PROPER(text)=TRUNC(8.927, 2)LEFT
Returns the specified number of characters from the start of a text string.
=LEFT(text, [num_chars])=LEFT(A2, 3)CODE
Returns a numeric code for the first character in a text string.
=CODE(text)=UNICHAR(9733)FIXED
Rounds a number to the specified number of decimals and formats it as text with or without commas.
=FIXED(number, [decimals], [no_commas])=FIXED(1234.567, 2, FALSE)NUMBERVALUE
Converts text to a number in a locale-independent manner.
=NUMBERVALUE(text, [decimal_separator], [group_separator])=CHAR(10)MID
★ EssentialExtracts characters from the middle of a text string given a starting position and length.
=MID(text, start_num, num_chars)=MID("ID-9876-US", 4, 4)LEN
★ EssentialReturns the length in number of characters of a text string.
=LEN(text)=LEN("Excel")TRIM
★ EssentialRemoves all spaces from a text string except for single spaces between words.
=TRIM(text)=TRIM(A2)UPPER
Converts all letters in a text string to uppercase.
=UPPER(text)=UPPER("excel")LOWER
Converts all letters in a text string to lowercase.
=LOWER(text)=LOWER("EXCEL")CONCAT
Combines text from multiple ranges and/or strings.
=CONCAT(text1, [text2], ...)=CONCAT("Hello ", "World")CONCATENATE
★ EssentialJoins two or more text strings into one string.
=CONCATENATE(text1, [text2], ...)=CONCATENATE(A2, " ", B2)TEXTJOIN
★ EssentialCombines text from multiple ranges/strings with a delimiter.
=TEXTJOIN(delimiter, ignore_empty, text1, ...)=TEXTJOIN(", ", TRUE, A2:A5)FIND
Finds one text string within another (case-sensitive).
=FIND(find_text, within_text, [start_num])=FIND("E", "Excel")SEARCH
Finds one text string within another (case-insensitive).
=SEARCH(find_text, within_text, [start_num])=SEARCH("e", "EXCEL")SUBSTITUTE
★ EssentialSubstitutes new text for old text in a string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])=SUBSTITUTE(A2, "2025", "2026")REPLACE
Replaces characters within text based on position and length.
=REPLACE(old_text, start_num, num_chars, new_text)=REPLACE(A2, 1, 4, "2026")REPT
Repeats text a specified number of times.
=REPT(text, number_times)=REPT("*", 5)CHAR
Returns character specified by code number.
=CHAR(number)=CHAR(10)CLEAN
Removes all non-printable characters from text.
=CLEAN(text)=CLEAN(A2)TEXT
★ EssentialFormats a number as text using a specified format.
=TEXT(value, format_text)=TEXT(TODAY(), "YYYY-MM-DD")📅Date & Time Basic Functions
22 functionsMONTH
Returns the month (a number from 1 to 12) of a date.
=MONTH(serial_number)=MONTH(A2)DATE
Returns the serial number of a specific date from individual year, month, and day arguments.
=DATE(year, month, day)=DATE(2026, 8, 28)DAY
Returns the day of the month (a number from 1 to 31) for a given date.
=DAY(serial_number)=DAY(TODAY())WEEKDAY
Returns a number representing the day of the week for a date (1 for Sunday through 7 for Saturday by default).
=WEEKDAY(serial_number, [return_type])=WEEKDAY(TODAY())WEEKNUM
Returns the week number of a specific date in a year.
=WEEKNUM(serial_number, [return_type])=WEEKNUM(TODAY())TIME
Returns the decimal fraction for a particular time.
=TIME(hour, minute, second)=TIME(14, 30, 0)HOUR
Returns the hour of a time value as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
=HOUR(serial_number)=HOUR(NOW())MINUTE
Returns the minute of a time value as a number from 0 to 59.
=MINUTE(serial_number)=MINUTE(NOW())SECOND
Returns the second of a time value as a number from 0 to 59.
=SECOND(serial_number)=SECOND(NOW())ISOWEEKNUM
Returns the ISO week number of the year for a given date.
=ISOWEEKNUM(date)=ISOWEEKNUM(TODAY())DAYS
Returns the number of days between two dates.
=DAYS(end_date, start_date)=DAYS(B2, A2)DAYS360
Calculates the number of days between two dates based on a 360-day year (twelve 30-day months).
=DAYS360(start_date, end_date, [method])=DAYS360(A2, B2)YEAR
Returns the year corresponding to a date as a 4-digit number.
=YEAR(serial_number)=YEAR(TODAY())TODAY
★ EssentialReturns the current date.
=TODAY()=TODAY()NOW
Returns the current date and time.
=NOW()=NOW()EDATE
Returns date specified number of months before or after start date.
=EDATE(start_date, months)=EDATE(TODAY(), 6)EOMONTH
Returns date for last day of month before or after specified months.
=EOMONTH(start_date, months)=EOMONTH(TODAY(), 0)DATEDIF
Calculates number of days, months, or years between two dates.
=DATEDIF(start_date, end_date, unit)=DATEDIF(A2, B2, "Y")NETWORKDAYS
Returns number of whole workdays between two dates.
=NETWORKDAYS(start_date, end_date, [holidays])=NETWORKDAYS(A2, B2, H2:H5)NETWORKDAYS.INTL
Returns number of whole workdays with custom weekend parameters.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])=NETWORKDAYS.INTL(A2, B2, 1, H2:H5)WORKDAY
Returns date before or after specified number of workdays.
=WORKDAY(start_date, days, [holidays])=WORKDAY(TODAY(), 10, H2:H5)WORKDAY.INTL
Returns date before or after specified number of workdays with custom weekend parameters.
=WORKDAY.INTL(start_date, days, [weekend], [holidays])=WORKDAY.INTL(TODAY(), 10, 1)📊Statistical Basic Functions
15 functionsSMALL
Returns the k-th smallest value in a data set.
=SMALL(array, k)=SMALL(B2:B100, 1)AVERAGE
★ EssentialReturns the average (arithmetic mean) of its arguments.
=AVERAGE(number1, [number2], ...)=AVERAGE(B2:B100)COUNT
★ EssentialCounts how many cells in a range contain numbers.
=COUNT(value1, [value2], ...)=COUNT(A2:A100)COUNTIF
★ EssentialCounts the number of cells within a range that meet a single given condition.
=COUNTIF(range, criteria)=MAX(C2:C100)MIN
★ EssentialReturns the smallest value in a set of values.
=MIN(number1, [number2], ...)=MIN(C2:C100)MEDIAN
Returns the median (the middle number) of the given numbers.
=MEDIAN(number1, [number2], ...)=MEDIAN(C2:C100)MODE.SNGL
Returns the most frequently occurring value in an array or range of data.
=MODE.SNGL(number1, [number2], ...)=MODE.SNGL(C2:C100)RANK.EQ
Returns the rank of a number in a list of numbers.
=RANK.EQ(number, ref, [order])=RANK.EQ(B2, B$2:B$100, 0)COUNTA
★ EssentialCounts the number of cells in a range that are not empty.
=COUNTA(value1, [value2], ...)=COUNTA(A2:A100)COUNTBLANK
Counts empty cells in a specified range of cells.
=COUNTBLANK(range)=COUNTBLANK(B2:B50)MAX
★ EssentialReturns the largest value in a set of values.
=MAX(number1, [number2], ...)=MAX(A2:A100)LARGE
Returns the k-th largest value in a data set.
=LARGE(array, k)=LARGE(B2:B100, 1)COUNTIFS
★ EssentialCounts the number of cells that meet multiple criteria.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)=COUNTIFS(A2:A50, "North", B2:B50, ">100")AVERAGEIF
Calculates the average of cells that meet a specified condition.
=AVERAGEIF(range, criteria, [average_range])=AVERAGEIF(A2:A50, ">=50")AVERAGEIFS
Calculates the average of cells that meet multiple criteria.
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)=AVERAGEIFS(C2:C50, A2:A50, "North", B2:B50, ">100")💰Financial Basic Functions
1 functionSLN
Returns the straight-line depreciation of an asset for one period.
=SLN(cost, salvage, life)=SLN(30000, 7500, 10)🧪Engineering Basic Functions
1 functionDEC2BIN
Converts a decimal number to binary.
=DEC2BIN(number, [places])=DEC2BIN(9)Master Advanced Excel Functions
Learn XLOOKUP, INDEX & MATCH, SUMIFS, FILTER, UNIQUE, SORT, and dynamic array formulas.