Excel Formulas Cheat Sheet: 30 Essential Functions with Examples

Lookup Functions

Lookup functions let you search for data across your spreadsheet, connecting related information from different columns or tables. These are among the most frequently used and most valuable Excel functions to learn.

1. VLOOKUP

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP searches for a value in the first column of a table and returns a corresponding value from a specified column. The last parameter should almost always be FALSE for an exact match.

Example: You have an employee table in A1:D500 with columns for Employee ID, Name, Department, and Salary. To look up the salary for employee ID 1042: =VLOOKUP(1042, A1:D500, 4, FALSE). This searches column A for 1042 and returns the value from the 4th column (Salary).

2. INDEX-MATCH

Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

INDEX-MATCH is a two-function combination that overcomes VLOOKUP's limitations. MATCH finds the row position of your lookup value, and INDEX returns the value from your desired column at that row position. Unlike VLOOKUP, it can look in any direction and is not affected by column insertions or deletions.

Example: Using the same employee table, to find the name (column B) for an employee whose salary (column D) is a specific amount: =INDEX(B1:B500, MATCH(75000, D1:D500, 0)). This looks "leftward," something VLOOKUP cannot do.

3. XLOOKUP

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP is Microsoft's modern replacement for VLOOKUP, available in Excel 365 and Excel 2021+. It searches any column or row and returns results from any other column or row, includes a built-in error handler, and defaults to exact match.

Example: =XLOOKUP(1042, A1:A500, D1:D500, "Not Found") searches for employee 1042 in column A and returns their salary from column D. If the ID is not found, it displays "Not Found" instead of an error.

Math Functions

Math functions handle calculations from simple addition to complex conditional aggregation. These are the workhorses of any spreadsheet that involves numbers.

4. SUM

Syntax: =SUM(number1, [number2], ...)

The most basic and most used Excel function. SUM adds all numbers in a range or a list of values. It ignores text and blank cells.

Example: =SUM(B2:B100) adds all values in cells B2 through B100. You can also sum non-contiguous ranges: =SUM(B2:B50, D2:D50, F2:F50).

5. SUMIFS

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SUMIFS adds values that meet multiple conditions simultaneously. It is one of the most powerful functions for data analysis without pivot tables.

Example: To sum sales amounts in column D where the region (column B) is "West" and the product (column C) is "Widget": =SUMIFS(D2:D1000, B2:B1000, "West", C2:C1000, "Widget"). You can also use comparison operators: =SUMIFS(D2:D1000, E2:E1000, ">=2026-01-01") sums values where dates in column E are in 2026 or later.

6. AVERAGE

Syntax: =AVERAGE(number1, [number2], ...)

Returns the arithmetic mean of the provided numbers. Like SUM, it ignores text and blank cells, but be aware that cells containing zero are included in the calculation.

Example: =AVERAGE(C2:C50) returns the average of all numeric values in the range. For conditional averages, use =AVERAGEIFS(C2:C50, B2:B50, "Sales") to average only rows where column B equals "Sales".

7. ROUND

Syntax: =ROUND(number, num_digits)

Rounds a number to a specified number of decimal places. Use positive num_digits for decimal places and negative for rounding to tens, hundreds, etc.

Example: =ROUND(3.14159, 2) returns 3.14. =ROUND(1547, -2) returns 1500. This is especially important for financial calculations where you need to round to cents: =ROUND(price * tax_rate, 2). For calculations involving currency, you may want to use our percentage calculator to double-check your work.

Text Functions

Text functions manipulate, extract, and transform strings of text. They are essential for cleaning imported data, standardizing formats, and combining information from multiple cells.

8. CONCAT (or CONCATENATE)

Syntax: =CONCAT(text1, [text2], ...)

Joins multiple text strings into one. CONCAT is the modern version that can accept ranges; CONCATENATE requires individual cell references.

Example: To combine first name (A2) and last name (B2) with a space between: =CONCAT(A2, " ", B2) or equivalently =A2 & " " & B2. To create a full address: =CONCAT(A2, ", ", B2, ", ", C2, " ", D2) produces something like "123 Main St, Austin, TX 78701".

9. LEFT

Syntax: =LEFT(text, [num_chars])

Extracts a specified number of characters from the beginning (left side) of a text string.

Example: If cell A2 contains "INV-2026-0042", then =LEFT(A2, 3) returns "INV". This is useful for extracting codes, prefixes, or area codes from phone numbers like =LEFT(A2, 3) on "(512) 555-1234" returning "(51".

10. RIGHT

Syntax: =RIGHT(text, [num_chars])

Extracts characters from the end (right side) of a text string. Works identically to LEFT but from the opposite direction.

Example: =RIGHT("INV-2026-0042", 4) returns "0042". This is commonly used to extract file extensions, suffixes, or the last few digits of an account number.

11. TRIM

Syntax: =TRIM(text)

Removes all extra spaces from text, leaving only single spaces between words. This is critical when working with imported or pasted data that often contains invisible leading, trailing, or double spaces that cause lookup functions to fail.

