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.
|Adds up a range of numbers.
|Adds all the numbers in a range that meets a single condition.
|Adds all the numbers in a range that meets multiple conditions.
|Multiplies corresponding elements in arrays and returns the sum.
|Returns the absolute value of a number.
|Converts a number from one base to another.
|Rounds a number up to the nearest integer or to the nearest multiple of significance.
|Calculates the cosine of an angle.
|Rounds a number down to the nearest integer or to the nearest multiple of significance.
|Rounds a number down to the nearest integer.
|Returns the remainder after division.
|Rounds a number to the nearest multiple.
|Generates a random number between 0 and 1.
|Generates a random number between specified minimum and maximum values.
|Rounds a number to a specified number of decimal places.
|Rounds a number down to a specified number of decimal places.
|Rounds a number up to a specified number of decimal places.
|Calculates the sine of an angle.
|Calculates the square root of a number.
|Returns a subtotal in a list or database.
|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.
|Returns the character specified by a number.
|Removes non-printable characters from text.
|Returns the numeric Unicode value of the first character in a text string.
|Combines two or more text strings into one.
|Combines two or more text strings into one.
|Tests if two text strings are exactly the same.
|Searches for a text string within another text string and returns its starting position.
|Extracts a specified number of characters from the beginning of a text string.
|Returns the number of characters in a text string.
|Extracts characters from the middle of a text string.
|Capitalizes the first letter of each word in a text string.
|Replaces part of a text string with another text string.
|Extracts a specified number of characters from the end of a text string.
|Finds one text string within another text string (case-insensitive) and returns its starting position.
|Replaces occurrences of a specified text within a text string.
|Converts a value to text in a specified format.
|Concatenates text from multiple cells with specified delimiters.
|Removes extra spaces from a text string.
|Converts text to uppercase.
|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.
|Creates a date based on year, month, and day.
|Calculates the difference between two dates in various units (e.g., days, months, years).
|Extracts the day from a date.
|Calculates the number of days between two dates.
|Extracts the hour from a time.
|Extracts the minute from a time.
|Extracts the month from a date.
|Calculates the number of working days between two dates, excluding weekends and specified holidays.
|Returns the current date and time.
|Extracts the second from a time.
|Creates a time based on hours, minutes, and seconds.
|Returns the current date.
|Calculates the date of the nth working day after a given date, excluding weekends and specified holidays.
|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.
|Calculates the average of a range of numbers.
|Calculates the average of cells that meet multiple criteria.
|Counts the number of cells that contain numbers.
|Counts the number of cells that are not empty.
|Counts the number of empty cells in a range.
|Counts cells that meet a specific criterion.
|Counts cells that meet multiple criteria.
|Returns the highest value in a range.
|Returns the lowest value in a range.
|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.
|Returns information about a cell, such as its format.
|Checks if a cell is empty.
|Checks if a cell contains an error.
|Checks if a cell contains the #N/A error.
|Checks if a cell contains text.
|Returns the #N/A error value.
|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.
|Returns true if all specified conditions are true.
|Returns the logical value “FALSE”.
|Returns one value if a condition is true and another if false.
|Returns a value based on multiple conditions.
|Returns a specified value if a formula results in an error; otherwise, it returns the result of the formula.
|Inverts the logical value of a cell.
|Returns true if at least one specified condition is true.
|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.
|Returns a cell address as text based on a specified row and column number.
|Returns a value from a list based on its position.
|Returns the column number of a reference.
|Returns the number of columns in a range.
|Filters a range of data based on specified criteria.
|Returns a formula as text from a specified cell.
|Searches for a value in the top row of a table and returns a corresponding value from a specified row.
|Creates a clickable hyperlink.
|Returns the value of a cell in a specified row and column.
|Returns the value of a cell specified by a text string.
|Searches for a value in a range and returns a corresponding value.
|Searches for a value in a range and returns its relative position.
|Returns a cell or range that is a specified number of rows and columns from a given reference.
|Returns the row number of a reference.
|Returns the number of rows in a range.
|Sorts a range of data in ascending or descending order.
|Switches rows and columns in a range.
|Returns unique values from a range.
|Searches for a value in a vertical range and returns a corresponding value.
|Searches for a value in a range and returns a corresponding value, with advanced capabilities.
|Searches for a value in a range and returns its relative position, with advanced capabilities.
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.