Microsoft Excel Table of Contents

This cheat sheet provides a quick reference for essential Common Functions Logical Functions
Excel functions, helping you perform calculations, DATE, IF, VLOOKUP
AND, IF, NOT
analyze data, and manage spreadsheets with ease. It
covers a wide range of categories, including logical,
statistical, text, lookup, and financial functions, ensuring Database Functions Lookup & Reference Functions
DSUM, DCOUNT, DAVERAGE INDEX, MATCH, XLOOKUP
you have the tools needed to tackle diverse tasks—from
simple summations to advanced financial modeling.

Date & Time Functions Statistical Functions

Each entry includes clear syntax and concise DATEDIF, TODAY, YEAR AVERAGE, COUNT, QUARTILE
explanations, paired with practical examples to
demonstrate how the function operates in real-world
scenarios. Whether you're calculating averages, finding Financial Functions Text Functions
ACCRINT, EFFECT, FV CONCATENATE, FIND, REPLACE
unique values, or building dynamic references with
functions like VLOOKUP and INDEX, this cheat sheet
simplifies Excel’s powerful functionality.

Information Functions Web Functions

ISNUMBER, ISNA, TYPE ENCODEURL, FILTERXML, WEBSERVICE
Designed to be accessible and actionable, this resource
is perfect for professionals, students, and data
enthusiasts looking to improve their efficiency and
confidence in Excel. Keep it handy for quick guidance on
Excel's diverse capabilities.

Excel Cheat Sheet

 Common Functions Database Functions
Function How to use Explained Function How to use Explained

Calculates the average of a range of DCOUNT

Counts the cells that contain numbers
AVERAGE =AVERAGE(A1:A10) =DCOUNT(Database, "Field", Criteria)
numbers. in a column of a list or database that
match conditions.
CHOOSE =CHOOSE(2, A1, C3, E9)
Returns a value from a list based on an
index number. DAVERAGE
Averages the numbers in a column of a
=DAVERAGE(Database, "Field",

list or database that match conditions.

Criteria)
DATE =DATE(2023, 12, 25)
Creates a date with the specified year,
month, and day.
DCOUNTA
Counts nonblank cells in a column of a
=DCOUNTA(Database, "Field", Criteria)
DAYS =DAYS("2023-12-25", "2023-01-01")
Returns the number of days between list or database that match conditions.
two dates.
DGET
Extracts a single value from a column
=DGET(Database, "Field", Criteria)
FIND
Finds the starting position of one text of a list or database that matches
=FIND("M", "Microsoft")
string within another. conditions.

IF
Returns one value if a condition is true, DMAX
Returns the maximum value from a
=IF(A1>10, "Yes", "No") =DMAX(Database, "Field", Criteria)
another if false. column of a list or database that
matches conditions.
INDEX =INDEX(A1:C10, 2, 3)
Returns the value of a cell in a
specified row and column. DMIN
Returns the minimum value from a
=DMIN(Database, "Field", Criteria)
column of a list or database that
Searches for a value in a range and matches conditions.
MATCH =MATCH(100, A1:A10, 0)
returns its position.
DPRODUCT
Multiplies values in a column of a list
=DPRODUCT(Database, "Field", Criteria)
or database that match conditions.
SUM =SUM(A1:A10) Adds all numbers in a range.

VLOOKUP =VLOOKUP(B1, A1:C10, 2, FALSE) Looks up a value in a table and returns

a corresponding value.

Excel Cheat Sheet

 Database Functions
Function How to use Explained Function How to use Explained

DSTDEV
Estimates standard deviation based on DAY =DAY("2023-12-25") Extracts the day from a date.
=DSTDEV(Database, "Field", Criteria)
a sample of selected database entries.

Calculates standard deviation based DAYS =DAYS("2023-12-25", "2023-01-01") Returns the number of days between
DSTDEVP =DSTDEVP(Database, "Field", Criteria)
on the entire population of selected two dates.
database entries.
DAYS360 =DAYS360("2023-01-01", "2023-12-25") Calculates the number of days
Adds numbers in a column of a list or between two dates based on a 360-day
DSUM =DSUM(Database, "Field", Criteria)
database that match conditions. year.

