# A List of 101 Common Excel Functions

Whether you’re a financial analyst, data scientist, teacher, or small business owner, Excel’s extensive library of functions can help you perform complex calculations, analyze data, and automate tasks. In this article, we will explore a list of 101 common Excel functions that can greatly enhance your productivity and proficiency in using Excel.

### What is a Function in Excel?

In Excel, a function is a predefined formula that performs a specific calculation or operation on data in a worksheet.

Functions are built-in tools that allow you to perform various tasks, such as mathematical calculations, text manipulation, date and time calculations, and more, without the need to write complex formulas from scratch.

Excel functions typically consist of the function name followed by parentheses “()” that may contain one or more arguments (input values) separated by commas. These arguments are used by the function to perform its operation and return a result.

### A Few Examples Of Common Functions in Excel

Excel provides hundreds of built-in functions to handle various types of calculations and tasks. Here are a few of them:

1. SUM: Adds up a range of numbers.

Example: =SUM(A1:A5) will add the values in cells A1 through A5.

2. AVERAGE: Calculates the average of a range of numbers.

Example: =AVERAGE(B1:B10) will find the average of the values in cells B1 through B10.

3. IF: Performs a conditional test and returns one value if the condition is true and another value if it’s false.

Example: =IF(C1>10, “Yes”, “No”) will return “Yes” if the value in cell C1 is greater than 10, otherwise, it will return “No.”

4. CONCATENATE (or CONCAT): Combines text from multiple cells into one cell.

Example: =CONCATENATE(A1, ” “, B1) will combine the text in cells A1 and B1 separated by a space.

5. TODAY: Returns the current date.

Example: =TODAY() will display the current date in the cell.

## Types Of Functions In Excel

Excel offers a wide range of functions that can be categorized into several types based on their primary purposes. Here are some of the main types of functions in Excel:

• Math and Trigonometry Functions
• Text Functions
• Date and Time Functions
• Logical Functions
• Lookup and Reference Functions
• Statistical Functions
• Financial Functions
• Information Functions
• Database Functions
• Engineering Functions

A. Math and Trigonometry Functions

These functions can be extremely useful for a wide range of calculations and data analysis tasks in Excel.

Function NameDescription
SUM FunctionAdds up a range of numbers.
SUMIF FunctionAdds all the numbers in a range that meets a single condition.
SUMIFS FunctionAdds all the numbers in a range that meets multiple conditions.
SUMPRODUCT FunctionMultiplies corresponding elements in arrays and returns the sum.
ABS FunctionReturns the absolute value of a number.
BASE FunctionConverts a number from one base to another.
CEILING FunctionRounds a number up to the nearest integer or to the nearest multiple of significance.
COS FunctionCalculates the cosine of an angle.
FLOOR FunctionRounds a number down to the nearest integer or to the nearest multiple of significance.
INT FunctionRounds a number down to the nearest integer.
MOD FunctionReturns the remainder after division.
MROUND FunctionRounds a number to the nearest multiple.
RAND FunctionGenerates a random number between 0 and 1.
RANDBETWEEN FunctionGenerates a random number between specified minimum and maximum values.
ROUND FunctionRounds a number to a specified number of decimal places.
ROUNDDOWN FunctionRounds a number down to a specified number of decimal places.
ROUNDUP FunctionRounds a number up to a specified number of decimal places.
SIN FunctionCalculates the sine of an angle.
SQRT FunctionCalculates the square root of a number.
SUBTOTAL FunctionReturns a subtotal in a list or database.
TRUNC FunctionTruncates a number to an integer by removing the fractional part.

### B. Text Functions

These functions are essential for manipulating and cleaning text data within Excel, making them valuable tools for data analysis and reporting.

Function NameDescription
CHAR FunctionReturns the character specified by a number.
CLEAN FunctionRemoves non-printable characters from text.
CODE FunctionReturns the numeric Unicode value of the first character in a text string.
CONCAT FunctionCombines two or more text strings into one.
CONCATENATE FunctionCombines two or more text strings into one.
EXACT FunctionTests if two text strings are exactly the same.
FIND FunctionSearches for a text string within another text string and returns its starting position.
LEFT FunctionExtracts a specified number of characters from the beginning of a text string.
LEN FunctionReturns the number of characters in a text string.
MID FunctionExtracts characters from the middle of a text string.
PROPER FunctionCapitalizes the first letter of each word in a text string.
REPLACE FunctionReplaces part of a text string with another text string.
RIGHT FunctionExtracts a specified number of characters from the end of a text string.
SEARCH FunctionFinds one text string within another text string (case-insensitive) and returns its starting position.
SUBSTITUTE FunctionReplaces occurrences of a specified text within a text string.
TEXT FunctionConverts a value to text in a specified format.
TEXTJOIN FunctionConcatenates text from multiple cells with specified delimiters.
TRIM FunctionRemoves extra spaces from a text string.
UPPER FunctionConverts text to uppercase.
VALUE FunctionConverts a text string that represents a number to a number.

