190+ Most Used Excel Functions List by Category

 

 


70 Most Popular & Common Excel Functions by Alphabetical Order

Function Name Objective
AND Checks whether all the arguments are TRUE, and returns TRUE if all arguments are TRUE.
AVERAGE Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers.
AVERAGEIF Finds average for the cells specified by a given condition or criterion.
AVERAGEIFS Returns the average of the cells of an array that satisfy one or more given criteria.
CHOOSE Chooses a value or action to perform from a list of values, based on an index number.
COLUMN Returns the column number of a cell reference.
COLUMNS Checks the number of columns in an array or reference and returns the number in digits.
CONCATENATE Joins two or more text values or numbers into one single text value.
CORREL Calculates the correlation coefficient of two cell ranges.
COUNT Counts the number of cells in a range that contains numbers.
COUNTA Counts the number of cells in a range that is not empty.
COUNTIF Counts the number of cells within a range that meets the given condition.
DATE Creates a date from numeric values in the arguments.
DATEDIF Determines how many days, months, or years there are between two dates.
DAY Returns the day of a date as a number between 1 and 31.
FILTER Filters cells or values according to requirements.
FIND Returns the starting position of a case-sensitive text string within another text string.
FLOOR Rounds a number down to the nearest multiple of significance.
FORECAST Predicts or calculates a future value with a linear trend.
FREQUENCY Returns how often numeric values occurred within the ranges you specify in a bin table of a set of data or dataset.
HLOOKUP Searches for a value in the top row of a table or array of values and returns the value in the same column from the specified row.
IF Checks whether a condition is met, and returns one value if TRUE, and another if FALSE.
IFS Takes multiple conditions and values and returns the corresponding value to the first TRUE.
INDIRECT Stores data from the reference specified by a text string.
INT Rounds a decimal number down to the lowest integer portion.
IRR Calculates the internal rate of return.
ISNA Checks whether a value is #N/A, and returns TRUE or FALSE.
LARGE Returns the K-th largest value in a dataset where K must be a positive integer.
LEFT Finds the several characters of a text beginning from the left according to the number you provide.
LEN Reflects the length of text as a number.
LINEST Finds the least-squares method to compute the statistics for a straight line and returns an array describing that line.
LOOKUP Looks up a value in the one-row or one-column range.
MATCH Returns the lookup value’s relative position.
MAX Returns the largest value in a set of values. Ignores logical values and text.
MID Returns a specific number of characters from the middle of a string, given a specific starting position.
MIN Extracts the lowest or smallest value from a range of cells or cell references.
MOD Returns the remainder after a number is divided by a divisor.
MODE Returns the most frequently occurring, or repetitive, value in an array or a range of data.
NOT Always returns a logically opposite value.
OFFSET Starts off from a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.
OR Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.
PMT Calculates the payment for a loan based on a constant interest rate.
POWER Returns a number raised to a power.
PRODUCT Calculates the multiplication among numbers in Excel.
PROPER Converts a text string into the proper case; the first letter in each word to uppercase, and all other letters to lowercase.
PV Calculates the present value of a loan or investment.
RANK Returns the position of a given number in a given list of other numbers.
RATE Calculates the rate of interest.
REPLACE Replaces a part of a text string with a different text string.
REPT Repeats text a given number of times.
RIGHT Extract a specified number of characters from a given string from right to left.
ROUND Rounds a number based on the provided number of digits.
ROUNDUP Rounds a number up, away from zero.
ROW Returns the row number for a given cell reference.
SEARCH Returns the number of characters after finding a specific character or text string, reading from left to right.
SMALL Finds the k-th (generally what we know as nth) smallest value in a data set.
SORT Sorts a given range of cells according to a specific row or column in ascending or descending order.
SUM Adds all the numbers in each range of cells.
SUMIF Adds the cells specified by a given condition or criteria.
SUMIFS Add the cells given by specified conditions or criteria.
SUMPRODUCT Takes one or more arrays as an argument, multiplies the corresponding values of all the arrays, and then returns the sum of the products.
TEXT Converts a value to text in a specific number format.
TODAY Returns the current date formatted as a date.
TRANSPOSE Converts a vertical range of cells to a horizontal range or vice versa.
TREND Calculates the values of a given set of X and Y and returns additional Y-values by using the least square method based on a new set of X-values along with a linear trend line.
TRIM Removes extra spaces from a text string.
UNIQUE Returns a list of unique values in a range or in a list.
VALUE Converts a text string that represents a number to a number.
VLOOKUP Looks for a given value in the leftmost column of a given table and then returns a value in the same row from a specified column.
WEEKDAY Returns the day of the week for a given argument.

Excel Database Functions

