Mastering yourself with Spreadsheet Formula List is always going to be a dream come true for Spreadsheet users. With the spreadsheet formula list, you can create a formula that manipulates data and calculate strings and numbers. The Spreadsheet formulas can be a bit tricky as well as exceptionally powerful.

Spreadsheet Formulas list

The table below provides you with a list of formulas and functions the Spreadsheet supports. There exist more than 400+ Spreadsheet formula and this number is increasing with every version. Download the complete spreadsheet formula list in PDF form.

Array Formulas

Formulas Name Formula Syntax
ARRAY_CONSTRAIN =ARRAY_CONSTRAI(input_range, num_rows, num_cols)
FREQUENCY =FREQUENCY(data, classes)
GROWTH =GROWTH(known_data_y, [known_data_x], [new_data_x], [b])
LINEST =LINEST(known_data_y, [known_data_x], [calculate_b], [verbose])
LOGEST =LOGEST(known_data_y, [known_data_x], [b], [verbose])
MDETERM =MDETERM(square_matrix)
MINVERSE =MINVERSE(square_matrix)
MMULT =MMULT(matrix1, matrix2)
SUMPRODUCT =SUMPRODUCT(array1, [array2, …])
TRANSPOSE =TRANSPOSE(array_or_range)
TREND =TREND(known_data_y, [known_data_x], [new_data_x], [b])

Database Formulas

Formulas Name Formula Syntax
DAVERAGE =DAVERAGE(database, field, criteria)
DCOUNT =DCOUNT(database, field, criteria)
DCOUNTA =DCOUNTA(database, field, criteria)
DGET =DGET(database, field, criteria)
DMAX =DMAX(database, field, criteria)
DMIN =DMIN(database, field, criteria)
DPRODUCT =DPRODUCT(database, field, criteria)
DSTDEV =DSTDEV(database, field, criteria)
DSUM =DSUM(database, field, criteria)
DVAR =DVAR(database, field, criteria)

Date Formulas

Formulas Name Formula Syntax
DATE =DATE(year, month, day)
DATEDIF =DATEDIF(start_date, end_date, unit)
DATEVALUE =DATEVALUE(date_string)
DAY =DAY(date)
DAYS =DAYS(end_date, start_date)
DAYS360 =DAYS360(start_date, end_date, [method])
EDATE =EDATE(start_date, months)
EOMONTH =EOMONTH(start_date, months)
HOUR =HOUR(time)
MINUTE =MINUTE(time)
MONTH =MONTH(date)
NETWORKDAYS =NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
NOW =NOW()
SECOND =SECOND(time)
TIME =TIME(hour, minute, second)
TIMEVALUE =TIMEVALUE(time_string)
TODAY =TODAY()
WEEKDAY =WEEKDAY(date, [type])
WEEKNUM =WEEKNUM(date, [type])
WORKDAY =WORKDAY(start_date, num_days, [holidays])
WORKDAY.INTL =WORKDAY.INTL(start_date, num_days, [weekend], [holidays])
YEAR =YEAR(date)

Engineering Formulas

Formulas Name Formula Syntax
BIN2DEC =BIN2DEC(signed_binary_number)
BIN2HEX =BIN2HEX(signed_binary_number, [significant_digits])
BIN2OCT =BIN2OCT(signed_binary_number, [significant_digits])
BITAND =BITAND(value1, value2)
BITLSHIFT =BITLSHIFT(value, shift_amount)
BITOR =BITOR(value1, value2)
BITRSHIFT =BITRSHIFT(value, shift_amount)
BITXOR =BITXOR(value1, value2)
COMPLEX =COMPLEX(real_part, imaginary_part, [suffix])
DEC2BIN =DEC2BIN(decimal_number, [significant_digits])
DEC2HEX =DEC2HEX(decimal_number, [significant_digits])
DEC2OCT =DEC2OCT(decimal_number, [significant_digits])
DELTA =DELTA(number1, [number2])
ERF =ERF(lower_bound, [upper_bound])
ERF.PRECISE =ERF.PRECISE(lower_bound, [upper_bound])
GESTEP =GESTEP(value, [step])
HEX2BIN =HEX2BIN(signed_hexadecimal_number, [significant_digits])
HEX2DEC =HEX2DEC(signed_hexadecimal_number)
HEX2OCT =HEX2OCT(signed_hexadecimal_number, [significant_digits])
IMABS =IMABS(number)
IMAGINARY =IMAGINARY(complex_number)
IMARGUMENT =IMARGUMENT(number)
IMCOS =IMCOS(number)

