Cheat Sheet Of Excel Formulas
Last Updated :
-
Blog Author :
Edited by :
Reviewed by :
Table Of Contents
What Is Cheat Sheet Of Excel Formulas?
Cheat Sheet of Excel Formulas is a one-point destination customized sheet, so to speak, for Excels most important or frequently used formulas. It contains a list of Excelās inbuilt formulas, shortcuts, and instructions to combine two or more functions, for easy access and use.
Different Excel formulas with respective examples are explained in the article below, and it focuses on TEXT, STATISTICAL, DATE & TIME, MATHEMATICAL functions, and many more.
Table of contents
- The Cheat Sheet of Excel Formulas acts like an index or dictionary that helps us use the frequently or commonly used formulas, functions, and keyboard shortcuts, to use easily.
- The Cheat Sheet also contains the Keyboard Shortcuts that come in handy when itās hard to remember the path that leads to perform those specific functions on the Excel ribbon. It also helps to save time and increase productivity.
- As per our requirement, we can introduce or add as many formulas or functions, such as Text, Statistical Functions, Date & Time,Mathematical Functions, and shortcuts.
Excel Formulas For Cheat Sheet
The Excel Formulas for Cheat Sheet available are as follows:
- Text Functions in Excel
- Statistical Functions in Excel
- Date and Time Functions in Excel
- Mathematical Functions in Excel
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 functionās name, meaning, syntax, example, and explanation.
| Sr. No | Function | Meaning | Syntax | Example | Explanation |
|---|---|---|---|---|---|
| 1 | LEFT function in Excel | 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 in Excel | 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 in Excel | 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 in Excel | Merges two given strings to form one | CONCATENATE (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 functionās name, meaning, syntax, example, and explanation.
| 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,.., value 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 a 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 a 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 is 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 functionās name, meaning, syntax, example, and explanation.
| 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 functionās name, meaning, syntax, example, and explanation.
| Sr. No | Function | Meaning | Syntax | Example | Explanation |
|---|---|---|---|---|---|
| 1 | SUM function in Excel | Returns the sum of all the given arguments | =SUM (Value1, Value 2..., ValueN) | = SUM (A2:A5) | The addition of 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 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 the 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 Sheets 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. Select the reference you want to change in the formula bar. 3. Press F4 to switch between the reference types.. |
| Quickly copy formulas | To enter the same formula into a range of cells, select the range you want to calculate, type the formula, and 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 range, using A1 as a relative reference. |
| Use Formula Autocomplete | For easily creating the formulas, and to reduce typo or syntax errors, we must use Formula Autocomplete. For example, when we type = (equal sign) and the first letter of the functions name, Excel displays a list of inbuilt functions that start with that alphabet. |
| Use Function ScreenTips | When we get confused with the function or the formulas arguments, we can use the function ScreenTip or the argument prompts, that pops up when we type the function name and open brackets. Then, 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. |
Important Things To Note
- The formulas entered in the Cheat Sheet must be with the correct syntax. Else, we may get an error when using it for calculations.
- When we use the shortcuts or formulas from the sheet, we must remember the sheet is for our reference. Therefore, look up the required details and use them, but never copy-paste.
Frequently Asked Questions (FAQs)
Some of the Basic formulas one may use commonly in Excel are:
ā¢ SUM - Finds the total of the selected numeric cell values.
ā¢ AVERAGE - Finds the total of the selected numeric cell values.
ā¢ MAX - In a selected numeric cell range, it finds the Maximum cell value.
ā¢ MIN - In a selected numeric cell range, it finds the Minimum cell value.
ā¢ COUNT - In a selected numeric cell range, it counts only the cells with numeric values.
Please note to merge cells,
ā¢ The cells should be blank or empty, else we may lose data.
ā¢ The cells should be adjacent, either to the right, left, top, or bottom.
Next, follow the path given below to merge cells in Excel:
First, choose the cells we want to merge - select the āHomeā tab - go to the āAlignmentā group - click the āMerge & Centerā option, as shown below.
If we want the merged alignment elsewhere to keep the text aligned to the left or right, we have a few other options when we click the āMerge & Centerā option drop-down, such as āMerge Acrossā or āMerge Cellsā, and choose as desired, as shown below.
Finally, the selected cells will merge into one cell. If there were any text in the original cells, it would appear centered in the new merged cell.
Some Ctrl shortcuts that may be found in the Cheat Sheet are:
ā¢ Ctrl+S - It helps us save the changes while working on Excel.
ā¢ Ctrl+X - It helps us cut data either to remove/delete, or to paste it elsewhere.
ā¢ Ctrl+C - It helps us copy the data either to move, or to paste it elsewhere.
ā¢ Ctrl+V - It helps us paste the data that was just cut or copied.
ā¢ Ctrl+Z - It helps us undo the data just added.
ā¢ Ctrl+Y - It helps us redo the data that was just undone.
ā¢ Ctrl+S - It helps us save the changes while working on Excel.
ā¢ Ctrl+Shift+=: It helps us to insert a new column/row into the worksheet.
Download Template
This article must help understand Cheat Sheet of Excel Formulas with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
This article is a guide to Cheat Sheet of Excel Formulas. We discuss commonly used formulas, shortcuts, saved in cheat sheet, examples & downloadable template. You can learn more about Excel from the following articles: -
