The Essential Microsoft Excel Formulas and Functions Cheat Sheet

0
676
The Essential Microsoft Excel Formulas and Functions Cheat Sheet

Here we can see, “The Essential Microsoft Excel Formulas and Functions Cheat Sheet”

Download this Excel formula cheat sheet to require a shortcut through the world’s favorite spreadsheet program.

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

But who’s got time to review 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 may be a pre-defined calculation built into Excel that creates it easier to write down formulas.

Also See:  Digital Minimalism: Tips for Simplifying Your Online Life

For instance, within 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 performance are often used interchangeably, so we’ll stick with using “formulas” to avoid confusion during this basic cheat sheet. Just remember, you create your formulas, while functions are pieces of code programmed into Excel by default.

Table of Contents

The Microsoft Excel Formulas Cheat Sheet

 

Date and Time Formulas
 
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
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
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
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
Also See:  The HTML Essentials Cheat Sheet: Tags, Attributes, and More

Speed Up Your add Microsoft Excel

You can download and print the above cheat sheet. If you retain about it, you will be an Excel wizard before you recognize it.

These Excel functions offer you a robust foundation to dig deeper into its immense power. Do not forget that Excel also has excellent in-built tools like Power Query. And remember to see out our other articles if you want to find out even more Microsoft Excel tips and tricks or work with text and functions in Excel.

Conclusion

I hope you found this guide useful. If you have any questions or comments, don’t hesitate to use the form below.

User Questions:

  1. What is a formula cheat sheet?

The cheat sheet of Excel formulas and performance is usually a customized worksheet with all those function details, shortcut keys to execute any function or procedures, a custom thanks to using 2 or more parts, and guidelines to use them.

  1. Can I teach myself Excel?

You can teach yourself everything from essential Excel functions to complex programming using readily available or accessible online resources. You’ll take online university courses in Excel or cash in on the various online tutorials and downloadable course guides.

  1. Can I learn Excel during a day?

It’s impossible to find out Excel daily or every week, but if you set your mind to understanding individual processes one by one, you will soon find that you have a working knowledge of the software.

Also See:  Grab This Free LibreOffice Impress Cheat Sheet
  1. A guide to Excel for Finance

A guide to Excel for Finance from finance

5.PDF or link to an honest list of formulas, functions, shortcuts – does anybody have one?

PDF or link to a good list of formulas, functions, shortcuts – does anybody have one? from excel