Estimates variance based on a sample EDATE =EDATE("2023-12-25", 1) Returns the serial number of the date
DVAR =DVAR(Database, "Field", Criteria)
of selected database entries. that is the indicated number of
months before or after the start date.
DVARP
Calculates variance based on the
=DVARP(Database, "Field", Criteria) Returns the serial number of the last
entire population of selected database EOMONTH =EOMONTH("2023-12-25", 1)
entries. day of the month before or after a
specified number of months.

HOUR =HOUR("12:30") Extracts the hour from a time.

Date & Time Functions
ISOWEEKNUM =ISOWEEKNUM("2023-12-25")
Returns the number of the ISO week
Formula How to use Explained number of the year for a given date.

DATE
Creates a date with the specified year,
=DATE(2023, 12, 25) MINUTE Extracts the minute from a time.
month, and day. =MINUTE("12:30")

DATEDIF
Calculates the number of days,
=DATEDIF("2023-1-1", "2023-12-25", "M") MONTH =MONTH("2023-12-25") Extracts the month from a date.
months, or years between two dates.

DATEVALUE
Converts a date in the form of text to a
=DATEVALUE("12/25/2023")
serial number.

Excel Cheat Sheet

 Date & Time Functions Financial Functions
Function How to use Explained Function How to use Explained

NETWORKDAYS
Returns the number of whole ACCRINT Calculates the accrued interest for a
=NETWORKDAYS("2023-01-01",
=ACCRINT("01/01/2023", "12/31/2023",

workdays between two dates. security that pays periodic interest.

"2023-12-25") 0.1, 1000, 2)

NOW
Returns the serial number of the
=NOW()
current date and time. ACCRINTM Calculates the accrued interest for a
=ACCRINTM("01/01/2023",

"12/31/2023", 0.1, 1000)

security that pays interest at maturity.
SECOND =SECOND("12:30:45") Extracts the second from a time.

AMORDEGRC =AMORDEGRC(1000, "01/01/2023",

Calculates the depreciation for each
TIME =TIME(12, 30, 45) Returns the serial number of a accounting period using a declining
particular time. "12/31/2023", 0.1, 1,

balance method.
0.2, 0.5)
TIMEVALUE =TIMEVALUE("12:30:45 PM") Converts a time in the form of text to a
serial number. AMORLINC Calculates the depreciation for each
=AMORLINC(1000, "01/01/2023",

"12/31/2023", 0.1, 1, 0.2)

accounting period using a straight-line
TODAY =TODAY() Returns the serial number of today's method.
date.
COUPDAYBS =COUPDAYBS("01/01/2023",
Calculates the number of days from
WEEKDAY =WEEKDAY("2023-12-25", 1) Returns the day of the week for a given the beginning of the coupon period to
date as a number. "12/31/2023", 2)
the settlement date.

WEEKNUM =WEEKNUM("2023-12-25", 1) Returns the week number of a given

COUPDAYS =COUPDAYS("01/01/2023",
Calculates the number of days in the
date within a year. The second
"12/31/2023", 2)
coupon period that contains the
argument specifies the day the week
settlement date.
starts (1 = Sunday, 2 = Monday).
YEAR =YEAR("2023-12-25") Extracts the year from a date. COUPDAYSNC =COUPDAYSNC("01/01/2023",
Calculates the number of days from
"12/31/2023", 2)
the settlement date to the next coupon
date.
Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, Jan 1,
1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, COUPNCD =COUPNCD("01/01/2023",
Calculates the next coupon date after
1900. "12/31/2023", 2)
the settlement date.

Excel Cheat Sheet

 Financial Functions
Function How to use Explained Formula How to use Explained
Calculates the number of coupons DOLLARFR Converts a dollar price expressed as a
COUPNUM =COUPNUM("01/01/2023",
=DOLLARFR(1.1, 32)
payable between the settlement date decimal number into a dollar price
"12/31/2023", 2)
and maturity date. expressed as a fraction.

Returns the previous coupon date DURATION =DURATION("01/01/2023",

Calculates the annual duration of a
COUPPCD =COUPPCD("01/01/2023",

before the settlement date. "12/31/2023", 0.1, 0.1, 2)

security with periodic interest
"12/31/2023", 2)
payments.

Calculates the previous coupon date EFFECT =EFFECT(0.1, 4) Calculates the effective annual interest
CUMIPMT =CUMIPMT(0.1, 10, 1000, 1, 10, 0) rate.
before the settlement date.