Example: =TRIM(" John Smith ") returns "John Smith". A common pattern is wrapping VLOOKUP values in TRIM: =VLOOKUP(TRIM(A2), table, 2, FALSE) to prevent space-related lookup failures.

12. SUBSTITUTE

Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Replaces specific text within a string. Unlike Find and Replace, SUBSTITUTE works within formulas and can target specific occurrences.

Example: =SUBSTITUTE("Q1-2026-Sales", "-", "/") returns "Q1/2026/Sales". To replace only the first dash: =SUBSTITUTE("Q1-2026-Sales", "-", "/", 1) returns "Q1/2026-Sales". This is also useful for removing unwanted characters: =SUBSTITUTE(A2, ",", "") removes all commas.

Date Functions

Date functions handle calculations involving dates, from simple differences to complex business day calculations. Excel stores dates as serial numbers (days since January 1, 1900), which is why these functions exist to make date math readable.

13. TODAY

Syntax: =TODAY()

Returns the current date. This is a volatile function, meaning it recalculates every time the spreadsheet opens or recalculates. It takes no arguments.

Example: =TODAY()-A2 calculates the number of days between the date in A2 and today. =YEAR(TODAY())-YEAR(A2) gives a rough age calculation. For a more precise age calculation, try our age calculator.

14. DATEDIF

Syntax: =DATEDIF(start_date, end_date, unit)

Calculates the difference between two dates in years ("Y"), months ("M"), or days ("D"). This is an undocumented function (it does not appear in autocomplete) but has been in Excel for decades and works reliably.

Example: =DATEDIF(A2, TODAY(), "Y") returns the number of complete years between the date in A2 and today, perfect for calculating age or tenure. =DATEDIF("2020-03-15", "2026-03-16", "M") returns 72, the number of complete months between those dates.

15. NETWORKDAYS

Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

Calculates the number of working days (excluding weekends) between two dates. The optional holidays parameter lets you exclude specific dates like company holidays.

Example: =NETWORKDAYS("2026-01-01", "2026-03-31") returns the number of business days in Q1 2026. If you have company holidays listed in H1:H5: =NETWORKDAYS("2026-01-01", "2026-03-31", H1:H5). This is invaluable for project planning and deadline estimation.

Logic Functions

Logic functions let you build conditional formulas that return different results based on whether specified conditions are true or false. These functions transform static spreadsheets into dynamic decision-making tools.

16. IF

Syntax: =IF(logical_test, value_if_true, value_if_false)

The foundational logic function. IF evaluates a condition and returns one value if true and another if false. You can nest up to 64 IF functions, though using IFS (below) is cleaner for multiple conditions.

Example: =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))) converts a numeric score into a letter grade. A simpler example: =IF(A2>0, "Profit", "Loss").

17. IFS

Syntax: =IFS(logical_test1, value1, [logical_test2, value2], ...)

Evaluates multiple conditions in order and returns the value associated with the first TRUE condition. Available in Excel 2019 and later, IFS eliminates the need for nested IF statements.

Example: The grade formula above becomes much cleaner: =IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F"). The final TRUE, "F" serves as a default/else case.

18. AND

Syntax: =AND(logical1, [logical2], ...)

Returns TRUE only if all conditions are true. Typically used inside an IF function to test multiple conditions simultaneously.

Example: =IF(AND(B2>=18, C2="Active"), "Eligible", "Not Eligible") checks whether someone is both 18 or older AND has Active status. All conditions must be met for the result to be "Eligible".

19. OR

Syntax: =OR(logical1, [logical2], ...)

Returns TRUE if any condition is true. Like AND, it is most often used inside IF functions.

Example: =IF(OR(B2="Manager", B2="Director", B2="VP"), "Leadership", "Staff") categorizes employees into leadership or staff based on their title matching any of the listed values.

20. IFERROR

Syntax: =IFERROR(value, value_if_error)

Returns a custom value if a formula evaluates to an error, and the normal result otherwise. This is essential for creating polished spreadsheets that handle edge cases gracefully.

Example: =IFERROR(B2/C2, 0) performs division and returns 0 instead of a #DIV/0! error when C2 is zero. Wrapping lookups is even more common: =IFERROR(VLOOKUP(A2, PriceTable, 2, FALSE), "Price not available").

Count Functions

Count functions tally cells based on various criteria. They are fundamental for data analysis, quality checks, and building dashboard summaries.

21. COUNT

Syntax: =COUNT(value1, [value2], ...)

Counts the number of cells that contain numeric values. Text, blanks, and errors are ignored.

Example: =COUNT(A1:A1000) tells you how many cells in that range contain numbers. This is useful for data validation: if you expect 500 numeric entries and COUNT returns 487, you know 13 entries are missing or contain text.

22. COUNTA

Syntax: =COUNTA(value1, [value2], ...)

Counts all non-empty cells regardless of data type. It counts numbers, text, dates, logical values, and even errors. Only truly blank cells are excluded.

