In this Excel Formulas Tutorial, we’ll share 102+ Excel formulas as a cheat sheet in a free PDF. You can download the PDF and print it to revise when needed. We did not include specialized formulas for Engineering, Statistics, Web, and other uses.
Provide a Valid Email ID below to download the PDF & Excel Files with 102 Excel Functions.
=ISBLANK(value) If a cell is blank, it returns TRUE. If a cell is not blank, it returns FALSE.
=ISERR(value) Checks whether a value is an error (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) excluding #N/A, and returns TRUE or FALSE.
=ISERROR(value) Checks whether a value is an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!), and returns TRUE or FALSE.
=ISEVEN(value) Returns TRUE if the number is even.
=ISODD(value) Returns TRUE if the number is odd.
=ISFORMULA(value) Checks whether a reference is to a cell containing a formula, and returns TRUE or FALSE.
=ISLOGICAL(value) Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE.
=ISNA(value) Checks whether a value is #N/A and returns TRUE or FALSE.
=ISNUMBER(value) Checks whether a value is a number, and returns TRUE or FALSE.
=ISREF(value) Checks whether a value is a reference, and returns TRUE or FALSE.
=ISTEXT(value) Checks whether a value is text, and returns TRUE or FALSE.
=ISNONTEXT(value) Checks whether a value is not text (blank cells are not text) and returns TRUE or FALSE.
=AVERAGEIF(range, criteria, [average_range]) Finds the average (arithmetic mean) for the cells specified by a given condition or criteria.
=SUMIF(range, criteria, [sum_range]) Adds the cells specified by a given condition or criteria.
=COUNTIF(range, criteria) Counts the number of cells within a range that meet the given condition.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) Finds the average (arithmetic mean) for the cells specified by a given set of conditions or criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) Adds the cells in the sum range if all corresponding cells in the criteria ranges fulfill their conditions.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …) Counts the number of cells in multiple ranges if each corresponding cell fulfills its criteria.
=IF(logical_test, [value_if_true], [value_if_false] Checks whether a condition is met, then returns one value if TRUE another value if FALSE.
=IFERROR(value, value_if_error) Returns the value_if_error if the expression is an error and the value of the expression itself otherwise.
=IFNA(value, value_if_na) Returns the value you specify if the expression resolves to #N/A. Otherwise, returns the result of the expression.
=SUM(number1, [number2], [number3], [number4], …) Adds all the numbers in a list or range of cells.
=AVERAGE(number1, [number2], [number3], [number4], …) Returns the average (arithmetic means) of its arguments, which can be numbers or names, arrays, or references that contain numbers.
=AVERAGEA(value1, [value2], [value3], [value4], …) Returns the average (arithmetic means) of its arguments, evaluating text and FALSE in arguments as 0; TRUE evaluates as 1. Arguments can be numbers, names, arrays, or references.
=COUNT(value1, [value2], [value3], …) Counts the number of cells in a range that contain numbers.
=COUNTA(value1, [value2], [value3], …) Counts the number of cells in a range that are not empty.
=MEDIAN(number1, [number2], [number3], …) Returns the median, or the number in the middle of the set of given numbers.
=SUMPRODUCT(array1, [array2], [array3], …) Returns the sum of the products of corresponding ranges or arrays.
=SUMSQ(number1, [number2], [number3], …) Returns the sum of the squares of the arguments. The arguments can be numbers, arrays, names, or references to cells that contain numbers.
=COUNTBLANK(range) Counts the number of empty cells in a range.
=EVEN(number) Rounds a positive number up and negative number down to the nearest even integer.
=ODD(number) Rounds a positive number up and negative number down to the nearest odd integer.
=INT(number) Rounds a number down to the nearest integer.
large function excel syntax and examples" width="793" height="444" />
=LARGE(array, k) Returns the k-th largest value in a data set. For example, k=5 returns the fifth-largest number.
=SMALL(array, k) Returns the k-th smallest value in a data set. For example, k=5 returns the fifth smallest number.
=MAX(number1, [number2], [number3], [number4], …) Returns the largest value in a set of values. Ignores logical values and text. =MAXA(value1, [value2], [value3], [value4], …) Returns the largest value in a set of values. Does not ignore logical values and text. MAXA function evaluates TRUE as 1, FALSE as 0, and any Text value as 0. Empty cells are ignored.
=MIN(number1, [number2], [number3], [number4], …) Returns the smallest number in a set of values. Ignores logical values and text. =MINA(value1, [value2], [value3], [value4], …) Returns the smallest value in a set of values. Does not ignore logical values and text. MAXA function evaluates TRUE as 1, FALSE as 0, and any Text value as 0. Empty cells are ignored.
=MOD(number, divisor) Returns the remainder after a number is divided by a divisor.
=RAND() Returns a random number greater than or equal to 0 and less than 1, using an internal pseudorandom algorithm (changes on recalculation).
=RANDBETWEEN(bottom, top) Returns a random number between the specified numbers.
=SQRT(number) Returns the square root of a number.
=SUBTOTAL(function_num, ref1, [ref2], [ref3], …) Returns a subtotal in a list or database.
=FIND(find_text, within_text, [start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive.
=SEARCH(find_text, within_text, [start_num]) Returns the number of the character at which a specific character or text string is first found, from left to right (not case-sensitive).
=SUBSTITUTE(text, old_text, new_text, [instance_num]) Replaces the old text with new text in a text string, with the optional value announcing what repetition of the old text to replace.
=REPLACE(old_text, start_num, num_chars, new_text) Replaces a part of a text string with a different text string.
=MATCH(lookup_value, lookup_array, [match_type]) Returns the relative position of an item in an array that matches a specified value in a specified order.
=LOOKUP(lookup_value, lookup_vector, [result_vector]) Looks up a value either from a one-row or one-column range or from an array. Obsolete in new versions of Excel, provided for backward compatibility.
=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 return the value in the same column from a row you specify.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Looks for a value in the leftmost column in a table, then return a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Creates a cell reference as text, given the specified row and column numbers.
=CHOOSE(index_num, value1, [value2], [value3], …) Chooses a value or action to perform from a list of values, based on an index number.
Array Form: =INDEX(array, row_num, [column_num]) Return the value of a specified cell or array of cells. 
Reference Form: =INDEX(reference, row_num, [column_num], [area_num]) Returns a reference to specified cells. 
=INDIRECT(ref_text, [a1]) Returns the reference specified by a text string.
=OFFSET(reference- rows, cols, [height], [width]) Returns a reference to a range that is a given number of rows and columns from a given reference.
=DATE(year, month, day) Returns the number that represents the date in Microsoft Excel date-time code.
=DATEVALUE(date_text) Converts a date in the form of text to a number that represents the date in the Microsoft Excel date-time code.
=TIME(hour, minute, second) Converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format.
=TIMEVALUE(time_text) Converts a text time to an Excel serial number for a time, a number from 0 (12:00:00 AM) to 0.999988424 (11:59:59 PM). Formats the number with a time format after entering the formula.
=NOW() Returns the current date and time formatted as a date and time.
=TODAY() Returns the current date formatted as a date.
YEAR(), MONTH(), DAY(), HOUR(), MINUTE() and SECOND() Functions All these functions take one the serial_number argument and return the respective time slice.
=WEEKDAY(serial_number, [return_type]) Returns a number from 1 to 7 identifying the day of the week from a date.
=DAYS(end_date, start_date) Returns the number of days between the two dates.
=NETWORKDAYS(start_date, end_date, [holidays]) Returns the number of whole workdays between two dates.
=WORKDAY(start_date, days, [holidays]) Returns the serial number of the date before or after a specified number of workdays.
=AREAS(reference) Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell.
=CHAR(number) Returns the character specified by the code number from the character set for your computer.
=CODE(text) Returns a numeric code for the first character in a text string, in the character set used by your computer.
=CLEAN(text) Removes all non-printable characters from text. Examples are Tab or New Line characters. Their codes are 9 and 10.
=TRIM(text) Removes all spaces from a text string except for single spaces between words.
=LEN(text) Returns the number of characters in a text string.
=COLUMN([reference]) Returns the column number of a reference. =ROW([reference]) Returns the row number of a reference.
=EXACT(text1, text2) Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case-sensitive.
=FORMULATEXT(reference) Returns a formula as a string.
=LEFT(text, [num_chars]) Returns the specified number of characters from the start of a text string. =MID(text, start_num, num_chars) Returns the characters from the middle of a text string, given a starting position and length. =RIGHT(text, [num_chars]) Returns the specified number of characters from the end of a text string.
=LOWER(text) Converts all letters in a text string to lowercase. =PROPER(text) Converts a text string to proper case – the first letter in each word in uppercase, and all other letters to lowercase. =UPPER(text) Converts a text string to all uppercase letters.
=REPT(text, number_times) Repeats text a given number of times.
=SHEET([value]) Returns the sheet number of the referenced sheet.
=SHEETS([reference]) Returns the number of sheets in a reference.
=TRANSPOSE(array) Converts a vertical range of cells to a horizontal range, or vice versa.
=TYPE(value) Returns an integer representing the data type of a value: number = 1, text = 2; logical value = 4, error value = 16; array = 64.
=VALUE(text) Converts a text string that represents a number to a number.
=RANK(number, ref, [order]) This function is available for compatibility with Excel 2007 and earlier. Returns the rank of a number in a list of numbers: its size relative to other values in the list.
=RANK.AVG(number, ref, [order]) Returns the rank of a number in a list of numbers. If more than one value has the same rank, the average rank is returned.
=RANK.EQ(number, ref, [order]) Returns the rank of a number in a list of numbers: its size relative to other values in the list. If more than one value has the same rank, the top rank of that set of values is returned.
=AND(logical1, [logical2], [logical3], [logical4], …) Checks whether all arguments are TRUE, and returns TRUE when all arguments are TRUE.
=NOT(logical) Changes FALSE to TRUE, or TRUE to FALSE.
=OR(logical1, [logical2], [logical3], [logical4], …) Returns FALSE only when all arguments are FALSE.
=XOR(logical1, [logical2], [logical3], …) Returns TRUE only if exactly one of the arguments is TRUE.
Provide a Valid Email below to download the PDF and Excel Files with 102 Excel Functions.
