The Essential Microsoft Excel Formulas and Functions Cheat Sheet

=NOW

Show the date and time

=TODAY()

Show the current date without the time

=DAY(TODAY())

Show today’s date in a cell

=MONTH(TODAY())

Show current month in a cell

=TODAY()+10

Add 10 days to current date

Counting and Rounding Formulas

=SUM

Calculates the sum of a group of values

=AVERAGE

Calculates the mean of a group of values

=COUNT

Counts the number of cells in a range that contains numbers

=INT

Removes the decimal portion of a number

=ROUND

Rounds a number to a specified number of decimal places

=IF

Tests for a true or false condition

=NOW

Returns the date and time

=TODAY

Returns the date, without the time

=SUMIF

Calculates a sum from a group of values in which a condition has been met

=COUNTIF

Counts the number of cells in a range that match a criteria

=COUNTA(A1:A5)

Count the number of non-blank cells in a range

=ROUND(1.45, 1)

Rounds 1.45 to one decimal place

=ROUND(-1.457, 2)

Rounds -1.457 to two decimal places

=TRUE

Returns the logical value TRUE

=FALSE

Returns the logical value FALSE

=AND

Returns TRUE if all of its arguments are TRUE

=OR

Returns TRUE if any argument is TRUE

Unit Conversion Formulas

=CONVERT(A1,”day”,”hr”)

Converts value of A1 from days to hours

READ MORE  Is Your Ex-Hotmail Inbox Over 15GB? Delete Your Emails or Pay Up, Says Microsoft

=CONVERT(A1,”hr”,”mn”)

Converts value of A1 from hours to minutes

=CONVERT(A1,”yr”, “day”)

Converts value of A1 from years to days

=CONVERT(A1,”C”,”F”)

Converts value of A1 from Celsius to Fahrenheit

=CONVERT(A1,”tsp”,”tbs”)

Converts value of A1 from teaspoons to tablespoons

=CONVERT(A1,”gal”,”l”)

Converts value of A1 from gallons to liters

=CONVERT(A1,”mi”,”km”)

Converts value of A1 from miles to kilometers

=CONVERT(A1,”km”,”mi”)

Converts value of A1 from kilometers to miles

=CONVERT(A1,”in”,”ft”)

Converts value of A1 from inches to feet

=CONVERT(A1,”cm”,”in”)

Converts value of A1 from centimeters to inches

=BIN2DEC(1100100)

Converts binary 1100100 to decimal (100)

=ROMAN

Converts a number into a Roman numeral

Mathematics

=B2-C9

Subtracts values in the two cells

=D8*A3

Multiplies the numbers in the two cells

=PRODUCT(A1:A19)

Multiplies the cells in the range

=PRODUCT(F6:A1,2)

Multiplies the cells in the range, and mulitplies the result by 2

=A1/A3

Divides value in A1 by the value in A3

=MOD

Returns the remainder from division

=MIN(A1:A8)

Calculates the smallest number in a range

=MAX(C27:C34)

Calculates the largest number in a range

=SMALL(B1:B7, 2)

Calculates the second smallest number in a range

=LARGE(G13:D7,3)

Calculates the third largest number in a range

=POWER(9,2)

Calculates nine squared

READ MORE  How to Transfer Files from Mobile to PC and Mac (or vice versa)

=9^3

Calculates nine cubed

=FACT(A1)

Factorial of value in A1

=EVEN

Rounds a number up to the nearest even integer

=ODD

Rounds a number up to the nearest odd integer

=AVERAGE

Calculates the average

=MEDIAN

Calculates the median

=SQRT

Calculates the square root of a number

=PI

Shows the value of pi

=POWER

Calculates the result of a number raised to a power

=RAND

Returns a random number between 0 and 1

=RANDBETWEEN

Returns a random number between the numbers you specify

=COS

Calculates the cosine of a number

=SIN Returns the sine of the given angle

Calculates the sine of the given angle

=TAN

Calculates the tangent of a number

=CORREL

Calculates the correlation coefficient between two data sets

=STDEVA

Estimates standard deviation based on a sample

=PROB

Returns the probability that values in a range are between two limits

Text Formulas

=LEFT

Extracts one or more characters from the left side of a text string

=RIGHT

Extracts one or more characters from the right side of a text string

=MID

Extracts characters from the middle of a text string

=CONCATENATE

Merges two or more text strings

=REPLACE

Replaces part of a text string

=LOWER

Converts a text string to all lowercase

=UPPER

Converts a text string to all uppercase

READ MORE  Why Microsoft 365 Is Getting More Expensive for Business Customers

=PROPER

Converts a text string to proper case

=LEN

Returns a text string’s length in characters

=REPT

Repeats text a given number of times

=TEXT

Formats a number and converts it to text

=VALUE

Converts a text cell to a number

=EXACT

Checks to see if two text values are identical

=DOLLAR

Converts a number to text, using the USD currency format

=CLEAN

Removes all non-printable characters from text

Finance

=INTRATE

Calculates the interest rate for a fully invested security

=EFFECT

Calculates the effective annual interest rate

=FV

Calculates the future value of an investment

=FVSCHEDULE

Calculates the future value of an initial principal after applying a series of compound interest rates

=PMT

Calculates the total payment (debt and interest) on a debt security

=IPMT

Calculates the interest payment for an investment for a given period

=ACCRINT

Calculates the accrued interest for a security that pays periodic interest

=ACCRINTM

Calculates the accrued interest for a security that pays interest at maturity

=AMORLINC

Calculates the depreciation for each accounting period

=NPV

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

=YIELD

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

=PRICE

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

      en_USEnglish
      Select your currency
      WinKeys
      Logo
      Register New Account