Excel Formulas for Cheat Sheet
This article is the cheat sheet of formulas available in Microsoft Excel. Different Excel Formulas with respective examples is explained in the article below. This Cheat Sheet of Excel Formulas article focuses on Text functions, Statistical functions, Date and Time functions, and Mathematical Functions and many more.
Different Excel Formulas with respective examples are explained below.
#1 Text Functions in Excel
MS Excel offers a variety of string functions. Some Excel Formulas Cheat Sheet is given below. The table contains the name of the function, the meaning of the function, the syntax and the example.
| Sr. No | Function | Meaning | Syntax | Example | Explanation | |||||
| 1 | Left Function | Returns the specified number of characters from the left of the string | LEFT (string, no_of_characters) | =LEFT(B2,1) | where B2 is the cell containing the text. | |||||
| 2 | Right Function | Returns the specified number of characters from the right of the string | RIGHT (string, no_of_characters) | = RIGHT (B2,5) | where B2 is the cell containing text. | |||||
| 3 | Mid Function | Returns the specified number of characters from the string starting from the given position | MID (string, starting_position, no_of_characters) | =MID (B2, 1,4) | where B2 is the cell containing text, 1 is the starting position and 4 is the number of chars to be retrieved. | |||||
| 4 | LEN | Returns the number of characters in the given string | LEN (String_to_be_measured) | =LEN(B2) | where B2 is the cell containing text. | |||||
| 5 | Concatenate Function | Merges two given strings to form one | CONCATENEATE (String1, String2…, StringN) | =CONCATENATE (D2, F2) | where D2 and F2 are the cells to be concatenated. |
The above-explained cheat sheet of excel formulas examples is implemented in the excel worksheet shown in the following screenshot.
#2 Statistical Functions in Excel
MS Excel offers a variety of statistical functions. Some Excel Formulas Cheat Sheet is given below. The table contains the name of the function, the meaning of the function, the syntax and the example.
| Sr. No | Function | Meaning | Syntax | Example | Explanation | |||||
| 1 | Average | Returns the average of the series of values given | =AVERAGE (Value1, Value 2…, ValueN) | =AVERAGE (B2:B5) | Value1,..,valueN is the range of values | |||||
| 2 | Min | Returns the minimum value from the given range of cells. | =MIN (Value1, Value 2…, ValueN) | =MIN (B2:B5) | Value1…, valueN is the range of values | |||||
| 3 | Count | Returns the number of cells fitting the given criteria of range of worksheet cells. | =COUNT (Value1, Value 2…, ValueN) | =COUNT (B2:B5) | Value1…, valueN is the range of values | |||||
| 4 | Counta | Returns the number of non-empty cells fitting the given criteria of range of worksheet cells. | =COUNTA (Value1, Value 2…, ValueN) | =COUNTA (B2:B6) | Value1…, valueN is the range of values | |||||
| 5 | Max | Merges two given strings to form one | =MAX (Value1, Value 2…, ValueN) | =MAX (B2:B5) | Value1…, valueN is the range of values |
The above-explained cheat sheet of excel formulas examples are implemented in the excel worksheet shown in the following screenshot.
#3 Date and Time Functions in Excel
MS Excel offers a variety of date and time functions. Some Excel Formulas Cheat Sheet is given below. The table contains the name of the function, the meaning of the function, the syntax and the example.
| Sr. No | Function | Meaning | Syntax | Example | Explanation | |||||
| 1 | DATE | Returns the serial number of a specific date | =DATE (year, month, day) | =DATE (2018,12,21) | 12/21/2018 Is the result. | |||||
| 2 | NOW | Returns the current date and time | =NOW () | =NOW () | 7/20/2018 11:26 | |||||
| 3 | WEEKDAY | Returns the day of the week | =WEEKDAY(serial_no) | =WEEKDAY(B1) | 6 | |||||
| 4 | WEEKNUM | Returns the week number of the week in a year | =WEEKNUM(serial_no) | =WEEKNUM (NOW ()) | 29 | |||||
| 5 | YEAR | Returns the year in the date argument | =YEAR(serial_no) | =YEAR (NOW ()) | 2018 |
The above-explained cheat sheet of excel formulas examples is implemented in the excel worksheet shown in the following screenshot.
#4 Mathematical Functions in Excel
MS Excel offers a variety of mathematical functions. Some Excel Formulas Cheat Sheet is given below. The table contains the name of the function, the meaning of the function, the syntax and the example.
| Sr. No | Function | Meaning | Syntax | Example | Explanation | |||||
| 1 | Sum Function | Returns the sum of all the given arguments | =SUM (Value1, Value 2…, ValueN) | = SUM (A2:A5) | The addition of the all the values in the range A2:A5 is 100. | |||||
| 2 | Product Function | Returns the product of all the given arguments | =PRODUCT (Value1, Value 2…, ValueN) | =PRODUCT (A2:A5) | The multiplication of the all the values in the range A2:A5 is 24000. | |||||
| 3 | Sqrt | Returns the square root of the given number | =SQRT (Number) | = SQRT (B2) | The square root of value in B2 is 20 which is 10. The same is shown in D2 | |||||
| 4 | Ceiling | Returns the round off number up to the nearest multiple of significance. | =CEILING (Number, Significance) | =CEILING (0.5,5) | The answer is 5 as shown in E2. | |||||
| 5 | Floor | Returns the round off number down to the nearest multiple of significance. | =FLOOR (Number, Significance) | =FLOOR (0.5,1) | The answer is 0 as shown in F2. |
The above-explained cheat sheet of excel formulas examples is implemented in the excel worksheet shown in the following screenshot.
Excel Best Practices for Using Cheat Sheet of Excel Formulas
| Follow this Best Practice | By doing the following: |
| Easily change the type of reference | To switch between relative, absolute, and mixed references: |
| 1. Select the cell that contains the formula. | |
2. In the formula bar,  select the reference that you want to change. |
|
| 3. Press F4 to switch between the reference types. | |
| Quickly copy formulas | To quickly enter the same formula into a range of cells, select the range that you want to calculate, type the formula, and then press Ctrl + Enter. For example, if you type =SUM (A1:B1) in range C1:C5, and then press Ctrl+ Enter, Excel enters the formula in each cell of the range, using A1 as a relative reference. |
| Use Formula Autocomplete | To make it easier to create and edit formulas and minimize typing and syntax errors, use Formula Autocomplete. After you type an = (equal sign) and beginning letters (the beginning letters act as a display trigger), Excel displays a dynamic list of valid functions and names below the cell. |
| Use Function ScreenTips | If you aren’t familiar with the arguments of a function, you can use the function ScreenTip that appears after you type the function name and an opening parenthesis. Click the function name to view the Help topic on the function, or click an argument name to select the corresponding argument in your formula. |
Recommended Articles
This is a cheat sheet of Excel Formulas. Here we provide you with most handy excel formulas in Text Functions in Excel, Statistical Functions in Excel, Date and Time Functions in Excel and Mathematical Functions in Excel. You can learn more about Excel from the following articles –
- WEEKNUM in Excel
- Explanation of Excel Max IF Formula
- What is Exponential Smoothing in Excel?
- How to use ROW in Excel?
- How to use MONTH in Excel
- How to use MAX in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
