Excel, Formulas, Functions
Sep 16, 2026
List of Essential Excel Formulas
Key Takeaways
- This excel formulas list covers more than 15 essential functions across six categories: math and statistical, logical, conditional, text, lookup and date/time.
- Each formula includes its syntax, a plain-English explanation of how to read it and a practical example you can follow along with.
- Download the companion Excel file to practice every formula covered in this guide.
- Master these basic excel formulas to build a strong foundation for data analysis, reporting and everyday productivity.
What Are Excel Formulas?
An Excel formula is an expression that performs a calculation or manipulates data in a cell. Every formula begins with an equals sign (=), which tells Excel that the contents of the cell should be evaluated rather than displayed as plain text.
You may hear the terms "formula" and "function" used interchangeably, but they are not quite the same thing. A function is a predefined, named formula built into Excel, such as SUM or IF. A formula can use one or more functions, but it can also include operators, cell references and constants. For example, =A1+A2 is a formula that uses no function at all, while =SUM(A1:A10) is a formula that uses the SUM function.
Excel contains hundreds of built-in functions, which can feel overwhelming. The good news is that a relatively small set of important formulas in Excel covers the vast majority of everyday tasks. This guide focuses on those essentials so you can work more efficiently without memorizing every option.
See these formulas and functions in action by downloading: List of Excel Formulas.xlsx
How to Enter a Formula in Excel
Before diving into specific functions, here is a quick primer on how to use excel formulas. Every formula follows the same basic entry process:
- Click on the cell where you want the result to appear.
- Type the equals sign (=) to tell Excel you are entering a formula.
- Type the function name and an open parenthesis.
- Enter the required arguments, separated by commas.
- Close the parenthesis and press Enter.
To apply a formula to an entire column, enter it in the first cell, then double-click the small square (called the fill handle) in the lower-right corner of that cell. Excel will auto-fill the formula down to the last row of adjacent data.
Essential Excel Formulas at a Glance
The table below provides a quick-reference summary of every formula covered in this guide. Use it to scan for the function you need, then jump to the corresponding section for a full explanation and example.
Formula | Category | What It Does | Syntax |
|---|---|---|---|
SUM | Math/Statistical | Adds up a range of values | =SUM(number1,number2,...) |
AVERAGE | Math/Statistical | Calculates the mean of a range | =AVERAGE(number1,number2,...) |
COUNT | Math/Statistical | Counts cells that contain numbers | =COUNT(value1,value2,...) |
MIN | Math/Statistical | Returns the smallest value in a range | =MIN(number1,number2,...) |
MAX | Math/Statistical | Returns the largest value in a range | =MAX(number1,number2,...) |
POWER | Math/Statistical | Raises a number to a specified power | =POWER(number,power) |
IF | Logical | Returns one value if true, another if false | =IF(logical_test,value_if_true,value_if_false) |
AND | Logical | Returns TRUE if all conditions are true | =AND(logical1,logical2,...) |
OR | Logical | Returns TRUE if any condition is true | =OR(logical1,logical2,...) |
SUMIF | Conditional | Sums values that meet a single criterion | =SUMIF(range,criteria,sum_range) |
SUMIFS | Conditional | Sums values that meet multiple criteria | =SUMIFS(sum_range,criteria_range1,criteria1,...) |
TEXT | Text | Converts a number to text in a specified format | =TEXT(value,format_text) |
TRIM | Text | Removes extra spaces from text | =TRIM(text) |
VLOOKUP | Lookup | Searches a column and returns a value from another column | =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) |
HLOOKUP | Lookup | Searches a row and returns a value from another row | =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) |
TODAY | Date/Time | Returns the current date | =TODAY() |
NOW | Date/Time | Returns the current date and time | =NOW() |
DATEDIF | Date/Time | Calculates the difference between two dates | =DATEDIF(start_date,end_date,unit) |
Math and Statistical Functions
The most fundamental Excel formulas are the ones that perform basic calculations. Whether you are totaling a column of sales figures or finding the average score on a test, these functions handle the math so you do not have to.
SUM
Syntax: =SUM(number1,number2,...)
How to read: Add up these values.
Uses: To total a range of numbers quickly instead of writing out individual cell references with plus signs.
Example: you have monthly sales figures in cells A2 through A13 and want to calculate the annual total. Your formula would look like this:
=SUM(A2:A13)
You can also sum non-contiguous cells by separating them with commas, such as =SUM(A2,A5,A9).
AVERAGE
Syntax: =AVERAGE(number1,number2,...)
How to read: Calculate the mean of these values.
Uses: To find the average of a data set, such as the average order value or average test score.
Example: to find the average of the same monthly sales figures, your formula would look like this:
=AVERAGE(A2:A13)
Note that AVERAGE ignores empty cells but treats cells containing zero as valid values.
COUNT
Syntax: =COUNT(value1,value2,...)
How to read: Count how many cells in this range contain numbers.
Uses: To determine how many numeric entries exist in a range. This is helpful when you need to know how many transactions occurred or how many responses were recorded.
Example: to count how many months have recorded sales data, your formula would look like this:
=COUNT(A2:A13)
If you need to count cells that contain text or any non-blank value, use COUNTA instead.
MIN and MAX
Syntax: =MIN(number1,number2,...) and =MAX(number1,number2,...)
How to read: Find the smallest value in this range / Find the largest value in this range.
Uses: To identify the lowest and highest values in a data set, such as the lowest expense or the highest quarterly revenue.
Example: to find the best and worst sales months, your formulas would look like this:
=MIN(A2:A13)
=MAX(A2:A13)
POWER
Syntax: =POWER(number,power)
How to read: Raise this number to this power.
Uses: To calculate exponential values, such as compound interest growth or squared values in statistical analysis.
Example: to calculate five raised to the third power, your formula would look like this:
=POWER(5,3)
The result is 125. You can achieve the same result with the caret operator (=5^3), but the POWER function is easier to read in complex formulas.
Logical Functions
It's only logical — these functions let you build decision-making logic directly into your spreadsheets.
IF
Syntax: =IF(logical test,value if true,value if false)
How to read: If this is true, then this, otherwise this.
Uses: To create data where there isn't any.
Example: if the current period's purchases are greater than the previous period's, then we want to designate the status as "growth." If the current period's purchases are less than the previous period's, it should be designated as, "decline." Our formula could look like this:
=IF(B2>A2,"growth","decline")
AND
Syntax: =AND(logical1,logical2,logicaln)
How to read: This is true AND this is true AND this is true
Uses: To test whether all conditions expressed in each logical are accurate.
Say we continue our example above. If we need for the number of purchases to exceed 20 and the total purchases to equal greater than $50,000, we could create a formula in column C, if we recorded the number of purchases in column B.
=AND(A2>50000,B2>20)
The result will show either TRUE or FALSE. We could then use this new value to create an IF statement showing a Y or blank for the Premium? Column.
OR
Syntax: =OR(logical1,logical2,logicaln)
How to read: This is true OR this is true OR this is true
Uses: To test whether any conditions expressed in each logical are accurate. If we reinterpret what "premium" means, for example either over 20 purchases or over $50,000 in purchases, it would look like this:
=OR(A2>50000,B2>20)
If any of the conditions are true, the cell value will be TRUE. With the AND function above, all of the conditions had to be true.
Conditional Functions
That depends! Conditional functions let you perform calculations only when specific criteria are met, while conditional formatting lets you visually highlight cells using similar rules.
SUMIF
Syntax: =SUMIF(range,criteria,sum range)
How to read: What range do you want to examine and for what criteria? When found, what do you want to sum?
Uses: To add up values in a particular range based on certain criteria.
Example: we have values from various locations. We only want to create a sum with those from Detroit. So our formula would look like this:
=SUMIF(A2:A14,"Detroit",B2:B14)
A2 through A14 is where we will find the value Detroit. The values we want to add are in B2 through B14. If we wanted to add up all values over $500,000, we could do that with a simpler SUMIF formula, because what we want to examine is the same thing we want to add. When that's the case, you do not have to specify the sum range if it's going to be the same as the range. That formula would look like this:
=SUMIF(B2:B14,">500000")
Do you notice the double quotes around the criteria? Unless your criteria is a single value or cell reference, it usually has to be enclosed in double quotes.
SUMIFS
Syntax: =SUMIFS(sum range,criteria range1,criteria1,criteria range2,criteria2,criteria range n,criteria n)
How to read: Add up this range if each of these criteria ranges satisfy their corresponding criteria.
Uses: To perform a SUMIF with more than one set of criteria. For example, if we extend our example above to include both sets of criteria, our SUMIFS formula would look like this:
=SUMIFS(B2:B14,A2:A14,"Detroit",B2:B14,">500000")
In this case, you do have to specify B2 through B14 to evaluate whether the values are greater than $500,000, even though it is also the sum range.
Text Functions
When it comes to cleaning up your data, text functions are indispensable.
TEXT
Syntax: =TEXT(value,format text)
How to read: Put this numeric value in this text format.
Uses: To address the problem with zero front-filled values, like some zip codes.
Example: the original zip code column is formatted with a Special Format called Zip Code. However, when you try to use this column in a mail merge, you're likely to get the values in the Actual Value column, which would be missing the zeroes on the front of the last zip code. By applying the text function and specifying the format as "00000", the mail merge will actually use the zero front-filled number.
=TEXT(B2,"00000")
Notice the double quotes. These are required in order for Excel to recognize the format.
TRIM
Syntax: =TRIM(text)
How to read: Remove all leading and trailing spaces from this value, leaving only single spaces between words.
Uses: When values in one list are supposed to match another, but don't, it can be due to leading and/or trailing spaces. Using the TRIM function will help.
Note: To finish up, you may want to perform a Copy/Paste Values to permanently change the values in the cell.
Example: we use a formula that declares G2=F2. Some work and some don't in column H. Though it might not seem obvious, stray spaces are causing FALSE declarations. However, when we attempt =I2=F2 the first column of names is shown at equal to the second (column J).
Lookup Functions
Lookup functions allow you to search for a value in one part of your spreadsheet and return related information from another. These are among the most widely used Excel formulas in data analysis and reporting.
VLOOKUP
Syntax: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
How to read: Look for this value in the first column of this table, then return the value from this column number.
Uses: To pull data from another table based on a matching value. This is especially useful when you need to combine information from different data sets.
Example: you have a list of employee IDs in column A and you want to return each employee's department from a reference table in columns E through F. Your formula would look like this:
=VLOOKUP(A2,E:F,2,FALSE)
The FALSE argument at the end tells Excel to find an exact match. If you use TRUE or omit this argument, Excel will return an approximate match, which can produce unexpected results.
HLOOKUP
Syntax: =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
How to read: Look for this value in the first row of this table, then return the value from this row number.
Uses: HLOOKUP works the same way as VLOOKUP but searches horizontally across the first row instead of vertically down the first column. Use it when your data is arranged in rows rather than columns.
Example: if quarterly budget categories are listed across row 1 and you want to find the value for "Marketing," your formula would look like this:
=HLOOKUP("Marketing",A1:E4,3,FALSE)
This searches row 1 for "Marketing" and returns the value from the third row of the table.
Date and Time Functions
Date and time functions help you work with dates, calculate deadlines, track durations and supply the values behind date or time charts.
TODAY and NOW
Syntax: =TODAY() and =NOW()
How to read: Return today's date / Return the current date and time.
Uses: To timestamp reports, calculate deadlines or determine how much time has elapsed since a given date. Both functions update automatically each time the spreadsheet recalculates.
Example: to calculate how many days remain until a project deadline in cell B2, your formula would look like this:
=B2-TODAY()
The result is the number of days between today and the deadline. Use =NOW() instead when you need both the date and the time, such as logging when a record was last updated.
DATEDIF
Syntax: =DATEDIF(start_date,end_date,unit)
How to read: Calculate the difference between two dates in the unit you specify.
Uses: To calculate employee tenure, project duration or a person's age. The unit argument accepts "Y" for years, "M" for months and "D" for days.
Example: to calculate how many full years an employee has been with the company, with a hire date in cell A2, your formula would look like this:
=DATEDIF(A2,TODAY(),"Y")
Note that DATEDIF does not appear in Excel's formula autocomplete list, but it is a fully supported function. You need to type it manually.
Tips for Troubleshooting Excel Formula Errors
Even experienced users run into formula errors. Here are the most common error codes and what they mean:
- #REF!: A cell reference is invalid. This often happens when you delete a row or column that the formula referenced.
- #VALUE!: The formula has the wrong type of argument, such as text where a number is expected.
- #NAME?: Excel does not recognize the function name. Check for typos or missing quotation marks.
- #DIV/0!: The formula is trying to divide by zero or by an empty cell.
- #N/A: A lookup function could not find a match. Verify that the lookup value exists in the source data.
If you want to see every formula in your spreadsheet at once, press CTRL+` (the grave accent key, located to the left of the 1 key). This toggles between displaying formula results and displaying the formulas themselves, making it much easier to audit your work.
Build Your Excel Skills with Pryor Learning
Think of this list as a starter kit for a strong foundation in Excel and continue to practice different scenarios and building logicals and conditionals in your Excel sheets.
The more you practice, the more intuitive these formulas become. Download the List of Excel Formulas.xlsx companion file to try every formula covered in this guide using real examples.
When you are ready to go further, Pryor Learning offers Excel training designed for every skill level:
- Live virtual seminars led by expert instructors
- In-Person seminars for hands-on, classroom-style learning
- On-Demand courses through PryorPlus that you can complete at your own pace
Whether you are just getting comfortable with basic formulas or ready to tackle advanced data analysis, Pryor has a course that fits your goals.