Calculates the cumulative principal FV =FV(0.1, 10, -100, -1000) Calculates the future value of an
CUMPRINC =CUMPRINC(0.1, 10, 1000, 1, 10, 0) investment.
paid on a loan between two periods.

FVSCHEDULE =FVSCHEDULE(1000, {0.1, 0.2, 0.3}) Calculates the future value of an initial
Calculates the depreciation of an asset
DB =DB(1000, 100, 10, 1) principal after applying a series of
for a specified period using the fixed-
compound interest rates.
declining balance method.
PV =PV(0.1, 10, -100, -1000) Calculates the present value of an
DDB
Calculates the depreciation of an asset
=DDB(1000, 100, 10, 1, 2) investment.
for a specified period using the
double-declining balance method.

DISC
Calculates the discount rate for a
=DISC("01/01/2023",

security.
"12/31/2023", 1000, 900)

DOLLARDE
Converts a dollar price expressed as a
=DOLLARDE(1.1, 32)
fraction into a dollar price expressed
as a decimal number.

Excel Cheat Sheet

 Information Functions Logical Functions
Function How to use Explained Function How to use Explained

ISNUMBER =ISNUMBER(A1) Checks if a value is a number. AND =AND(A1>1, A1<100) Returns TRUE if all arguments are
TRUE.

ISERROR =ISERROR(A1) Checks if a value is an error. IF =IF(A1>10, "Yes", "No") Returns one value if a condition is true,
another if false.

ISERR =ISERR(A1) Checks if a value is any error value Returns a value if an error is found,
IFERROR =IFERROR(A1/B1, "Error")
except #N/A. otherwise returns the result of the
formula.
ISBLANK =ISBLANK(A1) Checks if a cell is empty.
IFNA =IFNA(A1/B1, "N/A") Returns a value if the formula results
in #N/A, otherwise returns the result of
ISLOGICAL =ISLOGICAL(A1) Checks if a value is a logical value. the formula.

ISNA Checks if a value is the error value NOT =NOT(A1>1) Reverses the logic of its argument.
=ISNA(A1)
#N/A.

ISNONTEXT =ISNONTEXT(A1) Checks if a value is not text. OR =OR(A1>1, A1<100) Returns TRUE if any argument is TRUE.

ISREF =ISREF(A1) Checks if a value is a reference. XOR =XOR(A1>1, B1<5) Returns a logical exclusive OR of all
arguments.

ISTEXT =ISTEXT(A1) Checks if a value is text.

TYPE =TYPE(A1) Checks the type of a value and returns

a number representing its data type.

Excel Cheat Sheet

 Lookup & Reference Functions

Function How to use Explained Function How to use Explained

ADDRESS =ADDRESS(1, 1) Returns a reference as text to a single

cell in a worksheet. INDEX =INDEX(A1:C10, 2, 3) Returns the value of a cell in a
specified row and column.
AREAS =AREAS(A1:C10) Returns the number of areas in a
reference. INDIRECT =INDIRECT("A1") Returns a reference indicated by a text
value.
CHOOSE =CHOOSE(2, A1, C3, E9) Returns a value from a list based on an
index number. LOOKUP =LOOKUP(1, A1:A10, B1:B10) Looks up values in a vector or array.

COLUMN =COLUMN(A1) Returns the column number of a

MATCH =MATCH(100, A1:A10, 0) Searches for a value in a range and
reference.
returns its position.
COLUMNS =COLUMNS(A1:C10) Returns the number of columns in a
reference. OFFSET =OFFSET(A1, 1, 1) Returns a reference offset from a given
reference.
FORMULATEXT =FORMULATEXT(A1) Returns the formula at the given
ROW =ROW(A1) Returns the row number of a
reference as text.
reference.
GETPIVOTDATA =GETPIVOTDATA("Sales", A1) Returns data stored in a PivotTable
ROWS =ROWS(A1:C10) Returns the number of rows in a
report.
reference.

HLOOKUP =HLOOKUP("A", A1:C10, 2, FALSE)