In Excel, database functions are a set of specialized functions that enable you to perform calculations and analysis on data stored in a database format. These functions are designed to work specifically with lists or tables of data that are structured in a consistent manner.

 

 

Function Name Objective
DCOUNT Counts the cells that contain numbers in a field (column) of records in a list or database that fit the requirements that we define.
DCOUNTA Enumerates the number of non-empty cells in a given database based on criteria.
DMIN Determines the minimum value for a field or column based on user-specified criteria.
DPRODUCT Returns the product of a defined field from a database that matches specified criteria.
DSTDEV Calculates the standard deviation of a population based on a sample of data.
DSTDEVP Determines the standard deviation of a population based on the entire population by using the number or name of the heading in a column of the dataset that matches the conditions that are mentioned.
DSUM Calculates the total sum of a specific Field by matching specific Criteria from a given Range.
DVAR Obtains sample variance for matching records.

Excel Date & Time Functions

The date and time functions are useful for tasks such as calculating durations, extracting specific components from dates or times, formatting date and time values, and more.

 

Function Name Objective
DATE Creates a date from numeric values in the arguments.
DATEDIF Returns the difference of days, months or years between two dates.
DATEVALUE Converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code.
DAY Returns the day of a date as a number between 1 and 31. This function is used for extracting a day number from a date.
DAYS Returns the number of days between two dates.
EDATE Provides a date according to the month number given in the argument.
EOMONTH Returns a string of numbers that represent the last day of the month before or after a specified number of months.
HOUR Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
MINUTE Returns the number of minutes in a given time value.
MONTH Gets the month, as a number from 1 (January) to 12 (December).
NETWORKDAYS Returns the number of whole workdays between two dates.
NETWORKDAYS.INTL Returns the number of whole workdays between 2 dates with custom weekend parameters.
NOW Returns the current date and time formatted as a date and time.
SECOND Extracts seconds from different types of time input.
TIME Returns the decimal number for an individual time.
TIMEVALUE Converts a text time to an Excel serial number for a time, a number from 0 (12:00:00 AM) to 0.999988426 (11:59:59 PM).
TODAY Returns the current date formatted as a date.
WEEKDAY Returns a number from 1 to 7 identifying the day of the week to a given date.
WEEKNUM Calculates the week number of a date.
WORKDAY Returns a date that is a working day in the future or the past.
WORKDAY.INTL Returns the serial number of the date before or after a specified number of weekdays with custom weekend parameters.
YEAR Calculates a year number from a date and it returns a 4-digit year of the corresponding date.
YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates.

Excel Engineering Functions

The engineering functions can be used to solve specific engineering problems, perform conversions between different number systems, and handle complex calculations that are common in scientific and technical fields.

 

 

Function Name Objective
COMPLEX Combines real and imaginary components together into complex values.
CONVERT Converts a number from one measurement system to another.
IMDIV Returns the result after dividing a complex number by another complex number.
IMAGINARY Extracts the imaginary coefficient from the corresponding complex number in Excel.
IMPRODUCT Returns the product of complex numbers, up to a maximum of 255 numbers.
IMSUM Returns the sum of two or multiple complex numbers in Excel.

Excel Financial Functions

Financial functions perform various calculations, such as determining loan payments, calculating interest, analyzing investment returns, and more.

 

Function Name Objective
DB Determines the depreciation cost of an asset in each period of time over its lifetime.
EFFECT Returns an effective annual interest rate based on the nominal interest rate and the number of compounding periods per year.
FV Calculates the future value of an investment.
IPMT Returns the interest payment for a given period for an investment, based on periodic constant payments and a constant interest rate.
IRR Calculates the internal rate of return.
MIRR Considers both the finance and reinvest rates to calculate the modified internal rate of return.
NOMINAL Returns the nominal annual interest rate.
NPER Calculates the total number of periods over which the loan is fixed with a constant interest rate.
NPV Calculates the net present value (NPV) of an investment using a discount rate and a series of future cash flows.
PDURATION Calculates the number of periods required for an investment to reach a designated future value at a constant interest rate.
PMT Returns the number of loan payments required as a number.
PPMT Returns principal payments for a given period.
PRICE Calculates the breakeven price per $100 face value of a bond or security.
PV Calculates the present value of a loan or investment.
RATE Calculates the rate of interest.
RRI Returns an equivalent interest rate for the growth of an investment over a certain period.
SLN Returns the straight-line depreciation of an asset for one period.
XIRR Calculates the internal rate of return (IRR) for a series of cash flows.
YIELD Calculates the yield that pays interest on purchased bonds or security on a regular basis.

Excel Information Functions

These functions are useful for conditional formatting, data validation, or creating formulas that depend on specific cell characteristics.

 

 

