Microsoft Excel Formulas and Functions

Essential Microsoft Excel Formulas and Functions

If you are not using the formulas and functions of Microsoft Excel, then you are making a mistake. If you learn something and start using it, you can dramatically increase your functionality and speed up your workflow.

Excel is used extensively today globally. Excel helps preserve data in a very systematic way. This not only allows us to organize the data, but also perform detailed work on our data using simple techniques. You can do a lot of mathematics and apply formulas to large amounts of data without actually computing anything.This is the power of Excel and through this, you can significantly reduce the amount of time you would otherwise spend in the operation of data if you did not learn about Excel’s formulas.

By reading this article, you will be able to know about various essential formulas of Excel. In this article, you will get a list of Excel formulas, which you can customize by adding more formulas to suit your needs.

But who gets time to study it? So why not use a shortcut and download this list in PDF format and save it in your PC, so that you can open it and use this cheat sheet whenever needed.

Now let us take an example to know how this Cheat Sheet will help you-

For example, in the cheat sheet below:

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

Today () + 5 is a function that adds 5 days to the current date.

The term formula and function are often used interchangeably, so we will use the “formula” to avoid confusion in this basic chit sheet. Just remember, you create your own formula, while functions are pieces of code programmed in Excel by default.

Microsoft Excel Formulas Cheat Sheet

Formula => Description

Date and Time Formula

= NOW => shows date and time.
June 26, 1900 => shows the current date without time.
! ERROR! unexpected closing bracket => shows today’s date in a cell.
= MONTH (TODAY ()) => shows the current month in a cell.
June 27, 1900 => shows adding 10 days to the current date.

Counting and Rounding Formulas

= SUM => sums the group of values.
= AVERAGE => calculates the mean of the group of values
= COUNT => counts the number of cells in the range that contain numbers
= INT => extracts the decimal part of a number
= ROUND => Rounds a decimal place to a specified number
= IF

= SUM => sums the group of values.
= AVERAGE => calculates the mean of the group of values
= COUNT => counts the number of cells in the range that contain numbers
= INT => extracts the decimal part of a number
= ROUND => Rounds a decimal place to a specified number
= IF => for true or false case
= NOW => shows the current date and time
= TODAY => shows date, without time
= SUMIF => sum the values ​​from the group that have fulfilled a condition
= COUNTIF => counts the number of cells in a range that meet a criterion
= COUNTA (A1: A5) => calculates the number of non-blank cells in a range
1.5 => Rounds 1.45 to one decimal places
-1.46 => Rounds -1.457 to two decimal places
= TRUE => Returns the logical value TRUE
= FALSE => logical value returns FALSE
= AND => Returns TRUE if all arguments are TRUE
= OR => Returns TRUE if one of the arguments is TRUE

for true or false case
= NOW => shows the current date and time
= TODAY => shows date, without time
= SUMIF => sum the values ​​from the group that have fulfilled a condition
= COUNTIF => counts the number of cells in a range that meet a criterion
= COUNTA (A1: A5) => calculates the number of non-blank cells in a range
1.5 => Rounds 1.45 to one decimal places
-1.46 => Rounds -1.457 to two decimal places
= TRUE => Returns the logical value TRUE
= FALSE => logical value returns FALSE
= AND => Returns TRUE if all arguments are TRUE
= OR => Returns TRUE if one of the arguments is TRUE

Unit Conversion Formulas

= CONVERT (A1, “day”, “hr”) => converts the value of A1 from days to hours.
= CONVERT (A1, “hr”, “mn”) => converts the value of A1 in hours to minutes.
= CONVERT (A1, “yr”, “day”) => converts values ​​of A1 from years to days
= CONVERT (A1, “C”, “F”) => converts A1 values ​​from Celsius to Fahrenheit
= CONVERT (A1, “mi”, “km”) => converts values ​​of A1 from miles to kilometers
= CONVERT (A1, “km”, “mi”) => converts values ​​of A1 from kilometers to miles
= CONVERT (A1, “in”, “ft”) => converts values ​​of A1 in feet to feet
= CONVERT (A1, “cm”, “in”) => converts values ​​of A1 from centimeters to inches
= BIN2DEC (1100100) => converts binary 1100100 into decimal (100)
= ROMAN => converts a number into a roman number

Mathematics

= A2-D9 => Subtracts values ​​of two cells.
= D8 * A3 => Multiplies the value of two cells.
= PRODUCT (A1: A19) => multiplies cells in range
= PRODUCT (F6: A1,2) => multiplies the cells in the range, and multiplies the result by 2
= A1 / A3 => Divides A1 from A3.
= Gives => remainder from MOD division
= MIN (A1: A8) => calculates the smallest number in a range.
= MAX (C27: C34) => calculates the largest number in a range.
= LARGE (G13: D7,3) => calculates the third largest number in a range.
= POWER (9,2) => calculates nine squares
= 9 ^ 3 => calculates nine cube
= FACT (A1) => Finds the Factorial of the value of A1.
= EVEN => rounds a number to the nearest even integer
= ODD => rounds a number to the nearest odd integer
= AVERAGE => calculates the average
= MEDIAN => Median / Calculates Middle
= SQRT => calculates the square root of a number
= PI => denotes the value of pi
= POWER => calculates the result of a number that is raised to 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 => shows the sine of the given angle calculates the sine of the given angle.
= SMALL (B1: B7, 2) => calculates the second smallest number in a range.
= 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 => gives the probability that values ​​in a range are between two ranges.

Text Formulas

= LEFT => removes one or more characters from the left side of the text string
= MID => extracts the character from the middle of a text string
= CONCATENATE => adds two or more text strings
= REPLACE => replaces part of text strings
= LOWER => converts all text strings to lowercase
= UPPER => converts all text strings to uppercase
= PROPER => Converts text string to proper case
= LEN => shows the length of the text string
= REPT => repeats the text to the given number
= TEXT => creates the format of the number and converts it to text
= VALUE => converts a text cell to a number
= VALUE => Converts text cell to number
= EXACT => checks whether two text values ​​are the same
= DOLLAR => converts the number to text, using USD currency format
= CLEAN => Removes all non-printable characters from the text

Finance

= INTRATE => calculates the interest rate for fully invested security.
= EFFECT => calculates effective annual interest rate
= FV => calculates the future value of an investment
= RIGHT => Removes one or more characters to the right of the text string

= FVSCHEDULE=> calculates the future value of an initial principal after applying a series of compound interest rates
= PMT => calculates the total payment (loan and interest) on the debt security
= IPMT => calculates the interest payment for the investment for a fixed period.
= ACCRINT => calculates the interest earned for the security that pays the periodic interest
= AMORLINC => calculates depreciation for each accounting period
= NPV => calculates the net present value of cash flows based on discount rate
= YIELD => calculates yield of a security based on maturity, face value and interest rate
= ACCRINTM => calculates interest earned for a security that pays interest at maturity

• Ms excel formulas

• Advanced Excel 10 Formulas You Must Know 2021

• What is coding? How to learn coding in 2021

• SSC CHSL TYPING TEST PART-4