101 Most Popular Excel Formulas: Pro Version

Last Updated on: 8th October 2023, 10:22 am

Microsoft Excel is an essential software that professionals across various fields use to accomplish various tasks ranging from simple data entry to complex computations and analysis.

One of the powerful and advantageous features of Excel is its capability to execute formulas that perform calculations, manipulate data, or even automate and control the spreadsheet itself.

This feature is fundamental to mastering Excel. Let’s delve into the 101 most popular Excel formulas, their usage, and other applications.

For the purpose of our discussion, we will group these formulas into different categories: mathematical, logical, text, date and time, lookup and reference, and financial formulas.

Mathematical Formulas:

• SUM: This formula is one of the most common Excel functions and is used to add the value of cells in the argument. Usage is =SUM(A1:A5), adding up the values in cells A1 through A5.
• AVERAGE: This formula calculates the average of the selected range of cells. For example, =AVERAGE(A1:A5) gives the average of values in cells A1 to A5.
• MAX/MIN: The max and min functions help to find the highest and lowest values within a range respectively. For instance, = MAX(A1:A5) would return the highest value in cells A1 through A5.

Logical Formulas:

• IF: The IF formula checks whether a condition is met and returns a value if true and another value if false. =IF(A1>5, "Greater", "Lesser") would return “Greater” if A1 is more than 5 and “Lesser” otherwise.
• AND/OR: These two functions check for multiple conditions and return true or false respectively. =AND(A1>5, A2<10) would return true only if both conditions are met.

Text Formulas:

• CONCATENATE: This function merges two or more strings into one. For example, =CONCATENATE(A1, " ", A2) would join the contents of cells A1 and A2 with a space between them.
• LEFT/RIGHT: These formulas extract a specific number of characters from the left or right of a text string respectively. =LEFT(A1, 5) extracts the first five characters from the text in A1.

Date and Time Formulas:

• NOW: This function returns the current date and time. Usage is simple, =NOW().
• YEAR/MONTH/DAY: These functions extract the year, month, or day from a date respectively. For example, =YEAR(A1) retrieves the year from the date in cell A1.

Lookup and Reference Formulas:

• VLOOKUP: This function searches for a value in the first column of a range and returns a value in the same row from a column you specify. For example, =VLOOKUP(A2, B2:C10, 2, FALSE) would return the value from column 2 where column 1 matches A2.
• INDEX/MATCH: These combined functions find and return a value in a table based on the row and column number. The MATCH function returns the position of a value in a range, and the INDEX function uses that position to retrieve the value.

Financial Formulas:

• PV: The PV formula calculates the present value of a loan or an investment. It takes into account an interest rate and a specific number of payment periods.
• FV: The FV function calculates the future value of an investment. It also accepts an interest rate and the number of periods.

Basic Statistic Formulas:

• COUNT: This function counts the number of cells that contain numbers. For example, =COUNT(A1:A5) counts the cells with numbers between A1 and A5.
• COUNTA: This formula counts the number of cells that are not empty in the range, regardless of their content. For instance, =COUNTA(A1:A5) counts cells from A1 to A5 that are not empty.
• COUNTBLANK: This function counts the number of empty cells in a range. For instance, =COUNTBLANK(A1:A5) will count how many of the cells from A1 to A5 are empty.

Array Formulas:

• SUMPRODUCT: This function multiplies ranges or arrays together and returns the sum of products. For example, =SUMPRODUCT(A1:A3, B1:B3) multiplies each pair of values in the two ranges together, and then sums those products.
• FREQUENCY: This function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For instance, =FREQUENCY(A1:A5, B1:B5) returns the frequency of values in A1:A5 that fall within the ranges set out in B1:B5.

Formula Error Checking:

• ISERROR: This function checks whether a cell contains any error like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. If there’s an error, it returns TRUE otherwise FALSE. For instance, =ISERROR(A1) checks if there’s an error in cell A1.
• ISNA: This function specifically checks for the #N/A error. Example; =ISNA(VLOOKUP(A1,B2:C7, 3, FALSE)) will return TRUE if the VLOOKUP brings up an #N/A error.