Filter Formulas

Formulas Name Formula Syntax
FILTER =FILTER(range, condition1, [condition2])
SORT =SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2])
SORTN =SORTN(range, [n], [display_ties_mode], {sort_column1, is_ascending1], …)
UNIQUE =UNIQUE(range)

Financial Formulas

Formulas Name Formula Syntax
DB =DB(cost, salvage, life, period, [month])
DDB =DDB(cost, salvage, life, period, [factor])
DISC =DISC(settlement, maturity, price, redemption, [day_count_convention])
DOLLARDE =DOLLARDE(fractional_price, unit)
DOLLARFR =DOLLARFR(decimal_price, unit)
DURATION =DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
EFFECT =EFFECT(nominal_rate, periods_per_year)
FV =FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
FVSCHEDULE =FVSCHEDULE(principal, rate_schedule)
INTRATE =INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention])
IPMT =IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])
IRR =IRR(cashflow_amounts, [rate_guess])
ISPMT =ISPMT(rate, period, number_of_periods, present_value)
MIRR =MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)
NOMINAL =NOMINAL(effective_rate, periods_per_year)
NPER =NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])
NPV =NPV(discount, cashflow1, [cashflow2, …])
PMT =PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])
PRICE =PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])
PV =PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])
RATE =RATE(number_of_periods, payment_per_period,present_value, [future_value], [end_or_beginning], [rate_guess])
XIRR =XIRR(cashflow_amounts, cashflow_dates, [rate_guess])
YIELD =YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])

Google Formulas

Formulas Name Formula Syntax
ARRAYFORMULA =ARRAYFORMULA(array_fromula)
DETECTLANGUAGE =DETECTLANGUAGE(text_or_range)
GOOGLEFINANCE =GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date | num_days], [interval])
GOOGLETRANSLATE =GOOGLETRANSLATE(text, [source_language], [target_language])
IMAGE =IMAGE(url, [mode], [height], [width])
QUERY =QUERY(data, query, [headers])
SPARKLINE =SPARKLINE(data, [options])

Info Formulas

Formulas Name Formula Syntax
ERROR.TYPE =ERROR.TYPE(reference)
ISBLANK =ISBLANK(value)
ISDATE =ISDATE(value)
ISMAIL =ISMAIL(value)
ISERR =ISERR(value)
ISERROR =ISERROR(value)
ISFORMULA =ISFORMULA(cell)
ISLOGICAL =ISLOGICAL(value)
ISNA =ISNA(value)
ISNONTEXT =ISNOTEXT(value)
ISNUMBER =ISNUMBER(value)
ISREF =ISREF(value)
ISTEXT =ISTEXT(value)
N =N(value)
NA =NA()
TYPE =TYPE(value)
CELL =CELL(info_type, reference)

Lookup Formulas

Formulas Name Formula Syntax
AND =AND(logical_expression1, [logical_expression2, …])
FALSE =FALSE()
IF =IF(logical_expression, value_if_true, value_if_false)
IFERROR =IFERROR(value, [value_if_ error])
IFNA =IFNA(value, value_if_na)
IFS =IFS(conditiona1, value1, [condition2, value2], …)
NOT =NOT(logical_expression)
OR =OR(logical_expression1, [logical_expression2, …])
SWITCH =SWITCH(expression, case1, vaue1, [default or case2, value2], …)
TRUE =TRUE()
ADDRESS =ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])
CHOOSE =CHOOSE(index, choice1, [choice2, …])
COLUMN =COLUMN([cell_reference])
COLUMNS =COLUMNS(range)
FORMULATEXT =FORMULATEXT(cell)
GETPIVOTDATA =GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, …], [pivot_item, …])
HLOOKUP =HLOOKUP(search_key, range, index, [is_sorted])
INDEX =INDEX(reference, [row], [column])
INDIRECT =INDIRECT(cell_reference_as_string, [is_A1_notation])
LOOKUP =LOOKUP(searh_key, search_range | search_result_array, [result_range])
MATCH =MATCH(search_key, range, [search_type])
OFFSET =OFFSSET(cell_reference, offset_rows, offset_columns, [height], [width])
ROW =ROW([cell_reference])
ROWS =ROWS(range)
VLOOKUP =VLOOKUP(search_key, range, index, [is_sorted])

Math Formulas

