How to use the SUM function in Excel to add individual values, cell references, ranges, or a mix of all three.
Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.
How to use the IF function in Excel to make logical comparisons between data values.
Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant. Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in.
Returns the largest value in a set of values.
Returns the smallest number in a set of values.
Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.
How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.
How to use the FILTER function in Excel to filter a range of data based on criteria you define.
The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.
The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: =ROUND(A1, 2) The result of this function is 23.78.
Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
How to use the SUMPRODUCT function in Excel, one of Excel’s math and trig functions, using multiplication, addition, subtraction, and/or division to return the sum of the products of corresponding ranges or arrays.
Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set.
How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range.
How to use the COUNT function in Excel to count the number of cells that contain numbers, and to count numbers within a list of arguments.
Use Excel's DATE function when you need to take three separate values and combine them to form a date.
The IFNA function returns the value you specify if a formula returns the #N/A error value; otherwise it returns the result of the formula.
How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS will allow you to include up to 127 pairs of conditions (the test if something is True and the result if something is true). IFS can take the place of multiple nested IF statements.
The COUNTA function counts the number of cells that are not empty in a range.
Calculates the number of days, months, or years between two dates.
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
The XMATCH function searches for a specified item in an array or range of cells, and then returns the relative position of the first match.
RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
LEN returns the number of characters in a text string.
Rounds a number up, away from 0 (zero).
Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
The arrays to append.
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.
LET function
Learn how to concatenate text strings in Excel using the CONCATENATE function. Our step-by-step guide makes it easy to join two or more text strings into one.
Conditional count (a single condition)
How to use the PMT function in Excel to calculate monthly loan payments based on constant payments and a constant interest rate.
Learn about the SORTBY function, which sorts the contents of a range or array based on the values in a corresponding range or array. SORTBY is in a class of functions called dynamic arrays.
Returns the number of days between two dates.
Whether a formula is an error or not
Finds the position of a text inside another text (case-sensitive)
Checks whether a cell is empty or not?
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
The SEARCH function locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. For example, to find the position of the letter "n" in the word "printer", you can use the following function: =SEARCH("n","printer") This function returns 4 because "n" is the fourth character in the word "printer." You can also search for words within other words. For example, the function =SEARCH("base","database") returns 5, because the word "base" begins at the fifth character of the word "database". You can use the SEARCH to determine the location of a character or text string within another text string, and then use the MID to return the text, or use the REPLACE to change the text. These functions are demonstrated in Example 1 in this article.
How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.
Whether it is the #N/A error or not
Returns the average (arithmetic mean) of all cells that meet multiple criteria.
Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.) For more information about the new functions, see RANK.AVG function and RANK.EQ function.
Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Learn more about the SEQUENCE function, which allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. SEQUENCE is in a class of functions called dynamic arrays.
Estimates standard deviation based on a sample (ignores logical values and text in the sample).
TEXTSPLIT function
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. If there are no matching values, and no default argument is supplied, the SWITCH function returns the #N/A! error.
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Value returned if no match is found. By default, #N/A is returned.
Rounds a number down, toward zero.
Rounds a number down to the nearest integer.