Example: =COUNTA(A2:A1000) tells you the total number of filled cells. To find the number of blank cells, subtract: =ROWS(A2:A1000)-COUNTA(A2:A1000) gives you the count of empty cells in the range.

23. COUNTIFS

Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Counts cells that meet one or more conditions. It is the multi-criteria version of COUNTIF and works similarly to SUMIFS but counts instead of summing.

Example: =COUNTIFS(B2:B1000, "Sales", D2:D1000, ">50000") counts how many employees in the Sales department have values greater than 50,000 in column D. You can also count within date ranges: =COUNTIFS(E2:E1000, ">=2026-01-01", E2:E1000, "<=2026-03-31") counts Q1 2026 entries.

Additional Essential Functions

Beyond the core categories above, these functions round out your Excel toolkit and handle common needs that arise in everyday spreadsheet work.

24. MIN and MAX

Syntax: =MIN(number1, [number2], ...) and =MAX(number1, [number2], ...)

Return the smallest and largest values in a range, respectively. Use MINIFS and MAXIFS (Excel 2016+) for conditional minimum and maximum.

Example: =MAX(B2:B500)-MIN(B2:B500) calculates the range (spread) of a dataset. =MAXIFS(D2:D500, B2:B500, "Sales") returns the highest value in column D for the Sales department.

25. LEN

Syntax: =LEN(text)

Returns the number of characters in a text string, including spaces.

Example: =LEN(A2) is useful for validating data entry. For instance, =IF(LEN(A2)=10, "Valid", "Invalid") checks whether a phone number (without formatting) has exactly 10 digits.

26. PROPER, UPPER, and LOWER

Syntax: =PROPER(text), =UPPER(text), =LOWER(text)

PROPER capitalizes the first letter of each word. UPPER converts all text to uppercase. LOWER converts all text to lowercase. These are essential for standardizing data.

Example: =PROPER("john smith") returns "John Smith". =UPPER("widget") returns "WIDGET". These are particularly useful when cleaning imported data or combining lists from different sources with inconsistent capitalization.

27. TEXT

Syntax: =TEXT(value, format_text)

Converts a number or date into text with a specific format. This is useful when you need to combine formatted numbers or dates with text strings.

Example: =TEXT(0.15, "0.0%") returns "15.0%". =TEXT(TODAY(), "MMMM D, YYYY") returns something like "March 16, 2026". ="Invoice total: " & TEXT(B10, "#,##0.00") creates "Invoice total: 1,234.56".

28. UNIQUE (Excel 365)

Syntax: =UNIQUE(array, [by_col], [exactly_once])

Returns a list of unique values from a range. This dynamic array function spills results into adjacent cells automatically.

Example: =UNIQUE(B2:B500) extracts all unique department names from a list. =SORT(UNIQUE(B2:B500)) does the same but sorted alphabetically. Set the third parameter to TRUE to return values that appear exactly once.

29. FILTER (Excel 365)

Syntax: =FILTER(array, include, [if_empty])

Returns a filtered subset of an array based on criteria you define. Like UNIQUE, it is a dynamic array function that spills results.

Example: =FILTER(A2:D500, C2:C500="Sales", "No results") returns all rows where column C is "Sales". You can combine conditions: =FILTER(A2:D500, (C2:C500="Sales")*(D2:D500>50000)) filters for Sales department with values over 50,000.

30. SUMPRODUCT

Syntax: =SUMPRODUCT(array1, [array2], ...)

Multiplies corresponding elements of given arrays and returns the sum of those products. It is incredibly versatile for weighted calculations, conditional sums in older Excel versions, and multi-criteria analysis.

Example: To calculate weighted average grades where column B has scores and column C has weights: =SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10). For calculating total order value where column B has quantities and column C has unit prices: =SUMPRODUCT(B2:B100, C2:C100). Our percentage calculator can help verify weighted average calculations.

Tips for Working with Excel Formulas

Knowing the formulas is only half the battle. These practical tips will help you use them more effectively in real work.

Use Named Ranges

Instead of referencing A2:A500, name that range "EmployeeIDs" and use =VLOOKUP(1042, EmployeeData, 4, FALSE). Named ranges make formulas self-documenting and easier to maintain. Define them through Formulas > Name Manager.

Absolute vs. Relative References

Use the dollar sign to lock cell references when copying formulas. $A$1 is fully locked, $A1 locks only the column, and A$1 locks only the row. Press F4 to cycle through reference types while editing a formula.

Audit Your Formulas

Use Formulas > Trace Precedents and Trace Dependents to visually see which cells feed into your formula and which cells depend on it. Press Ctrl+` (backtick) to toggle between showing formula results and the formulas themselves.

Error Checking Strategy

Build formulas incrementally. Start with the innermost function, verify it works, then wrap it in the next layer. For complex nested formulas, use the Evaluate Formula tool (Formulas > Evaluate Formula) to step through the calculation one layer at a time. For quick math verification outside Excel, use our data conversion tools to cross-check your results.

Frequently Asked Questions