Rounding Formulas:

• ROUND: This function rounds a number to a specified number of digits. For example, =ROUND(A1, 2) rounds the number in cell A1 to two decimal places.
• MROUND: This function rounds a number up or down to the nearest specified multiple. For example, =MROUND(A1, 5) rounds the number in cell A1 to the nearest multiple of 5.

Information Formulas:

• CELL: This function returns information about the formatting, location, or contents of a cell. For example, =CELL("width", A1) gives the column width of cell A1.
• ISNUMBER: This formula checks if a cell contains a number and returns TRUE if it does and FALSE otherwise. For example, =ISNUMBER(A1) checks if cell A1 contains a number.

Other Useful Formulas:

• RAND: This function returns a random number between 0 and 1. To use, simply type =RAND().
• RANK: This function gives the order rank of a number in a list of numbers. For example, =RANK(A1, A2:A5) would give the rank of the value in A1 in the list of values in A2 to A5.

Lookup Formulas:

• HLOOKUP: This function searches for a value in the top row of a table array and returns a value in the same column from a row you specify. For example, =HLOOKUP(A1, A2:D5, 3, FALSE) finds the value in A1 in the top row of A2:D5 and returns the value in the same column from the third row.
• INDEX: This formula returns the value of a cell in a specific location within an array or range. For instance, =INDEX(A1:D5, 3, 2) provides the value in the third row and second column of the range A1:D5.

Data Manipulation Formulas:

• SUBSTITUTE: This function replaces text in a given string, by replacing old_text with new_text. For example, =SUBSTITUTE(A1, "old", "new") replaces “old” with “new” in cell A1.
• TRIM: This function removes extra spaces from text, leaving single spaces between words and no space characters at the start or end. Usage example is =TRIM(A1).

Date Formulas:

• DATEDIF: This function returns the difference between two date values, based on the interval specified. For example, =DATEDIF(A1,A2,"y") calculates the number of complete years between the dates in cells A1 and A2.
• EDATE: This function returns the serial number that represents the date that is a specified number of months before or after a specified date. For example, =EDATE(A1, 5) adds 5 months to the date in cell A1.

Financial Formulas:

• PMT: This function calculates the monthly payment for a loan with constant payments and a constant interest rate. For example, =PMT(A1/12, A2*12, A3) computes the monthly payment for a loan with the interest rate in A1, term in years in A2, and the initial loan amount in A3.
• NPER: This function returns the total number of payment periods for an investment or a loan. For example, =NPER(A1/12,A2, A3) computes the total number of periods given a rate in A1, payment in A2, and present value in A3.

Statistical Formulas:

• STDEVP: This function calculates the standard deviation of a population. For example, =STDEVP(A1:A10) gives the standard deviation of all values in cells A1 through A10.
• VAR: This function calculates variance based on a sample. =VAR(A1:A10) gives the variance of the values in cells A1 through A10.
• PEARSON: It calculates the Pearson product-moment correlation coefficient between two data sets. For example, =PEARSON(A1:A3, B1:B3) calculates the coefficient for the specified ranges.

Logical Formulas:

• OR: This function checks multiple conditions and returns TRUE if at least one condition is met, and FALSE if none are met. =OR(A1>1, A2<5) will return true if either A1 is greater than 1, or A2 is less than 5.
• NOT: This function changes FALSE to TRUE, or TRUE to FALSE. For example, =NOT(A1=A2) check if A1 and A2 are different.
• IFERROR: This function returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. =IFERROR(A1/B1, "Error") would return “Error” if division by zero occurs.

Lookup Formulas:

• ADDRESS: This function creates a cell reference as text, given specified row and column numbers. For example, =ADDRESS(5,3) returns ‘$C$5’ as the cell reference.
• MATCH: This function searches for a specific value in a range of cells and returns the relative position of the item from the range. For example, =MATCH(5,A1:A10,0) returns the position of number 5 in the range A1:A10.

