Excel Formulas List and Cheat Sheet (With Examples)

The most important Excel formulas

Written by CFI Team

Updated April 20, 2026

Read Time 3 minutes

Earn Your Certification

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.

Earn Your Certification

Additional Resources

See all Excel resources

See all Financial Modeling resources