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, Google Sheets Formulas PDF
Summary
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 google sheets formulas pdf.
Google Spreadsheet Shortcuts PDF
Array Formulas
| Spreadsheet 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]) |
Related Blog,
Most used Google Sheets Formulas with Examples.
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) |
Related Blog,
Most used Google Sheets Shortcuts – Download Sheets Shortcuts
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 May Also like
21 Most used YouTube Shortcuts you Should know
Top 5 Google Docs Add ons you should try in 2020
Install Google Chrome in Windows and Mac Step-by-Step
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. Connect with us and Take your Office Productivity to Next Level.
