In this Blog, we will be discussing Microsoft Excel Shortcut keys.
DATE AND TIME FORMULAS
| =NOW() | Display the current date and time. |
| =TODAY() | Display the current date without the time. |
| =DAY(TODAY()) | Display the current day of the month (1 – 31). For example, if the current date is 4-09-2022, this formula returns 4. |
| =MONTH(TODAY()) | Display the current month of the year (1 – 12). For example, if the current month is Sept, this formula returns 9. |
| =TODAY()+7 | Add 5 days to the current date. For example, if the current date is 4-09-2022, this formula returns 11-09-2022. |
COUNTING AND ROUNDING FORMULAS
| =SUM(A1:A10) | Add the values in cells A1:A10. |
| =AVERAGE( A1:A10) | Calculates the average in cells A1:A10. |
| =COUNT(A1:A10) | 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 the value of A1 from Celsius to Fahrenheit |
| =CONVERT(A1,”tsp”,”tbs”) | Converts the 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 Formula
| =B2-C9 | Subtracts values in the two cells |
| =D8*A3 | Multiplies the numbers in the two cells |
| =PRODUCT(A1:A5) | Multiplies the cells in the range |
| =PRODUCT(F6: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 the 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 Formulas
| =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 |
