Text
★ 55 · ◉ 32 · Excel 365
=TEXTSPLIT()
TEXTSPLIT function
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
Daily Tasks
Data Cleaning
Text
★ 55 · ◉ 30 · Excel 365
=TEXTAFTER()
Value returned if no match is found. By default, #N/A is returned.
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Data Cleaning
Text Manipulation
Text
★ 55 · ◉ 29 · Excel 365
=TEXTBEFORE()
Value returned if no match is found. By default, #N/A is returned.
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Data Cleaning
Text Manipulation
Statistical
★ 55 · ◉ 20 · Excel 2010
=RANK.EQ()
Returns the rank of a number in a list of numbers. Its size is 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. If you were to sort the list, the rank of the number would be its position.
=RANK.EQ(number,ref,[order])
Daily Tasks
Ranking & Top N
Lookup & Reference
★ 55 · ◉ 17 · Excel 365
=GROUPBY()
The GROUPBY function in Excel allows you to create a summary of your data via a formula.
=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])
Reporting
Lookup & Reference
★ 50 · ◉ 52 · All versions
=COLUMN()
Syntax: COLUMN([reference])
=COLUMN([reference])
Conditional Formatting & Validation
Numbering & Sample Data
Information
★ 50 · ◉ 52 · All versions
=ISNUMBER()
Checks whether a value is numeric or not?
=ISNUMBER(value)
Data Cleaning
Comparation
Math & Trig
★ 50 · ◉ 51 · All versions
=ABS()
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
=ABS(number)
Comparation
Rounding & Numeric
Logical
★ 50 · ◉ 51 · All versions
=AND()
Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE.
=AND(logical1, [logical2], ...)
Daily Tasks
Conditional Logic
Math & Trig
★ 50 · ◉ 48 · All versions
=TRUNC()
The TRUNC functions truncates a number to an integer by removing the fractional part of the number.
=TRUNC(number, [num_digits])
Rounding & Numeric
Text
★ 50 · ◉ 46 · All versions
=VALUE()
Converts a text string that represents a number to a number.
=VALUE(text)
Data Cleaning
Text Manipulation
Logical
★ 50 · ◉ 44 · Excel 365
=LAMBDA()
LAMBDA function
=LAMBDA([parameter1, parameter2, …,] calculation)
Advanced Formulas
Information
★ 50 · ◉ 38 · All versions
=ISERROR()
Checks whether a value is an error or not?
=ISERROR(value)
Error Handling
Logical
★ 50 · ◉ 37 · All versions
=OR()
Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE.
=OR(logical1, [logical2], ...)
Daily Tasks
Conditional Logic
Lookup & Reference
★ 50 · ◉ 37 · All versions
=TRANSPOSE()
How to use the TRANSPOSE function in Excel to switch or rotate cells.
=TRANSPOSE(array)
Reshape Tables
Logical
★ 50 · ◉ 35 · Excel 2007
=IFERROR()
How to use the IFERROR function in Excel to trap and handle errors in a formula.
=IFERROR(value, value_if_error)
Daily Tasks
Error Handling
Financial
★ 50 · ◉ 35 · All versions
=RATE()
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
=RATE(nper, pmt, pv, [fv], [type], [guess])
Financial Calculations
Loans & Installments
Text
★ 50 · ◉ 34 · All versions
=REPLACE()
REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
=REPLACE(old_text, start_num, num_chars, new_text)
Data Cleaning
Text Manipulation
Date & Time
★ 50 · ◉ 33 · All versions
=DAY()
Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
=DAY(serial_number)
Date & Time Calculations
Text
★ 50 · ◉ 33 · Excel 2019
=TEXTJOIN()
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Daily Tasks
Text Manipulation
Lookup & Reference
★ 50 · ◉ 30 · Excel 2021
=SORT()
Learn about the SORT function, which sorts the contents of a range or array. SORT is in a class of functions called dynamic arrays.
=SORT(array,[sort_index],[sort_order],[by_col])
Daily Tasks
Sort, Filter & Dedupe
Date & Time
★ 50 · ◉ 29 · All versions
=EOMONTH()
Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
=EOMONTH(start_date, months)
Daily Tasks
Date & Time Calculations
Text
★ 50 · ◉ 29 · All versions
=PROPER()
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
=PROPER(text)
Data Cleaning
Text Manipulation
Lookup & Reference
★ 50 · ◉ 28 · Excel 365
=HSTACK()
The arrays to append.
=HSTACK(array1,[array2],...)
Reshape Tables
Date & Time
★ 50 · ◉ 24 · All versions
=MONTH()
Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).
=MONTH(serial_number)
Date & Time Calculations
Statistical
★ 50 · ◉ 23 · Excel 2016
=FORECAST.LINEAR()
Predicts a future value based on the linear trend of past data
=FORECAST.LINEAR(x, known_y's, known_x's)
Forecasting & Trends
Date & Time
★ 50 · ◉ 23 · All versions
=MINUTE()
Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.
=MINUTE(serial_number)
Date & Time Calculations
Lookup & Reference
★ 50 · ◉ 22 · Excel 365
=PIVOTBY()
PIVOTBY function
=PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])
Reporting
Date & Time
★ 50 · ◉ 21 · Excel 2010
=WORKDAY.INTL()
string values are seven characters long. Each character in the string represents a day of the week, starting with Monday. The character 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Also, 1111111 is an invalid string.
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
Date & Time Calculations
Workdays & Due Dates
Text
★ 50 · ◉ 19 · Excel 2019
=CONCAT()
The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments.
=CONCAT(text1, [text2],…)
Daily Tasks
Text Manipulation
Lookup & Reference
★ 50 · ◉ 17 · Excel 365
=CHOOSECOLS()
CHOOSECOLS function
=CHOOSECOLS(array,col_num1,[col_num2],…)
Lookup & Retrieval
Reshape Tables
Date & Time
★ 50 · ◉ 7 · All versions
=YEAR()
Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.
=YEAR(serial_number)
Date & Time Calculations
Math & Trig
★ 45 · ◉ 56 · All versions
=COSH()
Returns the hyperbolic cosine of a number.
=COSH(number)
Rounding & Numeric
Date & Time
★ 45 · ◉ 42 · All versions
=EDATE()
Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
=EDATE(start_date, months)
Date & Time Calculations
Workdays & Due Dates
Math & Trig
★ 45 · ◉ 40 · All versions
=TANH()
Returns the hyperbolic tangent of a number.
=TANH(number)
Rounding & Numeric
Financial
★ 45 · ◉ 39 · All versions
=XIRR()
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
=XIRR(values, dates, [guess])
Financial Calculations
Investment Appraisal
Financial
★ 45 · ◉ 35 · All versions
=PV()
PV, one of the financial functions, calculates the present value of a loan or an investment, based on a constant interest rate. You can use PV with either periodic, constant payments (such as a mortgage or other loan), or a future value that's your investment goal.
=PV(rate, nper, pmt, [fv], [type])
Financial Calculations
Loans & Installments
Engineering
★ 45 · ◉ 32 · All versions
=DEC2OCT()
Converts a decimal number to octal.
=DEC2OCT(number, [places])
Scientific & Unit Conversion
Statistical
★ 45 · ◉ 32 · All versions
=MEDIAN()
Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
=MEDIAN(number1, [number2], ...)
Descriptive Statistics
Engineering
★ 45 · ◉ 30 · Excel 2013
=BITOR()
Returns a bitwise 'OR' of two numbers.
=BITOR(number1, number2)
Scientific & Unit Conversion
Lookup & Reference
★ 45 · ◉ 29 · All versions
=HYPERLINK()
The HYPERLINK function creates a shortcut that jumps to another location in the current workbook, or opens a document stored on a network server, an intranet, or the Internet. When you click a cell that contains a HYPERLINK function, Excel jumps to the location listed, or opens the document you specified.
=HYPERLINK(link_location, [friendly_name])
Dynamic Ranges & Dashboards
Worksheet Info & Navigation
Math & Trig
★ 45 · ◉ 29 · All versions
=MDETERM()
Returns the matrix determinant of an array.
=MDETERM(array)
Rounding & Numeric
Date & Time
★ 45 · ◉ 29 · All versions
=WORKDAY()
Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.
=WORKDAY(start_date, days, [holidays])
Date & Time Calculations
Workdays & Due Dates
Engineering
★ 45 · ◉ 27 · All versions
=IMCOT()
Returns the cotangent of a complex number in x+yi or x+yj text format.
=IMCOT(inumber)
Scientific & Unit Conversion
Lookup & Reference
★ 45 · ◉ 26 · Excel 365
=TAKE()
TAKE function
=TAKE(array, rows,[columns])
Sort, Filter & Dedupe
Ranking & Top N
Engineering
★ 45 · ◉ 25 · Excel 2013
=BITLSHIFT()
Returns a number shifted left by the specified number of bits.
=BITLSHIFT(number, shift_amount)
Scientific & Unit Conversion
Lookup & Reference
★ 45 · ◉ 25 · All versions
=GETPIVOTDATA()
The GETPIVOTDATA function returns visible data from a PivotTable.
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
Lookup & Retrieval
Reporting
Statistical
★ 45 · ◉ 24 · All versions
=CORREL()
The CORREL function returns the correlation coefficient of two cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.
=CORREL(array1, array2)
Descriptive Statistics
Forecasting & Trends
Financial
★ 45 · ◉ 24 · All versions
=IPMT()
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
=IPMT(rate, per, nper, pv, [fv], [type])
Financial Calculations
Loans & Installments
Financial
★ 45 · ◉ 24 · All versions
=NPV()
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
=NPV(rate,value1,[value2],...)
Financial Calculations
Investment Appraisal
Engineering
★ 45 · ◉ 23 · All versions
=BIN2HEX()
Converts a binary number to hexadecimal.
=BIN2HEX(number, [places])
Scientific & Unit Conversion
Financial
★ 45 · ◉ 23 · All versions
=NPER()
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
=NPER(rate,pmt,pv,[fv],[type])
Financial Calculations
Loans & Installments
Financial
★ 45 · ◉ 22 · All versions
=FV()
FV, one of the financial functions, calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment.
=FV(rate,nper,pmt,[pv],[type])
Financial Calculations
Loans & Installments
Engineering
★ 45 · ◉ 22 · All versions
=IMSIN()
Returns the sine of a complex number in x + yi or x + yj text format.
=IMSIN(inumber)
Scientific & Unit Conversion
Financial
★ 45 · ◉ 21 · All versions
=PPMT()
Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
=PPMT(rate, per, nper, pv, [fv], [type])
Financial Calculations
Loans & Installments
Lookup & Reference
★ 45 · ◉ 20 · Excel 365
=TOCOL()
Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.
=TOCOL(array, [ignore], [scan_by_column])
Data Cleaning
Reshape Tables
Financial
★ 45 · ◉ 19 · All versions
=IRR()
A number that you guess is close to the result of IRR.
=IRR(values, [guess])
Financial Calculations
Investment Appraisal
Text
★ 45 · ◉ 18 · All versions
=LENB()
Returns the length of a text in bytes — each double-byte character counts as 2
=LENB(text)
Text Manipulation
Engineering
★ 45 · ◉ 15 · All versions
=OCT2HEX()
Converts an octal number to hexadecimal.
=OCT2HEX(number, [places])
Scientific & Unit Conversion
Math & Trig
★ 44 · ◉ 45 · All versions
=SINH()
Returns the hyperbolic sine of a number.
=SINH(number)
Rounding & Numeric