Function Name Objective
CELL Returns information about a cell color, file name, contents, format, row, etc.
ISBLANK Evaluates if the cell is blank.
ISERROR Indicates any error value.
ISEVEN Determines if a number is even.
ISLOGICAL Checks if a cell contains any boolean or logical values.
ISNA Checks whether a value is #N/A, and returns TRUE or FALSE.
ISNUMBER Check whether a value is a number or not.
ISODD Determines if a number is odd.
ISTEXT Checks whether a value is text or not.
N Returns a value converted to a number.
SHEETS Counts the number of sheets.
TYPE Returns an integer number that represents the type of the selected data.

Excel Logical Functions

These functions are used to perform logical comparisons, make decisions, and test conditions within formulas or logical statements.

 

 

Function Name Objective
AND Checks whether all the arguments are TRUE, and returns TRUE if all arguments are TRUE.
FALSE Returns the word FALSE to make remarks or compare with others.
IF Checks whether a condition is met, and returns one value if TRUE, and another one if FALSE.
IFERROR Generates a custom value as a result if there is any error from the formula.
IFNA Tackles the #N/A error.
IFS Takes multiple conditions and values and returns the corresponding value to the first TRUE.
NOT Returns a logically opposite value.
OR Checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.
SWITCH Compares or evaluates a given expression which is a value against a list of values and returns a result corresponding to the first match found.
TRUE Returns the logical value TRUE based on a condition.
XOR Performs an exclusive OR function.

Excel Lookup & Reference Functions

These functions allow you to search for values, match data across multiple ranges, and extract information from different parts of your spreadsheet.

 

Function Name Objective
ADDRESS Creates a cell reference as text within specified row and column numbers.
CHOOSE Chooses a value or action to perform from a list of values, based on an index number.
COLUMN Returns the column number of a cell reference.
COLUMNS Returns the column count in a given reference.
FILTER Filters cells or values according to requirements.
FORMULATEXT Returns the used formula as a string.
HLOOKUP Searches for a value in the top row of a table or array of values and returns the value in the same column from the specified row.
HYPERLINK Brings out a cutoff link that will open on a server, or links to other worksheets.
INDEX Returns a value or reference of the cell at the intersection of a particular row and column in a given range.
INDIRECT Stores data from the reference specified by a text string.
LOOKUP Looks up a value in the one-row or one-column range.
MATCH Returns the lookup value’s relative position.
OFFSET Starts off from a particular cell reference, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.
ROW Returns the row number for a given reference.
ROWS Returns the number of rows in a reference or array.
SORT Sorts a given range of cells according to a specific row or column in ascending or descending order.
TRANSPOSE Converts a vertical range of cells to a horizontal range or vice versa.
UNIQUE Returns a list or array of unique values.
VLOOKUP Looks for a given value in the leftmost column of a given table and then returns a value in the same row from a specified column.
XLOOKUP Searches a range or an array for a match and returns the corresponding item from a second range or array.

Excel Math & Trig Functions

These functions allow you to perform calculations involving numbers, angles, and geometric operations.

 

 

Function Name Objective
ABS Returns the absolute value of a number.
AGGREGATE Is used on different functions like AVERAGE, COUNT, MAX, MIN, SUM, PRODUCT, etc., with the option to ignore hidden rows and error values to get certain results.
CEILING Rounds up a number to its nearest upper integer or to the multiple of significance.
COMBIN Determines the total possible number of combinations for a given number of items without any repetition in Excel.
COS Determines the cosine of angles in Excel.
EVEN Rounds any number to a nearly even integer number.
EXP Calculates and returns the value of the constant e raised to the power of a given number.
FACT Returns the factorial of a number.
FLOOR Rounds a number down to the nearest multiple of significance.
INT Rounds a decimal value down to the lowest integer portion.
LN Calculates the natural logarithm of a number.
LOG Returns the logarithm of a number to the base you specify.
MMULT Multiplies two arrays of numbers and returns another array of numbers.
MOD Returns the remainder after a number is divided by a divisor.
ODD Rounds up or down a number to the nearest odd integer.
PI Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
POWER Returns a number raised to a power.
PRODUCT Calculates the multiplication among numbers in Excel.
QUOTIENT Returns the integer part of a division without remainder.
RAND Returns a random number between 0 and 1.
RANDBETWEEN Returns a random integer between two given numbers called bottom and top.
ROUND Rounds a number based on the provided number of digits.
ROUNDDOWN Rounds a number down toward zero.
ROUNDUP Rounds a number up away from zero.
SEQUENCE Creates a sequence of numeric values.
SIGN Returns a number’s sign as 0, +1, or -1.
SIN Determines the sine of angles in Excel.
SQRT Returns the square root of a number.
SUBTOTAL Offers 11 maths & trig functions to perform by inserting the corresponding argument.
SUM Adds all the numbers in each range of cells.
SUMIF Adds the cells specified by a given condition or criteria.
SUMIFS Adds the cells given by specified conditions or criteria.
SUMPRODUCT Takes one or more arrays as an argument, multiplies the corresponding values of all the arrays and then returns the sum of the products.
TAN Determines the tangent of an angle.
TRUNC Truncates a number to an integer by removing the decimal, or fractional, part of the number.

