The Essential Microsoft Excel Formulas and Functions Cheat Sheet
If you’ve ever used Microsoft Excel, you know it’s a powerful tool for data management and analysis. However, to unlock its full potential, you need to harness the magic of Excel formulas and functions. These can not only make your work faster but also more accurate and efficient. In this article, we’ll provide you with a handy Excel formulas cheat sheet to help you navigate the world’s favorite spreadsheet program with ease.
Excel Formulas vs. Functions: What’s the Difference?
Before diving into our cheat sheet, let’s clarify a fundamental concept: the difference between Excel formulas and functions.
- Excel Formula: A formula is an equation created by the user. You can combine cell references, mathematical operators, and constants to perform calculations and generate results. For example, “A1+B1” is a formula.
- Excel Function: A function, on the other hand, is a predefined calculation built into Excel. Functions simplify complex operations, making it easier to perform calculations and manipulate data. For instance, the function “SUM(A1:A5)” calculates the sum of values in cells A1 through A5.
Now that we’ve got that covered, let’s explore our comprehensive Excel formulas cheat sheet:
Category | Formula/Function | Description |
---|---|---|
Date and Time | =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 the current date | |
Counting and Rounding | =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 | |
Logical and Statistical | =IF | Tests for a true or false condition |
=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 | |
=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 | =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 | |
Mathematical | =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 | |
=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 | |
=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 | Calculates the sine of a 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 | =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 |
Feel free to refer to this table as a quick reference for Excel formulas and functions while working with Microsoft Excel.
Speed Up Your Work in Microsoft Excel
This Excel formulas cheat sheet is your shortcut to becoming an Excel wizard. Download and print it, and keep it handy as a reference. With these essential Excel functions at your fingertips, you’ll be able to supercharge your productivity and make the most of this versatile tool.
Don’t forget that Excel offers even more powerful features, including tools like Power Query, to further enhance your data management capabilities. Explore our other articles for additional Microsoft Excel tips and tricks, and dive deeper into the world of text and functions in Excel.