Looks for a value in the top row of a Returns a list of unique values in a list
UNIQUE =UNIQUE(A1:A10)
table and returns a value in the same or range.
column from a row you specify.
Creates a shortcut or jump that opens VLOOKUP =VLOOKUP(B1, A1:C10, 2, FALSE) Looks up a value in a table and returns
HYPERLINK =HYPERLINK("http://example.com",
a corresponding value.
"Click for Example")
a document stored on a network
server, an intranet, or the Internet.
XLOOKUP
Searches a range or an array, and
=XLOOKUP(B1, A1:A10, C1:C10)
returns an item corresponding to the
first match it finds.

Excel Cheat Sheet

 Statistical Functions
Function How to use Explained Function How to use Explained

AVERAGE Calculates the average of a range of PERCENTILE =PERCENTILE(A1:A10, 0.5) Returns the k-th percentile of values in
=AVERAGE(A1:A10)
numbers. a range.

COUNT =COUNT(A1:A10)
Counts the number of cells that QUARTILE =QUARTILE(A1:A10, 1) Returns the quartile of a data set.
contain numbers.

RANK =RANK(A1, A1:A10) Returns the rank of a number in a list

COUNTA =COUNTA(A1:A10) Counts the number of nonblank cells. of numbers.

SMALL =SMALL(A1:A10, 1) Returns the k-th smallest value in a

COUNTBLANK =COUNTBLANK(A1:A10) Counts the number of blank cells. data set.

LARGE =LARGE(A1:A10, 1) Returns the k-th largest value in a data STDEV =STDEV(A1:A10) Estimates standard deviation based on
set. a sample.

MAX =MAX(A1:A10) Returns the maximum value in a range Calculates standard deviation based
STDEVP =STDEVP(A1:A10)
of numbers. on the entire population.

MEDIAN =MEDIAN(A1:A10) Returns the median of a range of

VAR =VAR(A1:A10) Estimates variance based on a sample.
numbers.

MIN =MIN(A1:A10) Returns the minimum value in a range Calculates variance based on the
VARP =VARP(A1:A10)
of numbers. entire population.

MODE =MODE(A1:A10) Returns the most frequently occurring

value in a range of numbers.

Excel Cheat Sheet

 Text Functions
Formula How to use Explained Formula How to use Explained

CHAR =CHAR(65) Returns the character specified by a LEN =LEN(A1) Returns the number of characters in a
number. text string.

CLEAN =CLEAN(A1) Removes all nonprintable characters LOWER =LOWER(A1) Converts text to lowercase.
from text.

CODE Returns a numeric code for the first MID Returns a specific number of
=CODE("A") =MID(A1, 2, 3)
character in a text string. characters from a text string, starting
at the position you specify.
CONCAT =CONCAT(A1, " ", B1) Combines the text from multiple
ranges and/or strings. NUMBERVALUE
Converts text to number in a locale-
=NUMBERVALUE("1,000.00")
independent manner.
CONCATENATE =CONCATENATE(A1, " ", B1) Joins several text items into one text
item.
PROPER Capitalizes the first letter in each word
=PROPER(A1)
of a text value.
DOLLAR =DOLLAR(1234.56, 2) Converts a number to text, using
currency format. Replaces part of a text string with a
REPLACE =REPLACE(A1, 1, 3, "New")
different text string.
EXACT =EXACT(A1, B1) Checks if two text strings are exactly
the same.
REPT =REPT(A1, 3) Repeats text a given number of times.
FIND =FIND("M", "Microsoft") Finds one text value within another
(case-sensitive). Finds one text value within another
SEARCH =SEARCH("m", "Microsoft")
(not case-sensitive).
FIXED =FIXED(1234.56, 2) Formats a number as text with a fixed
number of decimals.
UPPER =UPPER(A1) Converts text to uppercase.
Returns the first character or
LEFT =LEFT(A1, 3)
characters in a text string, based on Removes spaces from text except for
the number of characters you specify. TRIM =TRIM(A1)
single spaces between words.

Excel Cheat Sheet

 Web Functions
Formula How to use Explained

ENCODEURL =ENCODEURL("https://www.ibm.com") Returns a URL-encoded string.

FILTERXML =FILTERXML("<root><child>data</ Returns specific data from XML

child></root>", "//child") content by using the specified XPath.

WEBSERVICE =WEBSERVICE("http://example.com") Returns data from a web service.

Excel Cheat Sheet