AI Excel Generator
← ALL GUIDES

Excel Formulas Cheat Sheet: Essential Functions and Examples

Updated November 4, 2026 · AI Excel Generator

An Excel formulas cheat sheet helps you choose the right function, understand its syntax, and adapt it to your own ranges.

The examples below include expected results and point out details that can change how a formula behaves.

Formula basics

Every Excel formula starts with an equals sign (=).

A formula can use cell references, values, operators, and functions.

For example, =B2*C2 multiplies the values in cells B2 and C2.

Function arguments go inside parentheses and are usually separated by commas, as in =SUM(B2:B10).

Some regional settings use semicolons instead of commas; if Excel reports a syntax error, check the list separator configured for your system.

A colon marks a range, so B2:B10 includes every cell from B2 through B10.

Everyday math and counting formulas

  • Add a range: =SUM(B2:B10) adds the numbers in B2 through B10.

    If those cells contain 3, 4, and 5, the result is 12.
  • Find an average: =AVERAGE(B2:B10) calculates the arithmetic mean of numeric cells.

    For 3, 4, and 5, the result is 4.

    Blank cells are ignored; cells containing zero are included.
  • Find the largest or smallest value: =MAX(B2:B10) returns the largest number, while =MIN(B2:B10) returns the smallest.

    In a range containing 3, 4, and 5, these return 5 and 3.
  • Count numbers: =COUNT(B2:B10) counts cells containing numbers.

    Use =COUNTA(B2:B10) to count nonblank cells, including text.
  • Round to two decimal places: =ROUND(B2,2) rounds the value in B2 to two decimal places.

    For 12.345, the result is 12.35.

Formatting a cell to show fewer decimal places changes its appearance, not the stored value.

Use ROUND when later calculations should use the rounded number.

Conditional calculations and logic

Conditional functions evaluate criteria in a range.

Text criteria and comparison operators must be written inside quoted text arguments.

  • Sum matching values: =SUMIF(A2:A10,"East",B2:B10) adds values in B2:B10 where the corresponding cell in A2:A10 is East.

    If East appears in rows 2 and 4 and their B values are 120 and 80, the result is 200.
  • Count values above a threshold: =COUNTIF(B2:B10,">100") counts cells greater than 100.

    The greater-than operator is part of the quoted criterion.
  • Test a condition: =IF(B2>=60,"Pass","Review") returns Pass if B2 is at least 60, and Review otherwise.
  • Handle an error: =IFERROR(A2/B2,"Check input") displays a message if the division produces an error, such as when B2 is zero.

For two or more criteria, use SUMIFS or COUNTIFS.

For example, =SUMIFS(C2:C10,A2:A10,"East",B2:B10,"Paid") adds C values only where the same row has East in A and Paid in B.

Each criteria range must match the sum range in size and orientation.

Look up values in a table

For Microsoft 365 and Excel 2021 or later, XLOOKUP offers a direct way to find a matching value.

With product IDs in A2:A10 and prices in B2:B10, use =XLOOKUP(E2,A2:A10,B2:B10,"Not found") to find the ID in E2 and return its price.

If there is no match, the formula returns Not found.

In older Excel versions, VLOOKUP is widely supported: =VLOOKUP(E2,A2:B10,2,FALSE) searches the first column of A2:B10 and returns the value from its second column.

The final argument, FALSE, requests an exact match.

VLOOKUP cannot return a value from a column to the left of its lookup column.

Lookup matches can fail when one ID is stored as text and the other as a number, or when text contains extra spaces.

Check the underlying values and use consistent data types before changing the lookup formula.

Text and date formulas

  • Join text: =A2&" "&B2 combines first and last names with a space between them.

    The ampersand joins text values.
  • Remove extra spaces: =TRIM(A2) removes leading and trailing spaces and reduces repeated internal spaces to single spaces.

    It does not remove every special whitespace character copied from websites.
  • Return today's date: =TODAY() returns the current date and updates when the workbook recalculates.

    Use =NOW() for the current date and time.
  • Calculate days between dates: =B2-A2 returns the number of days between valid Excel dates in A2 and B2.

    For example, January 1 to January 10 is 9 days.

Excel stores dates as serial numbers, so subtraction works when both cells contain real dates.

If a date is stored as text, convert it to a date value first; changing the cell's number format alone may not convert it.

Relative and absolute references

When you copy a formula, Excel normally adjusts relative references.

If D2 contains =B2*C2 and you fill it down one row, Excel changes the formula to =B3*C3.

Add dollar signs to keep a reference fixed.

If F1 contains a tax rate, =B2*$F$1 keeps F1 fixed when copied down. $F$1 locks both the column and row; F$1 locks only the row, and $F1 locks only the column.

In desktop Excel, selecting a reference in the formula bar and pressing F4 cycles through these reference styles.

Common formula errors and quick checks

  • #DIV/0!: A formula divides by zero or a blank cell.

    Check the divisor or handle the case with IFERROR.
  • #N/A: A lookup did not find a match.

    Check spelling, spaces, and whether the lookup values use matching data types.
  • #VALUE!: An argument may have the wrong type, such as text where a number is expected.
  • #NAME?: Excel may not recognize a function name or text value.

    Check spelling and put text criteria in quotation marks.
  • Formula displays as text: The cell may use the Text number format, or the entry may start with an apostrophe.

    Change the format to General, then re-enter the formula.

Also check that parentheses are balanced, ranges have the intended endpoints, and calculation mode is set to Automatic if results are not updating.

Excel and Google Sheets compatibility

Common functions such as SUM, AVERAGE, IF, COUNTIF, and VLOOKUP are available in both Microsoft Excel and Google Sheets.

Function availability and behavior can differ for newer functions. XLOOKUP is available in supported current Excel versions, but may be unavailable in older Excel releases; check the spreadsheet app and version before relying on it.

Dynamic array functions such as FILTER, UNIQUE, and SORT require a compatible version.

In Excel, they are available in Microsoft 365 and supported newer releases; an older version may show #NAME? or fail to calculate them.

Confirm the version and app before sharing a workbook that relies on newer functions.