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.
Download A List of 101 Common Excel Functions in PDF
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 Name | Description |
|---|---|
| SUM Function | Adds up a range of numbers. |
| SUMIF Function | Adds all the numbers in a range that meets a single condition. |
| SUMIFS Function | Adds all the numbers in a range that meets multiple conditions. |
| SUMPRODUCT Function | Multiplies corresponding elements in arrays and returns the sum. |
| ABS Function | Returns the absolute value of a number. |
| BASE Function | Converts a number from one base to another. |
| CEILING Function | Rounds a number up to the nearest integer or to the nearest multiple of significance. |
| COS Function | Calculates the cosine of an angle. |
| FLOOR Function | Rounds a number down to the nearest integer or to the nearest multiple of significance. |
| INT Function | Rounds a number down to the nearest integer. |
| MOD Function | Returns the remainder after division. |
| MROUND Function | Rounds a number to the nearest multiple. |
| RAND Function | Generates a random number between 0 and 1. |
| RANDBETWEEN Function | Generates a random number between specified minimum and maximum values. |
| ROUND Function | Rounds a number to a specified number of decimal places. |
| ROUNDDOWN Function | Rounds a number down to a specified number of decimal places. |
| ROUNDUP Function | Rounds a number up to a specified number of decimal places. |
| SIN Function | Calculates the sine of an angle. |
| SQRT Function | Calculates the square root of a number. |
| SUBTOTAL Function | Returns a subtotal in a list or database. |
| TRUNC Function | Truncates 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 Name | Description |
|---|---|
| CHAR Function | Returns the character specified by a number. |
| CLEAN Function | Removes non-printable characters from text. |
| CODE Function | Returns the numeric Unicode value of the first character in a text string. |
| CONCAT Function | Combines two or more text strings into one. |
| CONCATENATE Function | Combines two or more text strings into one. |
| EXACT Function | Tests if two text strings are exactly the same. |
| FIND Function | Searches for a text string within another text string and returns its starting position. |
| LEFT Function | Extracts a specified number of characters from the beginning of a text string. |
| LEN Function | Returns the number of characters in a text string. |
| MID Function | Extracts characters from the middle of a text string. |
| PROPER Function | Capitalizes the first letter of each word in a text string. |
| REPLACE Function | Replaces part of a text string with another text string. |
| RIGHT Function | Extracts a specified number of characters from the end of a text string. |
| SEARCH Function | Finds one text string within another text string (case-insensitive) and returns its starting position. |
| SUBSTITUTE Function | Replaces occurrences of a specified text within a text string. |
| TEXT Function | Converts a value to text in a specified format. |
| TEXTJOIN Function | Concatenates text from multiple cells with specified delimiters. |
| TRIM Function | Removes extra spaces from a text string. |
| UPPER Function | Converts text to uppercase. |
| VALUE Function | Converts 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 Name | Description |
|---|---|
| DATE Function | Creates a date based on year, month, and day. |
| DATEDIF Function | Calculates the difference between two dates in various units (e.g., days, months, years). |
| DAY Function | Extracts the day from a date. |
| DAYS Function | Calculates the number of days between two dates. |
| HOUR Function | Extracts the hour from a time. |
| MINUTE Function | Extracts the minute from a time. |
| MONTH Function | Extracts the month from a date. |
| NETWORKDAYS Function | Calculates the number of working days between two dates, excluding weekends and specified holidays. |
| NOW Function | Returns the current date and time. |
| SECOND Function | Extracts the second from a time. |
| TIME Function | Creates a time based on hours, minutes, and seconds. |
| TODAY Function | Returns the current date. |
| WORKDAY Function | Calculates the date of the nth working day after a given date, excluding weekends and specified holidays. |
| YEAR Function | Extracts 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 Name | Description |
|---|---|
| AVERAGE Function | Calculates the average of a range of numbers. |
| AVERAGEIFS Function | Calculates the average of cells that meet multiple criteria. |
| COUNT Function | Counts the number of cells that contain numbers. |
| COUNTA Function | Counts the number of cells that are not empty. |
| COUNTBLANK Function | Counts the number of empty cells in a range. |
| COUNTIF Function | Counts cells that meet a specific criterion. |
| COUNTIFS Function | Counts cells that meet multiple criteria. |
| MAX Function | Returns the highest value in a range. |
| MIN Function | Returns the lowest value in a range. |
| SMALL Function | Returns 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 Name | Description |
|---|---|
| CELL Function | Returns information about a cell, such as its format. |
| ISBLANK Function | Checks if a cell is empty. |
| ISERROR Function | Checks if a cell contains an error. |
| ISNA Function | Checks if a cell contains the #N/A error. |
| ISTEXT Function | Checks if a cell contains text. |
| NA Function | Returns the #N/A error value. |
| TYPE Function | Returns 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 Name | Description |
|---|---|
| AND Function | Returns true if all specified conditions are true. |
| FALSE Function | Returns the logical value “FALSE”. |
| IF Function | Returns one value if a condition is true and another if false. |
| IFS Function | Returns a value based on multiple conditions. |
| IFERROR Function | Returns a specified value if a formula results in an error; otherwise, it returns the result of the formula. |
| NOT Function | Inverts the logical value of a cell. |
| OR Function | Returns true if at least one specified condition is true. |
| TRUE Function | Returns 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 Name | Description |
|---|---|
| ADDRESS Function | Returns a cell address as text based on a specified row and column number. |
| CHOOSE Function | Returns a value from a list based on its position. |
| COLUMN Function | Returns the column number of a reference. |
| COLUMNS Function | Returns the number of columns in a range. |
| FILTER Function | Filters a range of data based on specified criteria. |
| FORMULATEXT Function | Returns a formula as text from a specified cell. |
| HLOOKUP Function | Searches for a value in the top row of a table and returns a corresponding value from a specified row. |
| HYPERLINK Function | Creates a clickable hyperlink. |
| INDEX Function | Returns the value of a cell in a specified row and column. |
| INDIRECT Function | Returns the value of a cell specified by a text string. |
| LOOKUP Function | Searches for a value in a range and returns a corresponding value. |
| MATCH Function | Searches for a value in a range and returns its relative position. |
| OFFSET Function | Returns a cell or range that is a specified number of rows and columns from a given reference. |
| ROW Function | Returns the row number of a reference. |
| ROWS Function | Returns the number of rows in a range. |
| SORT Function | Sorts a range of data in ascending or descending order. |
| TRANSPOSE Function | Switches rows and columns in a range. |
| UNIQUE Function | Returns unique values from a range. |
| VLOOKUP Function | Searches for a value in a vertical range and returns a corresponding value. |
| XLOOKUP Function | Searches for a value in a range and returns a corresponding value, with advanced capabilities. |
| XMATCH Function | Searches 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.
Excel Glossary | Active Cell | Cell Address | Range | Argument | Formula | Wildcard | Worksheet | Workbook
