Cheatography

# Excel formula's functions and shortcuts by holly6901

### Date and time formulas

 =NOW Show the date and time =TODAY() Show the current date without the time =DAY(T­ODAY)) 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 =COUNT­A(A­1:A5) Count the number of non-blank cells in a range =ROUND­(1.4­57,2) Rounds 1.45 to one decimal place =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

### 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 =FVSCH­EDULE Calculates the future value of an initial principal after applying a series of compound interest rates =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 deprec­iation 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 =PMT Calculates the total payment (debt and interest) on a debt security

### Unit conversion formulas

 =CONVE­RT(­A1,­"­day­"­,"hr­") Converts value of A1 from days to hours =CONVE­RT(­A1,­"­hr",­"­mn") Converts value of A1 from hours to minutes =CONVE­RT(­A1,­"­yr",­"­day­") Converts value of A1 from years to day =CONVE­RT(­A1,­"­C","F­") Converts value of A1 from Celsius to Fahrenheit =CONVE­RT(­A1,­"­tsp­"­,"tb­s") Converts value of A1 from teaspoons to tables­poons =CONVE­RT(­A1,­"­gal­"­,"l") Converts value of A1 from gallons to litres =CONVE­RT(­A1,­"­mi",­"­km") Converts value of A1 from miles to kilometres =CONVE­RT(­A1,­"­km",­"­mi") Converts value of A1 from kilometres to miles =CONVE­RT(­A1,­"­in",­"­ft") Converts value of A1 from inches to feet =CONVE­RT(­A1,­"­cm",­"­in") Converts value of A1 from centim­etres to inches =BIN2D­EC(­110­0100) Converts binary 1100100 to decimal (100) =ROMAN Converts a number into a Roman numeral

### 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 =CONCA­TENATE 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-pr­intable characters from text

### Mathem­atics

 =B2-C9 Subtracts values in the two cells =D8*A3 Multiplies the numbers in the two cells =PRODU­CT(­A1:A19) Multiplies the cells in the range PRODUC­T(F­6:A1,2) Multiplies the cells in the range, and multiplies the result by 2 =A1/A3 Divides value in A1 by the value in A3 =MOD Returns the remainder from division =MIN(A­1:A8) Calculates the smallest number in a range =MAX(C­27:C34) Calculates the largest number in a range =SMALL­(B1­:B7,2) Calculates the second smallest number in a range =LARGE­(G1­3: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 10 =RANDB­ETWEEN Returns a random number between the numbers you specify =COS Calculates the cosine of a number =SIN Calculates the sine of the given angle =TAN Calculates the tangent of a number =CORREL Calculates the correl­ation coeffi­cient between two data sets =STDEVA Estimates standard deviation based on a sample =PROB Returns the probab­ility that values in a range are between two limits