Show Menu
Cheatography

Excel formula's functions and shortcuts by

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
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          More Cheat Sheets by holly6901

          The Times - media studies Cheat Sheet
          Research Methods Cheat Sheet
          Ethnicity and crime Cheat Sheet