### C. Date and Time Functions

These date and time functions in Excel are invaluable for working with date-related data, performing calculations, and managing schedules and timelines effectively.

Function NameDescription
DATE FunctionCreates a date based on year, month, and day.
DATEDIF FunctionCalculates the difference between two dates in various units (e.g., days, months, years).
DAY FunctionExtracts the day from a date.
DAYS FunctionCalculates the number of days between two dates.
HOUR FunctionExtracts the hour from a time.
MINUTE FunctionExtracts the minute from a time.
MONTH FunctionExtracts the month from a date.
NETWORKDAYS FunctionCalculates the number of working days between two dates, excluding weekends and specified holidays.
NOW FunctionReturns the current date and time.
SECOND FunctionExtracts the second from a time.
TIME FunctionCreates a time based on hours, minutes, and seconds.
TODAY FunctionReturns the current date.
WORKDAY FunctionCalculates the date of the nth working day after a given date, excluding weekends and specified holidays.
YEAR FunctionExtracts the year from a date.

### D. Statistical Functions

These functions are essential for performing various types of counting, averaging, and statistical calculations in Excel. They are commonly used in data analysis, reporting, and decision-making processes.

Function NameDescription
AVERAGE FunctionCalculates the average of a range of numbers.
AVERAGEIFS FunctionCalculates the average of cells that meet multiple criteria.
COUNT FunctionCounts the number of cells that contain numbers.
COUNTA FunctionCounts the number of cells that are not empty.
COUNTBLANK FunctionCounts the number of empty cells in a range.
COUNTIF FunctionCounts cells that meet a specific criterion.
COUNTIFS FunctionCounts cells that meet multiple criteria.
MAX FunctionReturns the highest value in a range.
MIN FunctionReturns the lowest value in a range.
SMALL FunctionReturns the k-th smallest value in a dataset.

### E. Information Functions

These functions are useful for inspecting cell contents, checking for errors, and determining data types, which can be important in data analysis and troubleshooting tasks in Excel.

Function NameDescription
CELL FunctionReturns information about a cell, such as its format.
ISBLANK FunctionChecks if a cell is empty.
ISERROR FunctionChecks if a cell contains an error.
ISNA FunctionChecks if a cell contains the #N/A error.
ISTEXT FunctionChecks if a cell contains text.
NA FunctionReturns the #N/A error value.
TYPE FunctionReturns a number that represents the data type of a value in a cell.

### F. Logical Functions

These logical functions in Excel are vital for performing conditional calculations, evaluating expressions, and making decisions based on specified conditions. They are essential tools for building dynamic and flexible worksheets.

Function NameDescription
AND FunctionReturns true if all specified conditions are true.
FALSE FunctionReturns the logical value “FALSE”.
IF FunctionReturns one value if a condition is true and another if false.
IFS FunctionReturns a value based on multiple conditions.
IFERROR FunctionReturns a specified value if a formula results in an error; otherwise, it returns the result of the formula.
NOT FunctionInverts the logical value of a cell.
OR FunctionReturns true if at least one specified condition is true.
TRUE FunctionReturns the logical value “TRUE”.

### G. Lookup and Reference Functions

These functions are used for a wide range of tasks in Excel, including data manipulation, searching, and retrieving values based on various criteria. They are powerful tools for creating dynamic and flexible spreadsheets.

Function NameDescription
ADDRESS FunctionReturns a cell address as text based on a specified row and column number.
CHOOSE FunctionReturns a value from a list based on its position.
COLUMN FunctionReturns the column number of a reference.
COLUMNS FunctionReturns the number of columns in a range.
FILTER FunctionFilters a range of data based on specified criteria.
FORMULATEXT FunctionReturns a formula as text from a specified cell.
HLOOKUP FunctionSearches for a value in the top row of a table and returns a corresponding value from a specified row.
INDEX FunctionReturns the value of a cell in a specified row and column.
INDIRECT FunctionReturns the value of a cell specified by a text string.
LOOKUP FunctionSearches for a value in a range and returns a corresponding value.
MATCH FunctionSearches for a value in a range and returns its relative position.
OFFSET FunctionReturns a cell or range that is a specified number of rows and columns from a given reference.
ROW FunctionReturns the row number of a reference.
ROWS FunctionReturns the number of rows in a range.
SORT FunctionSorts a range of data in ascending or descending order.
TRANSPOSE FunctionSwitches rows and columns in a range.
UNIQUE FunctionReturns unique values from a range.
VLOOKUP FunctionSearches for a value in a vertical range and returns a corresponding value.
XLOOKUP FunctionSearches for a value in a range and returns a corresponding value, with advanced capabilities.
XMATCH FunctionSearches for a value in a range and returns its relative position, with advanced capabilities.

## Conclusion

These 101 common Excel functions cover a wide range of tasks and scenarios, making Excel a versatile tool for data analysis, financial modeling, project management, and much more. By mastering these functions, you can significantly enhance your ability to work with data efficiently and effectively, no matter your profession or industry.

Rate this post