Information Formulas:

• ISCELL: This function checks if a value is a reference to a cell and returns TRUE if it is. For example, =ISCELL(A1) checks if cell A1 reference exists.
• ISNA: This function checks if a value is #N/A and returns TRUE if so. For example, =ISNA(VLOOKUP(A1,B2:C7,2,FALSE)) returns true if the Vlookup function returns #N/A.
• ISTEXT: This function checks if a value is text and returns TRUE if so. For example, =ISTEXT(A1) checks if A1 cell contains text.

Power Formulas:

• POWER: This function returns the result of a number raised to a power. For example, =POWER(A1, 2) will sqare the value in cell A1.
• SQRT: The SQRT formula in Excel returns the square root of a number. For example, =SQRT(A1) will give the square root of the value in cell A1.

Financial Formulas:

• NPV: Net Present Value, calculated using =NPV(rate,value1,[value2],...). For instance, =NPV(A1, B1:B3) calculates the NPV of a series of cash flows in cells B1:B3 discounted at the rate in A1.
• IRR: The Internal Rate of Return, calculated using =IRR(values,[guess]). For example, =IRR(B1:B3) calculates the IRR for the series of cash flows in cells B1:B3.

Trigonometric Formulas:

• SIN/COS: These functions return the sine and cosine of an angle. For example, =SIN(A1) returns the sine of the angle in cell A1 (the angle should be in radians), and =COS(A1) does the same but for the cosine.
• TAN/ATAN: Similarly, =TAN(A1) provides the tangent of the angle in cell A1, while =ATAN(A1) gives the arctangent (the angle, in radians, whose tangent is given in cell A1).

Advanced Statistical Formulas:

• FORECAST.LINEAR: This one predicts a future value along a linear trend line fitted to a supplied set of x- and y- values. For instance, =FORECAST.LINEAR(A1, B1:B3, C1:C3) predicts a y-value for x=A1 from the linear trend of y-values supplied in B1:B3 and corresponding x-values in C1:C3.
• CORREL: Calculates the correlation coefficient between two data sets. For example, =CORREL(A1:A3, B1:B3) returns the correlation coefficient of A1:A3 and B1:B3.

Conversion Formulas:

• DECIMAL: The =DECIMAL(text, radix) function converts a text representation of a number in a given base into a decimal number. Like =DECIMAL("FF", 16) converts hex FF to decimal (255).
• BIN2DEC: Converts a binary number to decimal. For example, =BIN2DEC("1101") would return 13.

Error Checking Formulas:

• ISERR: Returns TRUE if a value is any error value except #N/A, otherwise returns FALSE. For example, =ISERR(A1) checks if the value in cell A1 is an error.
• ERROR.TYPE: Returns a number corresponding to the error value in a cell. For example, =ERROR.TYPE(A1) returns a number representing the error type in cell A1.

Compatibility Formulas:

• DATEDIF: Calculates the difference between two dates based on the interval specified. For example, =DATEDIF(A1,A2,"m") will give the number of complete months between the dates in cell A1 and A2.
• SUMIF: Sum the values in a range that meet criteria you specify. It’s used as =SUMIF(range, criteria, [sum_range]). For instance, =SUMIF(A1:A10,">20") sums all numbers in the range A1:A10 that are greater than 20.

Database Formulas:

• DAVERAGE: Averages selected database entries based on given criteria. For example, =DAVERAGE(A1:C10,"Sales",A12:C13) will average the ‘Sales’ column cells where criteria set in A12:C13 is met.
• DCOUNT: Counts the cells with numerical data in the database where the criteria set are met. For example =DCOUNT(A1:C10,"Age",A12:C13) counts the cells in the ‘Age’ column where the set criteria in A12:C13 are met.

Information Formulas:

