Excel is a fantastic tool for working with data, and it relies on something called "formulas" to do its magic.
Whether you're just starting out with Microsoft Excel or you're already an expert, this page has a list of important Excel formulas that we've organized into different groups, Text Functions, Logical Functions, Lookup Functions and Most Common Functions.
These formulas are like special instructions that Excel understands.
They help you do things like math calculations, change text, make decisions, and find information in your data.
It's like having a set of secret codes that make Excel work wonders for you.
Whether you're a student, a business professional, or anyone who uses Excel, these formulas are essential tools you should know about.
And if you want to keep this list handy, we've made it available Excel Formulas List in PDF as a downloadable PDF. Just click the link below to get your own copy.
It's like having your own Excel superpowers at your fingertips!
Essential Excel functions for basic calculations, data analysis, and summarizing data efficiently.
Function | Syntax | Description |
---|---|---|
SUM | SUM(number1, number2, ...) | Adds up a range of numbers. |
AVERAGE | AVERAGE(number1, number2, ...) | Calculates the average of a range of numbers. |
MAX | MAX(number1, number2, ...) | Returns the largest value in a range. |
MIN | MIN(number1, number2, ...) | Returns the smallest value in a range. |
COUNT | COUNT(value1, value2, ...) | Counts the number of cells with numbers in a range. |
COUNTA | COUNTA(value1, value2, ...) | Counts the number of non-empty cells in a range. |
SUMIF | SUMIF(range, criteria, [sum_range]) | Adds up numbers based on a specified condition. |
COUNTIF | COUNTIF(range, criteria) | Counts cells based on a specified condition. |
AVERAGEIF | AVERAGEIF(range, criteria, [average_range]) | Calculates the average of numbers based on a specified condition. |
SUMIFS | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Adds up numbers based on multiple conditions. |
COUNTIFS | COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Counts cells based on multiple conditions. |
AVERAGEIFS | AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Calculates the average of numbers based on multiple conditions. |
Text Functions in Excel are a set of tools that allow you to manipulate text data, whether it's combining, extracting, or modifying text strings.
Function | Syntax | Description |
---|---|---|
CONCATENATE | CONCATENATE(text1, text2, ...) | Combines multiple text strings into one. |
LEFT | LEFT(text, num_chars) | Returns a specified number of characters from the beginning of a text string. |
LEN | LEN(text) | Returns the length (number of characters) of a text string. |
MID | MID(text, start_num, num_chars) | Returns a specified number of characters from the middle of a text string. |
LOWER | LOWER(text) | Converts text to lowercase. |
UPPER | UPPER(text) | Converts text to uppercase. |
PROPER | PROPER(text) | Capitalizes the first letter of each word in a text string. |
TEXT | TEXT(value, format_text) | Converts a value into text with a specified format. |
SUBSTITUTE | SUBSTITUTE(text, old_text, new_text, instance_num) | Replaces occurrences of a specified substring with another substring in a text string. |
Logical functions empower your spreadsheets to make decisions, validate data, and perform conditional calculations with ease.
Function | Syntax | Description |
---|---|---|
IF | IF(logical_test, value_if_true, value_if_false) | Returns one value if a condition is true and another if false. |
AND | AND(logical1, logical2, ...) | Returns TRUE if all arguments are true. |
OR | OR(logical1, logical2, ...) | Returns TRUE if at least one argument is true. |
NOT | NOT(logical) | Returns TRUE if the argument is false, and vice versa. |
These Excel functions allow you to search for specific data within your spreadsheet and retrieve related information, making it easier to find and work with relevant data in your worksheets.
Function | Syntax | Description |
---|---|---|
VLOOKUP | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Searches for a value in a table and returns a corresponding value from the same row. |
HLOOKUP | HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Retrieves a value from a table using a match criterion. |
INDEX and MATCH | INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num]) | Returns TRUE if at least one argument is true. |
LOOKUP | LOOKUP(lookup_value, lookup_vector, [result_vector]) | Searches for a value in a range and returns a corresponding value. |