Excel Statistical Functions

These functions enable you to calculate various statistical measures, such as averages, standard deviations, correlations, and more.

 

 

Function Name Objective
AVERAGE Returns the average (arithmetic mean) of its arguments which can be numbers or names, arrays, or references that contain numbers.
AVERAGEA Calculates the average of a group of values – numbers, texts or boolean – and returns the average (arithmetic mean) of the values.
AVERAGEIF Finds average for the cells specified by a given condition or criteria.
AVERAGEIFS Returns the average of the cells of an array that satisfy one or more given criteria.
CORREL Calculates the correlation coefficient of two cell ranges.
COUNT Counts the number of cells in a range that contains numbers.
COUNTA Counts the number of cells in a range that is not empty.
COUNTBLANK Counts the number of empty cells in a specified range of cells.
COUNTIF Counts the number of cells within a range that meets the given condition.
COUNTIFS Counts the number of cells in one or more given arrays that maintain one or more specific criteria.
FORECAST Calculates or predicts a future value based on existing value.
FREQUENCY Returns how often numeric values occurred within the ranges you specify in a bin table of a set of data or dataset.
GAMMA Finds the gamma function value of a number.
GROWTH Calculates predictive exponential growth for a given set of data.
INTERCEPT Determines the location where a regression line will intersect the y-axis.
LARGE Returns the K-th largest value in a dataset where K must be a positive integer.
LINEST Finds the least-squares method to compute the statistics for a straight line and returns an array describing that line.
MAX Returns the largest value in a given list of arguments.
MEDIAN Returns the median of a group of numbers.
MIN Extracts the lowest or smallest value from a range of cells or cell references.
MODE Returns the most frequently occurring or repetitive value in an array or a range of data.
PERCENTILE Calculates the kth percentile of the values in a particular data range.
PERMUT Returns the number of permutations for a chosen number of objects from a total number of objects without repetitions.
QUARTILE Finds the quartile (each of four equal groups) for a conveyed set of data.
RANK Returns the position of a given number in a given list of other numbers.
SKEW Calculates the skewness of a range of data in Excel.
SLOPE Returns the slope of the linear regression line through known y’s and known x’s data points.
SMALL Finds the k-th (generally known as nth) smallest value in a data set.
STDEV Estimates standard deviation.
VAR Returns the variance of a sample taken from population data.

Excel Text Functions

These functions enable you to extract specific parts of a text, combine or concatenate multiple text strings, change the case of text, convert numbers to text, and more.

 

Function Name Objective
CHAR Returns the character specified by the code number from the character set for your computer.
CLEAN Removes all of the non-printable characters from text.
CODE Returns a numeric code for the first character in a text string, in the character set used by your computer.
CONCATENATE Joins two or more text values or numbers into one single text value.
DOLLAR Converts a number into text in the form of a currency.
EXACT Compares two texts and then returns TRUE (in case the texts are exactly the same) or FALSE (in case the texts are exactly not the same).
FIND Returns the starting position of a case-sensitive text string within another text string.
FIXED Rounds a number to the fixed number of decimals, and formats the number in decimal format using a period and commas.
LEFT Returns a specified number of characters from the start of the provided text string.
LEN Returns the length of a given string.
LOWER Converts all the letters in a text string to lowercase.
MID Returns a specific number of characters from the middle of a string, given a specific starting position.
PROPER Capitalizes the first letter of each word in a given text string.
REPLACE Replaces a part of a text string with a different text string.
REPT Repeats text a given number of times.
RIGHT Extracts a specified number of characters from a given string from right to left.
SEARCH Returns the number of characters after finding a specific character or text string, reading from left to right.
SUBSTITUTE Replaces existing text with new text in a text string.
TEXT Converts a value to text in a specific number format.
TEXTJOIN Concatenates a list or range of text strings into a single string using a delimiter.
TRIM Removes the extra spaces from a text string.
UPPER Converts a text string to all uppercase letters.
VALUE Converts a text string that represents a number to a number.

Excel Functions: Knowledge Hub


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

2 Comments
  1. Dear Ahmed Sir, Thank you for the excel demy website which is very helpful for excel.

    • Hello Jitendra Sahu,

      Thanks for your kind words. Our goal is to make Excel easy for everyone.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo