The Essential Microsoft Excel Formulas and Functions Cheat Sheet
Whatsapp Pinterest
Advertisement

If you’re not using Microsoft Excel formulas and functions, you’re doing it all wrong. If you learn some, they can dramatically increase the app’s functionality and speed up your workflow.

Unlock the "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

But who’s got time to study this stuff? Why not take a shortcut and use our Excel formulas cheat sheet instead?

In Excel, a formula is an equation put together by the user. A function is a pre-defined calculation built into Excel that makes it easier to write formulas.

For instance, in the cheat sheet below:

Today() is a function that gives you the current date.

Today()+10 is a formula that adds 10 days to the current date.

The terms formula and function are often used interchangeably, so we will stick to using “formulas” to avoid confusion in this basic cheat sheet. Just remember, you make your own formulas, while functions are pieces of code programmed into Excel by default.

The Microsoft Excel Formulas Cheat Sheet

Date and Time Formulas
=NOWShow 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()+10Add 10 days to current date
Counting and Rounding Formulas
=SUMCalculates the sum of a group of values
=AVERAGECalculates the mean of a group of values
=COUNTCounts the number of cells in a range that contains numbers
=INTRemoves the decimal portion of a number
=ROUNDRounds a number to a specified number of decimal places
=IFTests for a true or false condition
=NOWReturns the date and time
=TODAYReturns the date, without the time
=SUMIFCalculates a sum from a group of values in which a condition has been met
=COUNTIFCounts 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
=TRUEReturns the logical value TRUE
=FALSEReturns the logical value FALSE
=ANDReturns TRUE if all of its arguments are TRUE
=ORReturns TRUE if any argument is TRUE
Unit Conversion Formulas
=CONVERT(A1,"day","hr") Converts value of A1 from days to hours
=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
!ERROR! A1 does not contain a number or expressionConverts 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)
=ROMANConverts a number into a Roman numeral
Mathematics
=B2-C9Subtracts values in the two cells
=D8*A3Multiplies 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/A3Divides value in A1 by the value in A3
=MODReturns 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
=9^3Calculates nine cubed
=FACT(A1)Factorial of value in A1
=EVENRounds a number up to the nearest even integer
=ODDRounds a number up to the nearest odd integer
=AVERAGECalculates the average
=MEDIANCalculates the median
=SQRTCalculates the square root of a number
=PIShows the value of pi
=POWERCalculates the result of a number raised to a power
=RANDReturns a random number between 0 and 1
=RANDBETWEENReturns a random number between the numbers you specify
=COSCalculates the cosine of a number
=SIN Returns the sine of the given angleCalculates the sine of the given angle
=TANCalculates the tangent of a number
=CORRELCalculates the correlation coefficient between two data sets
=STDEVAEstimates standard deviation based on a sample
=PROBReturns the probability that values in a range are between two limits
Text Formulas
=LEFTExtracts one or more characters from the left side of a text string
=RIGHTExtracts one or more characters from the right side of a text string
=MIDExtracts characters from the middle of a text string
=CONCATENATEMerges two or more text strings
=REPLACEReplaces part of a text string
=LOWERConverts a text string to all lowercase
=UPPERConverts a text string to all uppercase
=PROPERConverts a text string to proper case
=LENReturns a text string’s length in characters
=REPTRepeats text a given number of times
=TEXTFormats a number and converts it to text
=VALUEConverts a text cell to a number
=EXACTChecks to see if two text values are identical
=DOLLARConverts a number to text, using the USD currency format
=CLEANRemoves all non-printable characters from text
Finance
=INTRATECalculates the interest rate for a fully invested security
=EFFECTCalculates the effective annual interest rate
=FVCalculates the future value of an investment
=FVSCHEDULECalculates the future value of an initial principal after applying a series of compound interest rates
=PMTCalculates the total payment (debt and interest) on a debt security
=IPMTCalculates the interest payment for an investment for a given period
=ACCRINTCalculates the accrued interest for a security that pays periodic interest
=ACCRINTMCalculates the accrued interest for a security that pays interest at maturity
=AMORLINCCalculates the depreciation for each accounting period
=NPVCalculates the net present value of cash flows based on a discount rate
=YIELDCalculates the yield of a security based on maturity, face value, and interest rate
=PRICECalculates the price per $100 face value of a periodic coupon bond

Speed Up Your Work in Microsoft Excel

You can download and print the above cheat sheet. If you keep referring to it, you’ll be an Excel wizard before you know it.

These Excel functions give you a strong foundation to dig deeper into its immense power. And remember to check out our other articles if you’d like to learn even more Microsoft Excel tips and tricks Be The Smartest Person At Work With These Excel Tricks Be The Smartest Person At Work With These Excel Tricks Excel is a powerful tool. At first glance, it looks like a bunch of boxes that you type numbers into, but it does so much more. Read More or work with text and functions in Excel Tips for Working With Text and Text Functions in Excel Tips for Working With Text and Text Functions in Excel Let's look at various ways of working with text in Excel spreadsheets. Learn several different Excel text functions and see how they can save you time. Read More .

Explore more about: Cheat Sheet, Microsoft Excel, Microsoft Office 2019, Microsoft Office 365, Spreadsheet.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Aet
    March 6, 2019 at 3:28 pm

    The "Converts value of A1 from gallons to liters" and "Calculates the sine of the given angle" formulas need proofing, but this is good stuff - I did not know most of these.