Formulas Name Formula Syntax
ABS =ABS(value)
ACOS =ACOS(value)
ACOSH =ACOSH(value)
ACOT =ACOT(value)
BASE =BASE(value, base, [min_length])
CEILING =CEILING(value, [factor])
COMBIN =COMBIN(n, k)
COMBINA =COMBINA(n, k)
COS =COS(angle)
COUNTBLANK =COUNTBLANK(range)
COUNTIF =COUNTIF(range, criterion)
COUNTIFS =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …])
COUNTUNIQUE =COUNTUNIQUE(value1, [value2, …])
DECIMAL =DECIMAL(value, base)
DEGREES =DEGREES(angle)
IMSQRT =IMSQRT(complex_number)
INT =INT(value)
SUMIF =SUMIF(range, criterion, [sum_range])
SUMIFS =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
MROUND =MROUND(value, factor)
POWER =POWER(base, exponent)
PRODUCT =PRODUCT(factor1, [factor2, …])
RAND =RAND()
RANDBETWEEN =RANDBETWEEN(low, high)
ROUND =ROUND(value, [places])
ROUNDDOWN =ROUNDDOWN(value, [places])
ROUNDUP =ROUNDUP(value, [places])
SUM =SUM(value1, [values2, …])

Operator Formulas

Formulas Name Formula Syntax
ADD =ADD(value1, value2)
CONCAT =CONCAT(value1, value2)
DIVIDE =DIVIDE(dividend, divisor)
EQ =EQ(value1, value2)

Statistical Formulas

Formulas Name Formula Syntax
AVERAGE =AVERAGE(value1, [value2, …])
AVERAGEIF =AVERAGEIF(criteria_range, criterion, [average_range])
AVERAGEIFS =AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
CORREL =CORREL(data_y, data_x)
COUNT =COUNT(value1, [value2, …])
COUNTA =COUNTA(value1, [value2, …])
MAX =MAX(value1, [value2, …])
MAXA =MAXA(value1, value2)
MAXIFS =MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], …)
MEDIAN =MEDIAN(value1, [value2, …])
MIN =MIN(value1, [value2, …])
MINIFS =MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], …)
MODE =MODE(value1, [value2, …])
PERCENTILE =PERCENTILE(data, percentile)

Text - Spreadsheet formulas list

Formulas Name Formula Syntax
CHAR =CHAR(table_number)
CLEAN =CLEAN(text)
CONCATENATE =CONCATENATE(string1, [string2, …])
EXACT =EXACT(string1, string2)
FIND =FIND(search_for, text_to_search, [starting_at])
JOIN =JOIN(delimiter, value_or_array1, [value_or_array2, …])
LEFT =LEFT(string, [number_of_characters])
LEN =LEN(text)
LOWER =LOWER(text)
MID =MID(string, starting_ate, extract_length)
PROPER =PROPER(text_to_capitalize)
REPLACE =REPLACE(text, position, length, new_text)
REPT =REPT(text_to_repeat, number_of_repetitions)
RIGHT =RIGHT(string, [number_of_characters])
SEARCH =SEARCH(search_for, text_to_search, [starting_at])
SUBSTITUTE =SUBSTITUTE(text_tp_search, search_for, replace_with, [occurrence_number])
TEXT =TEXT(number, format)
TRIM =TRIM(text)
UPPER =UPPER(text)
VALUE =VALUE(text)

You do not have to worry about memorizing the complete Spreadsheet Formula List. But, you must consider learning the most important and most used once daily. Some of the formulas that are definitely used in day to basis are SUM(), AVERAGE(), MAX(), MIN(), COUNT(), COUNTA(), IF(), TRIM(), LEN(), AND(), OR(), CONCATENATE(), TODAY(), NOW() and etc.

To help Spreadsheet users more effectively and save a lot of time we have compiled you with a list of Google sheet keyboard shortcuts along with Excel shortcut keys. And as it is rightly said Practice makes a man perfect. So practicing the spreadsheet formula list will make you a Spreadsheet Ninja!

Hope you have liked our blog on Google Spreadsheet Formulas List. Suggestions are always welcomed for the blog you looking forward to hearing from us. Like our My e-Learning Hub Facebook Page and Subscribe to our My e-Learning Hub YouTube Channel. Connect with us and Take your Office Productivity to Next Level.

Leave a comment

Main Office:

Mumbai, India

Contact Us:

[email protected]

MyeLearningHUB © 2019. All rights reserved.