Excel Formulas List and Cheat Sheet (With Examples)

Excel Formulas Cheat Sheet

Excel formulas are the engine behind fast analysis, clean data, and automated reporting. This cheat sheet gives you a categorized list of essential Excel formulas with syntax and simple examples you can copy directly into Excel.

Use it as a quick reference while you work in spreadsheets, or as a learning guide as you level up from basic to more advanced functions.

Dates and Time Excel Formulas Cheat Sheet

=EDATE

Add a specified number of months to a date in Excel.

Example: =EDATE(“2026-01-31”, 3) → 2026-04-30

=EOMONTH

Convert a date to the last day of the month.

Example: =EOMONTH(“2026-07-18”, 0) → 2026‑07‑31

=DATE

Returns a number that represents the date (yyyy/mm/dd) in Excel. This formula is useful when working with Excel functions that have a date as an argument.

Example: =DATE(2026,4,1)

=TODAY

nsert and display today’s date in a cell (Example: =TODAY())

=NETWORKDAYS

Returns the number of whole workdays between two specified dates.

Example: =NETWORKDAYS(“2026-04-01″,”2026-04-30”)

=YEAR

Extracts and displays the year from a date.

Example: =YEAR(“2026-07-18”) → 2026

=YEARFRAC

Expresses the fraction of a year between two dates.

Example: =YEARFRAC(“2026-01-01″,”2026-03-31”) → 0.25

Convert Time to Seconds

Converts an amount of time to seconds.

Example: 5 minutes to 300 seconds

Navigation Excel Formulas Cheat Sheet

Go To Special

Press F5 and find all cells that are hard-coded, formulas, and more. Great for auditing.

Find and Replace

Press Ctrl + F and you can change parts of many formulas at once.

Lookup Formulas

INDEX MATCH

A combination of lookup functions that are more powerful than VLOOKUP.

Example: =INDEX(C2:C100, MATCH(A2, A2:A100, 0))

=VLOOKUP

A lookup function that searches vertically in a table.

Example: =VLOOKUP(A2, A2:D100, 3, FALSE)

=HLOOKUP

A lookup function that searches horizontally in a table.

Example: =HLOOKUP(A2, A1:Z10, 3, FALSE)

=INDEX

A lookup function that searches vertically and horizontally in a table.

Example: =INDEX(C2:C100, 5)

=MATCH

Returns the position of a value in a series.

Example: =MATCH(A2, A2:A100, 0)

=OFFSET

Moves the reference of a cell by the number of rows and/or columns specified.

Example: =OFFSET(A2, 1, 2)

=XLOOKUP

Modern lookup function that replaces VLOOKUP/HLOOKUP; can look left or right and handle missing values gracefully.

Example: =XLOOKUP(A2, A2:A100, C2:C100, “Not found”

Math Functions Excel Formulas Cheat Sheet

=SUM

Add the total of a series of numbers.

Example: =SUM(B2:B100)

=AVERAGE

Calculates the average of a series of numbers.

Example: =AVERAGE(B2:B100)

=MEDIAN

Returns the median average number of a series.

Example: =MEDIAN(B2:B100)

=SUMPRODUCT

Calculates the weighted average, very useful for financial analysis.

Example: =SUMPRODUCT(B2:B100, C2:C100)

=PRODUCT

Multiplies all of a series of numbers.

Example: =PRODUCT(B2:B5)

=ROUNDDOWN

Rounds a number to the specified number of digits.

Example: =ROUNDDOWN(B2,2)

=ROUNDUP

The formula rounds a number to the specific number of digits.

Example: =ROUNDUP(B2,2)

AutoSum

A shortcut to quickly sum a series of numbers.

Example: Select B2:B10 and press Alt + =

=ABS

Returns the absolute value of a number.

Example: =ABS(-5) → 5

=PI

Returns the value of pi, accurate to 15 digits.

Example: =PI()

=SUMIF

Sum values in a range that are specified by a condition.

Example: =SUMIF(A2:A100, “North”, B2:B100)

=SUMQ

Returns the sum of the squares of the arguments.

Example: =SUMQ(B2:B5)

=COUNT

Count cells that contain numbers.

Example: =COUNT(B2:B100)

=COUNTA

Count non‑empty cells.

Example: =COUNTA(A2:A100)

=COUNTIF

Count cells that meet a single condition.

Example (values greater than 100): =COUNTIF(B2:B100,”>100″)

=COUNTIFS

Count cells that meet multiple conditions

Example: =COUNTIFS(A2:A100,”North”,B2:B100,”>100″)

Financial Formulas

=NPV

Calculates the net present value of cash flows based on a discount rate.

Example: =NPV(0.1,B2:B6)

=XNPV

Calculates the NPV of cash flows based on a discount rate and specific dates.

Example: =XNPV(0.1,B2:B6,A2:A6)

=IRR

This formula calculates the internal rate of return (discount rate that sets the NPV to zero).

Example: =IRR(B2:B6)

=XIRR

Calculates the internal rate of return (discount rate that sets the NPV to zero) with specified dates.

Example: =XIRR(B2:B8,A2:A8)

=YIELD

Returns the yield of a security based on maturity, face value, and interest rate.

Example: =YIELD(A2,B2,0.05,95,100,2,0)

=FV

Calculates the future value of an investment with constant periodic payments and a constant interest rate.

Example: =FV(0.05/12,60,-200,0,0)

=PV

Calculates the present value of an investment.

Example: =PV(0.05/12,60,-200,0,0)

=INTRATE

The interest rate on a fully invested security.

Example: =INTRATE(A2,B2,950,1000,0)

=IPMT

This formula returns the interest payments on a debt security.

Example: =IPMT(0.05/12,1,60,-10000)

=PMT

This function returns the total payment (debt and interest) on a debt security.

Example: =PMT(0.05/12,60,-10000)

=PRICE

Calculates the price per $100 face value of a periodic coupon bond.

Example: =PRICE(A2,B2,0.05,0.045,100,2,0)

=DB

Calculates depreciation based on the fixed-declining balance method.

Example: =DB(10000,1000,5,1)

=DDB

Calculates depreciation based on the double-declining balance method.

Example: =DDB(10000,1000,5,1)

=SLN

Calculates depreciation based on the straight-line method.

Example: =SLN(10000,1000,5)

=PPMT

Returns the principal portion of a payment in a given period.

Example: =PPMT(0.05/12,1,60,-10000)

Conditional Functions

=IF

Checks if a condition is met and returns a value if yes and if no.

=OR

Checks if any conditions are met and returns only “TRUE” or “FALSE”.

=XOR

The “exclusive or” statement returns true if the number of TRUE statements is odd.

=AND

Checks if all conditions are met and returns only “TRUE” or “FALSE”.

=NOT

Changes “TRUE” to “FALSE”, and “FALSE” to “TRUE”.

IF AND

Combine IF with AND to have multiple conditions.

=IFERROR

If a cell contains an error, you can tell Excel to display an alternative result.

Other Functions and Formulas

Sheet Name Code

A formula using MID, CELL, and FIND functions to display the worksheet name.

Consolidate

How to consolidate information between multiple Excel workbooks.

Additional Resources

• Excel Shortcuts for PC and Mac
• Advanced Excel Formulas
• Learn Basic Excel Formulas for Beginners
• Convert Time to Seconds in Excel
• Excel for Finance
• Protecting Excel Data
• Sheet Name Code Template

See all Excel resources

Connect what you just learned to a clear career path with CFI’s role‑based courses and certification programs.