March Madness Challenge – Compete, Learn, and Win – Register Now
Microsoft Excel Cheat Sheet
This cheat sheet provides a quick reference for essential Excel functions, helping you perform calculations, analyze data, and manage spreadsheets with ease. It covers a wide range of categories, including logical, statistical, text, lookup, and financial functions, ensuring you have the tools needed to tackle diverse tasks—from simple summations to advanced financial modeling.
Each entry includes clear syntax and concise explanations, paired with practical examples to demonstrate how the function operates in real-world scenarios. Whether you're calculating averages, finding unique values, or building dynamic references with functions like VLOOKUP and INDEX, this cheat sheet simplifies Excel’s powerful functionality.
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.
Have the Dataquest Microsoft Excel Sheet at your fingertips when you need it!
Table of Contents
Common Functions
DATE, IF, VLOOKUP
Database Functions
DSUM, DCOUNT, DAVERAGE
Date & Time Functions
DATEDIF, TODAY, YEAR
Financial Functions
ACCRINT, EFFECT, FV
Information Functions
ISNUMBER, ISNA, TYPE
Logical Functions
AND, IF, NOT
Lookup & Reference Functions
INDEX, MATCH, XLOOKUP
Statistical Functions
AVERAGE, COUNT, QUARTILE
Text Functions
CONCATENATE, FIND, REPLACE
Web Functions
ENCODEURL, FILTERXML, WEBSERVICE
Common Functions
Function
How to use
Explained
AVERAGE
=AVERAGE(A1:A10)Calculates the average of a range of numbers.
CHOOSE
=CHOOSE(2, A1, C3, E9)Returns a value from a list based on an index number.
DATE
=DATE(2023, 12, 25)Creates a date with the specified year, month, and day.
DAYS
=DAYS("2023-12-25", "2023-01-01") Returns the number of days between two dates.
FIND
=FIND("M", "Microsoft")Finds the starting position of one text string within another.
IF
=IF(A1>10, "Yes", "No")Returns one value if a condition is true, another if false.
INDEX
=INDEX(A1:C10, 2, 3)Returns the value of a cell in a specified row and column.
MATCH
=MATCH(100, A1:A10, 0)Searches for a value in a range and returns its position.
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.
Database Functions
Function
How to use
Explained
DCOUNT
=DCOUNT(Database, "Field", Criteria)Counts the cells that contain numbers in a column of a list or database that match conditions.
DAVERAGE
=DAVERAGE(Database, "Field", Criteria)Averages the numbers in a column of a list or database that match conditions.
DCOUNTA
=DCOUNTA(Database, "Field", Criteria)Counts nonblank cells in a column of a list or database that match conditions.
DGET
=DGET(Database, "Field", Criteria)Extracts a single value from a column of a list or database that matches conditions.
DMAX
=DMAX(Database, "Field", Criteria)Returns the maximum value from a column of a list or database that matches conditions.
DMIN
=DMIN(Database, "Field", Criteria)Returns the minimum value from a column of a list or database that matches conditions.
DPRODUCT
=DPRODUCT(Database, "Field", Criteria)Multiplies values in a column of a list or database that match conditions.
DSTDEV
=DSTDEV(Database, "Field", Criteria)Estimates standard deviation based on a sample of selected database entries.
DSTDEVP
=DSTDEVP(Database, "Field", Criteria)Calculates standard deviation based on the entire population of selected database entries.
DSUM
=DSUM(Database, "Field", Criteria)Adds numbers in a column of a list or database that match conditions.
DVAR
=DVAR(Database, "Field", Criteria)Estimates variance based on a sample of selected database entries.
DVARP
=DVARP(Database, "Field", Criteria)Calculates variance based on the entire population of selected database entries.
Date & Time Functions
Function
How to use
Explained
DATE
=DATE(2023, 12, 25)Creates a date with the specified year, month, and day.
DATEDIF
=DATEDIF("2023-1-1", "2023-12-25", "M")Calculates the number of days, months, or years between two dates.
DATEVALUE
=DATEVALUE("12/25/2023")Converts a date in the form of text to a serial number.
DAY
=DAY("2023-12-25")Extracts the day from a date.
DAYS
=DAYS("2023-12-25", "2023-01-01")Returns the number of days between two dates.
DAYS360
=DAYS360("2023-01-01", "2023-12-25")Calculates the number of days between two dates based on a 360-day year.
EDATE
=EDATE("2023-12-25", 1)Returns the serial number of the date that is the indicated number of months before or after the start date.
EOMONTH
=EOMONTH("2023-12-25", 1)Returns the serial number of the last day of the month before or after a specified number of months.
HOUR
=HOUR("12:30")Extracts the hour from a time.
ISOWEEKNUM
=ISOWEEKNUM("2023-12-25")Returns the number of the ISO week number of the year for a given date.
MINUTE
=MINUTE("12:30")Extracts the minute from a time.
MONTH
=MONTH("2023-12-25")Extracts the month from a date.
NETWORKDAYS
=NETWORKDAYS("2023-01-01", "2023-12-25")Returns the number of whole workdays between two dates.
NOW
=NOW()Returns the serial number of the current date and time.
SECOND
=SECOND("12:30:45")Extracts the second from a time.
TIME
=TIME(12, 30, 45)Returns the serial number of a particular time.
TIMEVALUE
=TIMEVALUE("12:30:45 PM")Converts a time in the form of text to a serial number.
TODAY
=TODAY()Returns the serial number of today's date.
WEEKDAY
=WEEKDAY("2023-12-25", 1)Returns the day of the week for a give date as a number.
WEEKNUM
=WEEKNUM("2023-12-25", 1)Returns the week number of a given date within a year. The second argument specifies the day the week starts (1 = Sunday, 2 = Monday).
YEAR
=YEAR("2023-12-25")Extracts the year from a 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, 1900.
Financial Functions
Function
How to use
Explained
ACCRINT
=ACCRINT("01/01/2023", "12/31/2023", 0.1, 1000, 2)Calculates the accrued interest for a security that pays periodic interest.
ACCRINTM
=ACCRINTM("01/01/2023", "12/31/2023", 0.1, 1000)Calculates the accrued interest for a security that pays interest at maturity.
AMORDEGRC
=AMORDEGRC(1000, "01/01/2023", "12/31/2023", 0.1, 1, 0.2, 0.5)Calculates the depreciation for each accounting period using a declining balance method.
AMORLINC
=AMORLINC(1000, "01/01/2023", "12/31/2023", 0.1, 1, 0.2)Calculates the depreciation for each accounting period using a straight-line method.
COUPDAYBS
=COUPDAYBS("01/01/2023", "12/31/2023", 2)Calculates the number of days from the beginning of the coupon period to the settlement date.
COUPDAYS
=COUPDAYS("01/01/2023", "12/31/2023", 2)Calculates the number of days in the coupon period that contains the settlement date.
COUPDAYSNC
=COUPDAYSNC("01/01/2023", "12/31/2023", 2)Calculates the number of days from the settlement date to the next coupon date.
COUPNCD
=COUPNCD("01/01/2023", "12/31/2023", 2)Calculates the next coupon date after the settlement date.
COUPNUM
=COUPNUM("01/01/2023", "12/31/2023", 2)Calculates the number of coupons payable between the settlement date and maturity date.
COUPPCD
=COUPPCD("01/01/2023", "12/31/2023", 2)Returns the previous coupon date before the settlement date.
CUMIPMT
=CUMIPMT(0.1, 10, 1000, 1, 10, 0)Calculates the previous coupon date before the settlement date.
CUMPRINC
=CUMPRINC(0.1, 10, 1000, 1, 10, 0)Calculates the cumulative principal paid on a loan between two periods.
DB
=DB(1000, 100, 10, 1)Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.
DDB
=DDB(1000, 100, 10, 1, 2)Calculates the depreciation of an asset for a specified period using the double-declining balance method.
DISC
=DISC("01/01/2023", "12/31/2023", 1000, 900)Calculates the discount rate for a security.
DOLLARDE
=DOLLARDE(1.1, 32)Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number.
DOLLARFR
=DOLLARFR(1.1, 32)Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
DURATION
=DURATION("01/01/2023", "12/31/2023", 0.1, 0.1, 2)Calculates the annual duration of a security with periodic interest payments.
EFFECT
=EFFECT(0.1, 4)Calculates the effective annual interest rate.
FV
=FV(0.1, 10, -100, -1000)Calculates the future value of an investment.
FVSCHEDULE
=FVSCHEDULE(1000, {0.1, 0.2, 0.3})Calculates the future value of an initial principal after applying a series of compound interest rates.
PV
=PV(0.1, 10, -100, -1000)Calculates the present value of an investment.
Information Functions
Function
How to use
Explained
ISNUMBER
=ISNUMBER(A1)Checks if a value is a number.
ISERROR
=ISERROR(A1)Checks if a value is an error.
ISERR
=ISERR(A1)Checks if a value is any error value except #N/A.
ISBLANK
=ISBLANK(A1)Checks if a cell is empty.
ISLOGICAL
=ISLOGICAL(A1)Checks if a value is a logical value.
ISNA
=ISNA(A1)Checks if a value is the error value #N/A.
ISNONTEXT
=ISNONTEXT(A1)Checks if a value is not text.
ISREF
=ISREF(A1)Checks if a value is a reference.
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.
Logical Functions
Function
How to use
Explained
AND
=AND(A1>1, A1<100)Returns TRUE if all arguments are TRUE.
IF
=IF(A1>10, "Yes", "No")Returns one value if a condition is true, another if false.
IFERROR
=IFERROR(A1/B1, "Error")Returns a value if an error is found, otherwise returns the result of the formula.
IFNA
=IFNA(A1/B1, "N/A")Returns a value if the formula results in #N/A, otherwise returns the result of the formula.
NOT
=NOT(A1>1)Reverses the logic of its argument.
OR
=OR(A1>1, A1<100)Returns TRUE if any argument is TRUE.
XOR
=XOR(A1>1, B1<5)Returns a logical exclusive OR of all arguments.
Lookup & Reference Functions
Function
How to use
Explained
ADDRESS
=ADDRESS(1, 1)Returns a reference as text to a single cell in a worksheet.
AREAS
=AREAS(A1:C10)Returns the number of areas in a reference.
CHOOSE
=CHOOSE(2, A1, C3, E9)Returns a value from a list based on an index number.
COLUMN
=COLUMN(A1)Returns the column number of a reference.
COLUMNS
=COLUMNS(A1:C10)Returns the number of columns in a reference.
FORMULATEXT
=FORMULATEXT(A1)Returns the formula at the given reference as text.
GETPIVOTDATA
=GETPIVOTDATA("Sales", A1)Returns data stored in a PivotTable report.
HLOOKUP
=HLOOKUP("A", A1:C10, 2, FALSE)Looks for a value in the top row of a table and returns a value in the same column from a row you specify.
HYPERLINK
=HYPERLINK("http://example.com", "Click for Example")Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.
INDEX
=INDEX(A1:C10, 2, 3)Returns the value of a cell in a specified row and column.
INDIRECT
=INDIRECT("A1")Returns a reference indicated by a text value.
LOOKUP
=LOOKUP(1, A1:A10, B1:B10)Looks up values in a vector or array.
MATCH
=MATCH(100, A1:A10, 0)Searches for a value in a range and returns its position.
OFFSET
=OFFSET(A1, 1, 1)Returns a reference offset from a given reference.
ROW
=ROW(A1)Returns the row number of a reference.
ROWS
=ROWS(A1:C10)Returns the number of rows in a reference.
UNIQUE
=UNIQUE(A1:A10)Returns a list of unique values in a list or range.
VLOOKUP
=VLOOKUP(B1, A1:C10, 2, FALSE)Looks up a value in a table and returns a corresponding value.
XLOOKUP
=XLOOKUP(B1, A1:A10, C1:C10)Searches a range or an array, and returns an item corresponding to the first match it finds.
Statistical Functions
Function
How to use
Explained
AVERAGE
=AVERAGE(A1:A10)Calculates the average of a range of numbers.
COUNT
=COUNT(A1:A10)Counts the number of cells that contain numbers.
COUNTA
=COUNTA(A1:A10)Counts the number of nonblank cells.
COUNTBLANK
=COUNTBLANK(A1:A10)Counts the number of blank cells.
LARGE
=LARGE(A1:A10, 1)Returns the k-th largest value in a data set.
MAX
=MAX(A1:A10)Returns the maximum value in a range of numbers.
MEDIAN
=MEDIAN(A1:A10)Returns the median of a range of numbers.
MIN
=MIN(A1:A10)Returns the minimum value in a range of numbers.
MODE
=MODE(A1:A10)Returns the most frequently occurring value in a range of numbers.
PERCENTILE
=PERCENTILE(A1:A10, 0.5)Returns the k-th percentile of values in a range.
QUARTILE
=QUARTILE(A1:A10, 1)Returns the quartile of a data set.
RANK
=RANK(A1, A1:A10)Returns the rank of a number in a list of numbers.
SMALL
=SMALL(A1:A10, 1)Returns the k-th smallest value in a data set.
STDEV
=STDEV(A1:A10)Estimates standard deviation based on a sample.
STDEVP
=STDEVP(A1:A10)Calculates standard deviation based on the entire population.
VAR
=VAR(A1:A10)Estimates variance based on a sample.
VARP
=VARP(A1:A10)Calculates variance based on the entire population.
Text Functions
Function
How to use
Explained
CHAR
=CHAR(65)Returns the character specified by a number.
CLEAN
=CLEAN(A1)Removes all nonprintable characters from text.
CODE
=CODE("A")Returns a numeric code for the first character in a text string.
CONCAT
=CONCAT(A1, " ", B1)Combines the text from multiple ranges and/or strings.
CONCATENATE
=CONCATENATE(A1, " ", B1)Joins several text items into one text item.
DOLLAR
=DOLLAR(1234.56, 2)Converts a number to text, using currency format.
EXACT
=EXACT(A1, B1)Checks if two text strings are exactly the same.
FIND
=FIND("M", "Microsoft")Finds one text value within another (case-sensitive).
FIXED
=FIXED(1234.56, 2)Formats a number as text with a fixed number of decimals.
LEFT
=LEFT(A1, 3)Returns the first character or characters in a text string, based on the number of characters you specify.
LEN
=LEN(A1)Returns the number of characters in a text string.
LOWER
=LOWER(A1)Converts text to lowercase.
MID
=MID(A1, 2, 3)Returns a specific number of characters from a text string, starting at the position you specify.
NUMBERVALUE
=NUMBERVALUE("1,000.00")Converts text to number in a locale-independent manner.
PROPER
=PROPER(A1)Capitalizes the first letter in each word of a text value.
REPLACE
=REPLACE(A1, 1, 3, "New")Replaces part of a text string with a different text string.
REPT
=REPT(A1, 3)Repeats text a given number of times.
SEARCH
=SEARCH("m", "Microsoft")Finds one text value within another (not case-sensitive).
UPPER
=UPPER(A1)Converts text to uppercase.
TRIM
=TRIM(A1)Removes spaces from text except for single spaces between words.
Web Functions
Function
How to use
Explained
ENCODEURL
=ENCODEURL("https://www.ibm.com") Returns a URL-encoded string.
FILTERXML
=FILTERXML("<root><child>data</child></root>", "//child")Returns specific data from XML content by using the specified XPath.
WEBSERVICE
=WEBSERVICE("http://example.com")Returns data from a web service.