• CELL: Returns information about formatting, location, or contents of a cell. For example, =CELL("format", A1) returns the number format of cell A1.
• INFO: Returns information about the current operating environment. For example, =INFO("system") returns the name of the operating system on the computer on which Excel is installed.

Text Manipulation Formulas:

• MID: Returns a specific number of characters from a text string starting at a specified position. For example, =MID(A1,1,3) extracts the first 3 characters from the text in cell A1.
• REPLACE: Replaces characters within text, at a specified location and length. For example, =REPLACE(A1,2,3,"New Text") will replace 3 characters from the second position (count starts from 1) in the cell A1 with the string “New Text”.
• LEN: Returns the number of characters in a text string. For example, =LEN(A1) gives the count of characters in cell A1.

Math & Trig Formulas:

• MOD: Returns the remainder from division. For example, =MOD(A2, 3) returns the remainder of the number in cell A2 divided by 3.
• ABS: Returns the absolute value of a number. For instance, =ABS(A1) provides the absolute value of the value in cell A1.

Conditional Aggregate Formulas:

• SUMIF: This function adds values in a range based on supplied criteria. For instance =SUMIF(A1:A5,">5") would sum only the values that are greater than five in the range A1:A5.
• COUNTIF: This is similar to SUMIF, but instead of adding, it counts the number of instances that meet the criteria. For example =COUNTIF(A1:A5,"=yes") will count the cells that contain “yes” in the range A1:A5.

Nested Logical Formulas:

• IF with AND: You can nest the AND function within an IF function to test more than one condition. For example =IF(AND(B2>3,D2<5),"Yes","No") will return “Yes” if B2 is greater than 3 AND D2 is less than 5, and “No” otherwise.
• IF with OR: Similarly, you can nest the OR function within an IF function to fulfill at least one of the conditions. For instance =IF(OR(B2>3,D2<5),"Yes","No") returns “Yes” if either B2 is greater than 3 OR D2 is less than 5, and “No” otherwise.

Text Transformation Formulas:

• LOWER: Converts all uppercase letters in a text string to lowercase. For example, =LOWER(A1) will convert all the text in cell A1 to lowercase.
• UPPER: Converts a text string to all uppercase. For example =UPPER(A1) will convert all the text in cell A1 to uppercase.

Financial Formulas:

• RATE: Returns the interest rate per period of an annuity. For instance =RATE(nper, pmt, pv, [fv], [type], [guess]).
• NPER: Returns the number of periods for an investment or loan. For example =NPER(rate, pmt, pv, [fv], [type]).

Date & Time Formulas:

• NETWORKDAYS: Returns the number of whole workdays between two dates. For instance =NETWORKDAYS(start_date, end_date, [holidays]).
• WORKDAY: Returns the date that is a specified number of workdays ahead or back from a start date. For example =WORKDAY(start_date, days, [holidays]).

Engineering Formulas:

• BIN2HEX: This function converts a binary number into a hexadecimal number, =BIN2HEX(A1).
• HEX2BIN: Converts a hexadecimal number to binary, =HEX2BIN(A1).

Math and Trigonometrics Formulas:

• ASIN/ACOS: ASIN returns the arcsine, or inverse sine, of a number, while ACOS returns the arccosine or inverse cosine of a number. For instance, =ASIN(A1) and =ACOS(A1).
• FACT: Returns the factorial of a number. =FACT(A1) will give the factorial of the number in cell A1.

Statistical Formulas:

• AVEDEV: Calculates the average of the absolute deviations of data points from their mean, =AVEDEV(number1, [number2], ...).
• STDEVP: Estimates standard deviation based on an entire population. =STDEVP(number1, [number2], ...).

Text Formulas:

• REPT: Repeats text a given number of times. Use =REPT(text, number_times).
• TEXT: Converts a numeric value into text and allows you to specify the display formatting by using special format strings. For example, =TEXT(A1, "0.0").

More Lookup and Reference Formulas:

• INDEX: This formula returns the value of a cell in a specific location within an array or range. For instance, =INDEX(A1:D10, 5,3) yields the value at the fifth row and third column in range A1:D10.
  • INDIRECT: Converts a text string into a cell reference. =INDIRECT(A1) will return the value in the cell referred by the text in A1.

Compatibility Formulas:

• BETADIST: This function calculates the beta distribution probability for a given standard deviation. Use =BETADIST(x, alpha, beta, [A], [B]).
• CHITEST: This function calculates the test for independence. For example =CHITEST(A1:A5, B1:B5) calculates the test for independence comparing the actual values in A1:A5 with expected values in B1:B5.

Database Formulas:

• DSUM: This function sums the selected database entries based on the specified criteria. =DSUM(database, field, criteria)
• DGET: This function extracts from a database a single record that matches the specified criteria. =DGET(database, field, criteria)

Engineering Formulas:

• DELTA: This function checks if two values are equal. For example, =DELTA(A1, B1) will return 1 if A1=B1, else 0.
• CONVERT: This function is used to convert numbers from one measurement system to another. For example, =CONVERT(A1, "km", "mi") converts the number in cell A1 from kilometers to miles.

Cube Formulas:

• CUBEVALUE: Returns an aggregated value from a cube. For example, =CUBEVALUE("connection", member_expression1, [member_expression2], ...).
• CUBEMEMBER: Returns a member or tuple from the cube. For example, =CUBEMEMBER("connection", member_expression, ).

Information Formulas:

• TYPE: Returns a number representing the data type of a value. For instance, =TYPE(A1) returns a number corresponding to the data type of the value in cell A1.
• ISLOGICAL: Returns TRUE if the value is a logical value; otherwise, it returns FALSE. For example, =ISLOGICAL(A1) checks if the value in cell A1 is a logical value.

Math and Trigonometric Formulas:

• SIGN: The function returns the sign of a number. For instance, =SIGN(A1) returns -1 if the number is negative, 0 if the number is zero, and 1 if the number is positive.
• MROUND: This function rounds a number up or down to the desired multiple. =MROUND(A1, 5) rounds the number in cell A1 to the nearest multiple of 5.

Statistical Formulas:

• PERCENTILE: This function Returns the kth percentile of values in a range. For example, =PERCENTILE(A1:A10, 0.3) returns the 30th percentile from the set of values in cells A1 through A10.
• NORMDIST/NORMSDIST: These functions return the normal distribution for the specified mean and standard deviation. For example, =NORMDIST(A1,0,1,FALSE) returns the normal distribution for the specified mean=0 and standard deviation=1.

Text Formulas:

• VALUE: Converts a text string that represents a number to a number. For instance, =VALUE("123") converts the text “123” to a numeric value of 123.
• CODE: Returns a numeric code for the first character in a text string. For example, =CODE("A") returns 65, the ASCII code for “A”.

Math and Trigonometric Formulas:

• RADIANS: Converts degrees to radians. For example, =RADIANS(A1) converts the degree value in cell A1 to radians.
• ACOTH: Returns the inverse hyperbolic cotangent of a number, in radians. For example, =ACOTH(A1) returns the hyperbolic arccotangent of the number in cell A1.

Date and Time Formulas:

• TODAY: Returns the current date formatted as a date. The function has no arguments and is entered as =TODAY().
• EOMONTH: Returns the serial number of the last day of the month before or after a specified number of months. For example, =EOMONTH(A1, 1) would return the last day of the month, 1 month after the date in cell A1.

Lookup and Reference Formulas:

• XLOOKUP: Searches a range or an array, and returns an item corresponding to the first match it finds. For example, =XLOOKUP(A2,A2:A9,B2:B9) looks for A2 in the range A2:A9 and returns the corresponding value from B2:B9.
• XMATCH: Returns the relative position of an item in an array or range of cells. For example, =XMATCH(A1,A2:A5) returns the position of A1 in the range A2:A5.