Formula Cheat Sheet.
Quickly lookup syntax, required parameters, and copy equations. Clean layout optimized for reading, searching, and physical printing.
Welcome to the Student Cheat Sheet! Easily find descriptions, copy equations, and learn what parameters mean.
| Category | Formula Name | Syntax Structure | Description | Copy |
|---|---|---|---|---|
| Lookup & Reference | XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | Searches a range or array and returns the corresponding item from a second range or array.Searches for a value in one list (lookup array) and returns matching information from another list (return array) in the exact same row. | |
| Lookup & Reference | VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Searches for a value in the first column of a table and returns a value in the same row from a specified column.Searches down the first column of a table to find a matching word, then reads across that row to get a value from a column you choose. | |
| Lookup & Reference | INDEX & MATCH | =INDEX(return_array, MATCH(lookup_value, lookup_array, [match_type])) | Combines two formulas to perform powerful two-way lookups, searching left and right.MATCH finds the row number of your lookup item. INDEX uses that row number to grab the corresponding value from another column. | |
| Logical & Conditional | IF | =IF(logical_test, value_if_true, [value_if_false]) | Checks whether a condition is met, and returns one value if TRUE, and another if FALSE.Checks whether a condition is true or false. If true, it outputs one value; if false, it outputs another. | |
| Logical & Conditional | IFS | =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...) | Checks multiple conditions and returns a value corresponding to the first TRUE condition.Checks multiple conditions in order and returns the grade/value matching the first true condition. | |
| Logical & Conditional | IFERROR | =IFERROR(value, value_if_error) | Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.Runs a formula, but if the formula fails (like a division by zero error), it gives a clean default value instead of Excel's ugly errors. | |
| Math & Arithmetic | SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Sums cells that meet multiple criteria.Adds up all numbers in a range that meet one or more conditions (e.g., adding sales only for West region and Apple product). | |
| Math & Arithmetic | COUNTIF | =COUNTIF(range, criteria) | Counts the number of cells within a range that meet a single specified condition or criteria.Counts how many cells in a list match a rule you specify, such as counting scores over 75 or orders marked "Yes". | |
| Statistical & Analysis | COUNTIFS | =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Counts cells that meet multiple criteria.Counts how many cells in one or more ranges meet specific conditions. | |
| Text & Manipulation | TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) | Combines text from multiple ranges or strings, and includes a delimiter you specify between each text value.Glues text from different cells together into one cell, placing a separator of your choice (like a comma) in between. | |
| Dynamic Arrays | FILTER | =FILTER(array, include, [if_empty]) | Filters a range or array based on conditions you define, spilling matching rows automatically.Sifts through a table of data and extracts only the rows that match your criteria, spilling the results down the sheet. | |
| Dynamic Arrays | UNIQUE | =UNIQUE(array, [by_col], [exactly_once]) | Extracts all unique values from a range or array, removing duplicates automatically.Looks at a list with duplicate entries and returns a clean list containing only one copy of each item. | |
| Date & Time | TODAY | =TODAY() | Returns the current date.Returns the current date. It updates itself automatically every single day. | |
| Date & Time | NETWORKDAYS | =NETWORKDAYS(start_date, end_date, [holidays]) | Returns the number of whole working days between two dates, excluding weekends and optional holidays.Counts the number of working days between two dates, automatically skipping weekends (Saturdays & Sundays) and holidays. | |
| Financial | PMT | =PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan based on constant payments and a constant interest rate.Calculates the monthly payment for a loan (like a house mortgage or car loan) based on interest rate and loan term. | |
| Math & Arithmetic | SUM | =SUM(number1, [number2], ...) | Adds all numbers in a range of cells.Adds up all the numbers in a selected group of cells to find the total sum. | |
| Statistical & Analysis | AVERAGE | =AVERAGE(number1, [number2], ...) | Returns the average (arithmetic mean) of the arguments.Calculates the average (arithmetic mean) of a group of numbers. | |
| Statistical & Analysis | COUNT | =COUNT(value1, [value2], ...) | Counts the number of cells that contain numbers.Counts how many cells in a range contain actual numbers, completely ignoring text or blank cells. | |
| Text & Manipulation | LEFT | =LEFT(text, [num_chars]) | Returns the first character or characters in a text string, based on the number of characters you specify.Extracts a specific number of characters starting from the very beginning (left edge) of a text cell. | |
| Text & Manipulation | RIGHT | =RIGHT(text, [num_chars]) | Returns the last character or characters in a text string, based on the number of characters you specify.Extracts a specific number of characters starting from the very end (right edge) of a text cell. | |
| Text & Manipulation | TRIM | =TRIM(text) | Removes all spaces from text except for single spaces between words.Cleans up text by removing extra leading/trailing spaces and multiple spaces in between words. | |
| Text & Manipulation | PROPER | =PROPER(text) | Capitalizes the first letter in each word of a text value.Capitalizes the first letter of each word and converts all other letters to lowercase. | |
| Math & Arithmetic | ROUND | =ROUND(number, num_digits) | Rounds a number to a specified number of digits.Rounds a decimal number to a specific number of decimal places (like rounding 19.9567 to 19.96). | |
| Logical & Conditional | AND | =AND(logical1, [logical2], ...) | Returns TRUE if all of its arguments are TRUE; returns FALSE if one or more arguments are FALSE.Checks multiple rules. It only outputs TRUE if every single rule is met; otherwise, it outputs FALSE. | |
| Logical & Conditional | OR | =OR(logical1, [logical2], ...) | Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.Checks multiple rules. It outputs TRUE if at least one rule is met; it only outputs FALSE if all rules fail. | |
| Text & Manipulation | CONCAT | =CONCAT(text1, [text2], ...) | Combines the text from multiple ranges and/or strings.Glues multiple pieces of text from different cells together into a single cell, without any spacing in between. | |
| Text & Manipulation | LEN | =LEN(text) | Returns the number of characters in a text string.Counts the total number of letters, numbers, spaces, and symbols inside a text cell. | |
| Lookup & Reference | HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Looks for a value in the top row of a table or array of values and returns a value in the same column from a row you specify.Searches horizontally across the first row of a table for a query value, then retrieves matching info from a row number you choose. | |
| Information | ISBLANK | =ISBLANK(value) | Checks whether a cell is completely empty and returns TRUE or FALSE. | |
| Information | ISNUMBER | =ISNUMBER(value) | Checks whether a value is numeric and returns TRUE or FALSE. | |
| Information | ISTEXT | =ISTEXT(value) | Checks whether a value is text and returns TRUE or FALSE. | |
| Information | ISERROR | =ISERROR(value) | Checks whether a value is any Excel error and returns TRUE or FALSE. | |
| Database | DSUM | =DSUM(database, field, criteria) | Sums the numbers in a database column that match specified criteria. | |
| Database | DCOUNT | =DCOUNT(database, field, criteria) | Counts the numeric cells in a database column that match criteria. | |
| Database | DAVERAGE | =DAVERAGE(database, field, criteria) | Averages the values in a database column that match conditions. | |
| Engineering | CONVERT | =CONVERT(number, from_unit, to_unit) | Converts a number from one measurement unit to another. | |
| Engineering | DELTA | =DELTA(number1, [number2]) | Tests whether two numbers are equal (returns 1 if equal, 0 if not). | |
| Engineering | BIN2DEC | =BIN2DEC(number) | Converts a binary number to a decimal integer. | |
| Dynamic Arrays | SORT | =SORT(array, [sort_index], [sort_order], [by_col]) | Sorts the contents of a range or array dynamically. | |
| Dynamic Arrays | SORTBY | =SORTBY(array, by_array1, [sort_order1], ...) | Sorts a range based on values in a separate range or array. | |
| Dynamic Arrays | SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | Generates a list or 2D grid of sequential numbers. | |
| Dynamic Arrays | TOCOL | =TOCOL(array, [ignore], [scan_by_column]) | Transforms a 2D table or array into a single vertical column. | |
| Compatibility | RANK | =RANK(number, ref, [order]) | Returns the rank of a number in a list of numbers (Excel 2007 compatibility). | |
| Compatibility | MODE | =MODE(number1, [number2], ...) | Returns the most frequently occurring value in an array or data set. | |
| Google Sheets | ARRAYFORMULA | =ARRAYFORMULA(array_formula) | Enables array calculations across entire columns in Google Sheets. | |
| Google Sheets | QUERY | =QUERY(data, query, [headers]) | Runs Google Visualization API SQL-like queries on data tables. | |
| Google Sheets | SPARKLINE | =SPARKLINE(data, [options]) | Generates a miniature chart within a single spreadsheet cell. | |
| Financial | FV | =FV(rate, nper, pmt, [pv], [type]) | Calculates the future value of an investment with periodic payments. | |
| Financial | PV | =PV(rate, nper, pmt, [fv], [type]) | Returns the present value of a series of future cash flows. | |
| Financial | NPV | =NPV(rate, value1, [value2], ...) | Calculates the net present value of an investment using a discount rate. | |
| Math & Arithmetic | ABS | =ABS(number) | Returns the absolute (positive) value of a number. | |
| Math & Arithmetic | MOD | =MOD(number, divisor) | Returns the remainder after a number is divided by a divisor. | |
| Math & Arithmetic | POWER | =POWER(number, power) | Returns the result of a number raised to a power. | |
| Math & Arithmetic | SQRT | =SQRT(number) | Returns the positive square root of a number. | |
| Math & Arithmetic | PRODUCT | =PRODUCT(number1, [number2], ...) | Multiplies all the numbers given as arguments. | |
| Math & Arithmetic | CEILING | =CEILING(number, significance) | Rounds a number up to the nearest multiple of significance. | |
| Math & Arithmetic | FLOOR | =FLOOR(number, significance) | Rounds a number down to the nearest multiple of significance. | |
| Logical & Conditional | SWITCH | =SWITCH(expression, val1, result1, [default]) | Evaluates an expression against a list of values and returns the matching result. | |
| Logical & Conditional | NOT | =NOT(logical) | Reverses the logic of its argument (TRUE becomes FALSE, FALSE becomes TRUE). | |
| Logical & Conditional | XOR | =XOR(logical1, [logical2], ...) | Returns exclusive OR: TRUE if an odd number of arguments evaluate to TRUE. | |
| Logical & Conditional | IFNA | =IFNA(value, value_if_na) | Catches and replaces #N/A errors specifically while preserving other errors. | |
| Lookup & Reference | INDEX | =INDEX(array, row_num, [column_num]) | Returns the value of a cell at a specific row and column intersection. | |
| Lookup & Reference | MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | Returns the relative numeric position of an item in a range. | |
| Lookup & Reference | CHOOSE | =CHOOSE(index_num, value1, [value2], ...) | Returns a value from a list of arguments based on an index number. | |
| Reference & Information | OFFSET | =OFFSET(reference, rows, cols, [height], [width]) | Constructs a reference offset from a starting cell by rows and columns. | |
| Text & Manipulation | MID | =MID(text, start_num, num_chars) | Extracts a specific number of characters from text starting at any position. | |
| Text & Manipulation | UPPER | =UPPER(text) | Converts all letters in a text string to uppercase. | |
| Text & Manipulation | LOWER | =LOWER(text) | Converts all letters in a text string to lowercase. | |
| Text & Manipulation | SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Replaces occurrences of existing text with new text in a string. | |
| Text & Manipulation | REPLACE | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string with different text based on character count. | |
| Text & Manipulation | VALUE | =VALUE(text) | Converts a text string that represents a number into a numeric value. | |
| Date & Time | DATE | =DATE(year, month, day) | Creates a valid date serial number from separate Year, Month, and Day. | |
| Date & Time | DAYS | =DAYS(end_date, start_date) | Returns the number of calendar days between two dates. | |
| Date & Time | EDATE | =EDATE(start_date, months) | Returns the date a specified number of months before or after a start date. | |
| Date & Time | EOMONTH | =EOMONTH(start_date, months) | Returns the last day of the month before or after a specified number of months. | |
| Date & Time | YEAR | =YEAR(serial_number) | Extracts the 4-digit year from a date value. | |
| Date & Time | MONTH | =MONTH(serial_number) | Extracts the month (1 to 12) from a date value. | |
| Date & Time | DAY | =DAY(serial_number) | Extracts the day of the month (1 to 31) from a date value. | |
| Statistical & Analysis | MEDIAN | =MEDIAN(number1, [number2], ...) | Returns the median (middle numeric value) of a data set. | |
| Statistical & Analysis | MIN | =MIN(number1, [number2], ...) | Returns the smallest (minimum) numeric value in a set of values. | |
| Statistical & Analysis | MAX | =MAX(number1, [number2], ...) | Returns the largest (maximum) numeric value in a set of values. | |
| Statistical & Analysis | COUNTA | =COUNTA(value1, [value2], ...) | Counts the number of cells in a range that are not empty. | |
| Statistical & Analysis | COUNTBLANK | =COUNTBLANK(range) | Counts empty cells in a specified range of cells. | |
| Statistical & Analysis | LARGE | =LARGE(array, k) | Returns the k-th largest value in a data set. | |
| Statistical & Analysis | SMALL | =SMALL(array, k) | Returns the k-th smallest value in a data set. |
No matching formulas found.
Try clearing your filters or testing other query keywords.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Searches a range or array and returns the corresponding item from a second range or array.Searches for a value in one list (lookup array) and returns matching information from another list (return array) in the exact same row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Searches for a value in the first column of a table and returns a value in the same row from a specified column.Searches down the first column of a table to find a matching word, then reads across that row to get a value from a column you choose.
=INDEX(return_array, MATCH(lookup_value, lookup_array, [match_type]))Combines two formulas to perform powerful two-way lookups, searching left and right.MATCH finds the row number of your lookup item. INDEX uses that row number to grab the corresponding value from another column.
=IF(logical_test, value_if_true, [value_if_false])Checks whether a condition is met, and returns one value if TRUE, and another if FALSE.Checks whether a condition is true or false. If true, it outputs one value; if false, it outputs another.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)Checks multiple conditions and returns a value corresponding to the first TRUE condition.Checks multiple conditions in order and returns the grade/value matching the first true condition.
=IFERROR(value, value_if_error)Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.Runs a formula, but if the formula fails (like a division by zero error), it gives a clean default value instead of Excel's ugly errors.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Sums cells that meet multiple criteria.Adds up all numbers in a range that meet one or more conditions (e.g., adding sales only for West region and Apple product).
=COUNTIF(range, criteria)Counts the number of cells within a range that meet a single specified condition or criteria.Counts how many cells in a list match a rule you specify, such as counting scores over 75 or orders marked "Yes".
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Counts cells that meet multiple criteria.Counts how many cells in one or more ranges meet specific conditions.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)Combines text from multiple ranges or strings, and includes a delimiter you specify between each text value.Glues text from different cells together into one cell, placing a separator of your choice (like a comma) in between.
=FILTER(array, include, [if_empty])Filters a range or array based on conditions you define, spilling matching rows automatically.Sifts through a table of data and extracts only the rows that match your criteria, spilling the results down the sheet.
=UNIQUE(array, [by_col], [exactly_once])Extracts all unique values from a range or array, removing duplicates automatically.Looks at a list with duplicate entries and returns a clean list containing only one copy of each item.
=TODAY()Returns the current date.Returns the current date. It updates itself automatically every single day.
=NETWORKDAYS(start_date, end_date, [holidays])Returns the number of whole working days between two dates, excluding weekends and optional holidays.Counts the number of working days between two dates, automatically skipping weekends (Saturdays & Sundays) and holidays.
=PMT(rate, nper, pv, [fv], [type])Calculates the payment for a loan based on constant payments and a constant interest rate.Calculates the monthly payment for a loan (like a house mortgage or car loan) based on interest rate and loan term.
=SUM(number1, [number2], ...)Adds all numbers in a range of cells.Adds up all the numbers in a selected group of cells to find the total sum.
=AVERAGE(number1, [number2], ...)Returns the average (arithmetic mean) of the arguments.Calculates the average (arithmetic mean) of a group of numbers.
=COUNT(value1, [value2], ...)Counts the number of cells that contain numbers.Counts how many cells in a range contain actual numbers, completely ignoring text or blank cells.
=LEFT(text, [num_chars])Returns the first character or characters in a text string, based on the number of characters you specify.Extracts a specific number of characters starting from the very beginning (left edge) of a text cell.
=RIGHT(text, [num_chars])Returns the last character or characters in a text string, based on the number of characters you specify.Extracts a specific number of characters starting from the very end (right edge) of a text cell.
=TRIM(text)Removes all spaces from text except for single spaces between words.Cleans up text by removing extra leading/trailing spaces and multiple spaces in between words.
=PROPER(text)Capitalizes the first letter in each word of a text value.Capitalizes the first letter of each word and converts all other letters to lowercase.
=ROUND(number, num_digits)Rounds a number to a specified number of digits.Rounds a decimal number to a specific number of decimal places (like rounding 19.9567 to 19.96).
=AND(logical1, [logical2], ...)Returns TRUE if all of its arguments are TRUE; returns FALSE if one or more arguments are FALSE.Checks multiple rules. It only outputs TRUE if every single rule is met; otherwise, it outputs FALSE.
=OR(logical1, [logical2], ...)Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.Checks multiple rules. It outputs TRUE if at least one rule is met; it only outputs FALSE if all rules fail.
=CONCAT(text1, [text2], ...)Combines the text from multiple ranges and/or strings.Glues multiple pieces of text from different cells together into a single cell, without any spacing in between.
=LEN(text)Returns the number of characters in a text string.Counts the total number of letters, numbers, spaces, and symbols inside a text cell.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Looks for a value in the top row of a table or array of values and returns a value in the same column from a row you specify.Searches horizontally across the first row of a table for a query value, then retrieves matching info from a row number you choose.
=ISBLANK(value)Checks whether a cell is completely empty and returns TRUE or FALSE.
=ISNUMBER(value)Checks whether a value is numeric and returns TRUE or FALSE.
=ISTEXT(value)Checks whether a value is text and returns TRUE or FALSE.
=ISERROR(value)Checks whether a value is any Excel error and returns TRUE or FALSE.
=DSUM(database, field, criteria)Sums the numbers in a database column that match specified criteria.
=DCOUNT(database, field, criteria)Counts the numeric cells in a database column that match criteria.
=DAVERAGE(database, field, criteria)Averages the values in a database column that match conditions.
=CONVERT(number, from_unit, to_unit)Converts a number from one measurement unit to another.
=DELTA(number1, [number2])Tests whether two numbers are equal (returns 1 if equal, 0 if not).
=BIN2DEC(number)Converts a binary number to a decimal integer.
=SORT(array, [sort_index], [sort_order], [by_col])Sorts the contents of a range or array dynamically.
=SORTBY(array, by_array1, [sort_order1], ...)Sorts a range based on values in a separate range or array.
=SEQUENCE(rows, [columns], [start], [step])Generates a list or 2D grid of sequential numbers.
=TOCOL(array, [ignore], [scan_by_column])Transforms a 2D table or array into a single vertical column.
=RANK(number, ref, [order])Returns the rank of a number in a list of numbers (Excel 2007 compatibility).
=MODE(number1, [number2], ...)Returns the most frequently occurring value in an array or data set.
=ARRAYFORMULA(array_formula)Enables array calculations across entire columns in Google Sheets.
=QUERY(data, query, [headers])Runs Google Visualization API SQL-like queries on data tables.
=SPARKLINE(data, [options])Generates a miniature chart within a single spreadsheet cell.
=FV(rate, nper, pmt, [pv], [type])Calculates the future value of an investment with periodic payments.
=PV(rate, nper, pmt, [fv], [type])Returns the present value of a series of future cash flows.
=NPV(rate, value1, [value2], ...)Calculates the net present value of an investment using a discount rate.
=ABS(number)Returns the absolute (positive) value of a number.
=MOD(number, divisor)Returns the remainder after a number is divided by a divisor.
=POWER(number, power)Returns the result of a number raised to a power.
=SQRT(number)Returns the positive square root of a number.
=PRODUCT(number1, [number2], ...)Multiplies all the numbers given as arguments.
=CEILING(number, significance)Rounds a number up to the nearest multiple of significance.
=FLOOR(number, significance)Rounds a number down to the nearest multiple of significance.
=SWITCH(expression, val1, result1, [default])Evaluates an expression against a list of values and returns the matching result.
=NOT(logical)Reverses the logic of its argument (TRUE becomes FALSE, FALSE becomes TRUE).
=XOR(logical1, [logical2], ...)Returns exclusive OR: TRUE if an odd number of arguments evaluate to TRUE.
=IFNA(value, value_if_na)Catches and replaces #N/A errors specifically while preserving other errors.
=INDEX(array, row_num, [column_num])Returns the value of a cell at a specific row and column intersection.
=MATCH(lookup_value, lookup_array, [match_type])Returns the relative numeric position of an item in a range.
=CHOOSE(index_num, value1, [value2], ...)Returns a value from a list of arguments based on an index number.
=OFFSET(reference, rows, cols, [height], [width])Constructs a reference offset from a starting cell by rows and columns.
=MID(text, start_num, num_chars)Extracts a specific number of characters from text starting at any position.
=UPPER(text)Converts all letters in a text string to uppercase.
=LOWER(text)Converts all letters in a text string to lowercase.
=SUBSTITUTE(text, old_text, new_text, [instance_num])Replaces occurrences of existing text with new text in a string.
=REPLACE(old_text, start_num, num_chars, new_text)Replaces part of a text string with different text based on character count.
=VALUE(text)Converts a text string that represents a number into a numeric value.
=DATE(year, month, day)Creates a valid date serial number from separate Year, Month, and Day.
=DAYS(end_date, start_date)Returns the number of calendar days between two dates.
=EDATE(start_date, months)Returns the date a specified number of months before or after a start date.
=EOMONTH(start_date, months)Returns the last day of the month before or after a specified number of months.
=YEAR(serial_number)Extracts the 4-digit year from a date value.
=MONTH(serial_number)Extracts the month (1 to 12) from a date value.
=DAY(serial_number)Extracts the day of the month (1 to 31) from a date value.
=MEDIAN(number1, [number2], ...)Returns the median (middle numeric value) of a data set.
=MIN(number1, [number2], ...)Returns the smallest (minimum) numeric value in a set of values.
=MAX(number1, [number2], ...)Returns the largest (maximum) numeric value in a set of values.
=COUNTA(value1, [value2], ...)Counts the number of cells in a range that are not empty.
=COUNTBLANK(range)Counts empty cells in a specified range of cells.
=LARGE(array, k)Returns the k-th largest value in a data set.
=SMALL(array, k)Returns the k-th smallest value in a data set.