Excel formulas help you calculate, classify, look up, clean and analyze information without manually working through every row. This guide focuses on the formulas that are useful most often, with simple syntax, practical examples and a downloadable workbook you can use to practice.
Quick answer: If you are learning Excel, start with SUM, AVERAGE, IF, SUMIFS, COUNTIF and XLOOKUP. Then add text, date and dynamic-array functions as your work becomes more advanced.
Download the Coursecentrals Excel Formula Workbook
Keep the most useful Excel formulas at your fingertips. Includes essential formulas for calculations, IF statements, lookups, text, dates, dynamic arrays, statistics and more.
Practice your learning on w3schools
Excel formulas quick reference
| Formula | What it does | Example | Best for |
|---|---|---|---|
| SUM | Adds numbers | =SUM(B2:B10) | Totals and budgets |
| AVERAGE | Returns the arithmetic mean | =AVERAGE(B2:B10) | Average scores and KPIs |
| IF | Returns different results based on a condition | =IF(B2>=70,"Pass","Review") | Rules and classifications |
| SUMIFS | Adds values that meet multiple conditions | =SUMIFS(D:D,A:A,F2,B:B,G2) | Conditional analysis |
| COUNTIF | Counts cells that meet a condition | =COUNTIF(B:B,"Complete") | Conditional counts |
| XLOOKUP | Finds a match and returns a corresponding value | =XLOOKUP(A2,F:F,G:G,"Not found") | Modern lookups |
| IFERROR | Returns a fallback when a formula errors | =IFERROR(A2/B2,0) | Cleaner reports |
| TRIM | Removes extra spaces from text | =TRIM(A2) | Cleaning imported data |
| TODAY | Returns the current date | =TODAY() | Dynamic dates |
| FILTER | Returns rows that meet criteria | =FILTER(A2:D20,D2:D20>1000,"None") | Dynamic filtered lists |
| UNIQUE | Returns distinct values | =UNIQUE(A2:A20) | Deduplicated lists |
How Excel formulas work
An Excel formula starts with an equals sign (=). A formula can combine cell references, operators, constants and built-in functions. For example, =SUM(B2:B10) uses the SUM function to add the values from B2 through B10.
Basic Excel formulas
SUM
What it does: Adds numbers in a range.
Syntax: =SUM(number1,[number2],...)
Example: =SUM(B2:B10)
Best for: Sales totals, budgets, expenses and other numeric totals.
AVERAGE
What it does: Returns the arithmetic mean of a set of numbers.
Example: =AVERAGE(B2:B10)
MIN and MAX
Use =MIN(B2:B10) to return the smallest value and =MAX(B2:B10) to return the largest value in the range.
COUNT and COUNTA
COUNT counts cells containing numbers. COUNTA counts nonblank cells, making it useful when your records contain text as well as numbers.
Logical formulas: IF, AND, OR and IFERROR
IF
What it does: Tests a condition and returns one result when the condition is true and another when it is false.
Syntax: =IF(logical_test,value_if_true,value_if_false)
Example: =IF(B2>=70,"Pass","Review")
Use it for: Status labels, thresholds, eligibility rules and performance classifications.
AND and OR
AND returns TRUE when all specified conditions are true. OR returns TRUE when at least one condition is true. They are often combined with IF when a decision depends on several conditions.
IFERROR
Example: =IFERROR(A2/B2,0)
IFERROR is useful when a calculation or lookup could return an error and you want a cleaner fallback value or message.
Lookup formulas: XLOOKUP, VLOOKUP and INDEX MATCH
XLOOKUP
What it does: Searches one range and returns the corresponding value from another range.
Syntax: =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found])
Example: =XLOOKUP(A2,Products!A:A,Products!C:C,"Not found")
Best for: Product prices, employee details, campaign IDs and other matched records.
Microsoft positions XLOOKUP as an improved alternative to VLOOKUP because it can return values from either direction and uses exact matching by default. VLOOKUP remains useful when working with older spreadsheets or environments where XLOOKUP is unavailable.
Conditional formulas: SUMIF, SUMIFS, COUNTIF and COUNTIFS
SUMIFS
SUMIFS adds values only when multiple conditions are met.
Syntax: =SUMIFS(sum_range,criteria_range1,criteria1,...)
Example: =SUMIFS(D:D,A:A,F2,B:B,G2)
This is particularly useful for questions such as: What was total revenue in one market from one channel?
COUNTIF and COUNTIFS
Use COUNTIF for one condition and COUNTIFS when several conditions must be satisfied.
Text formulas for cleaning data
Text functions are useful when data arrives with inconsistent formatting or when you need to extract parts of IDs, names or labels.
=LEFT(A2,3)returns characters from the left.=RIGHT(A2,4)returns characters from the right.=TRIM(A2)removes unnecessary spaces.=TEXTJOIN(", ",TRUE,A2:C2)combines text using a chosen delimiter.
Date formulas
=TODAY()returns the current date.=YEAR(A2)extracts the year.=MONTH(A2)extracts the month number.=DAY(A2)extracts the day.=NETWORKDAYS(A2,B2)counts working days between two dates.
Dynamic array formulas: FILTER and UNIQUE
FILTER
Example: =FILTER(A2:D20,D2:D20>1000,"None")
FILTER can return a dynamic subset of a dataset based on criteria, which is useful for creating focused views without manually hiding rows.
UNIQUE
Example: =UNIQUE(A2:A20)
UNIQUE returns distinct values from a range and is useful for quickly creating lists of markets, categories, products or other dimensions.
Advanced formula: LET
LET lets you assign names to intermediate values inside a formula. This can make longer formulas easier to understand and can avoid recalculating the same expression repeatedly.
Example: =LET(revenue,B2,rate,C2,discount,revenue*rate,revenue-discount)
Which Excel formulas should beginners learn first?
For most beginners, a practical order is SUM and AVERAGE first, followed by IF, COUNTIF, SUMIF/SUMIFS and XLOOKUP. Once those feel comfortable, add text and date functions, then move to FILTER, UNIQUE and LET if your Excel version supports them.
Frequently asked questions
What is an Excel formula?
An Excel formula is an expression that begins with an equals sign and calculates a result using values, cell references, operators or functions.
What are the most useful Excel formulas?
There is no single list for every job, but SUM, AVERAGE, IF, SUMIFS, COUNTIF, XLOOKUP and IFERROR cover many everyday spreadsheet tasks. Text, date and dynamic-array functions become increasingly useful as datasets grow.
Is XLOOKUP better than VLOOKUP?
For compatible versions of Excel, XLOOKUP is generally more flexible because it can look in either direction and uses exact matching by default. VLOOKUP is still relevant for older workbooks and versions where XLOOKUP is unavailable.
How can I practise Excel formulas?
Practise with realistic data rather than memorising syntax alone. Change the inputs, inspect the result, deliberately create errors and compare similar functions. The downloadable Course Centrals workbook accompanying this guide is designed for that workflow.
Download the Excel Formula Workbook
The Coursecentrals workbook includes a formula cheat sheet plus examples for basic calculations, logical tests, lookups, conditional analysis, text cleaning, dates, dynamic arrays and LET.