What Is Cheat Sheet Of Excel Formulas?
A cheat sheet of Excel formulas is a customized guide that lists the most frequently used functions and formulas and the basic information required for quick reference. It includes details such as syntax, a brief description of the formulas, and their examples.
Users can create an editable cheat sheet of Excel formulas they use in their daily Excel-based tasks, which will help them complete their activities faster.
For example, if we typically use specific mathematical and statistical functions in Excel in our periodic tasks. Then we can create a cheat sheet containing all the information required to quickly refer to the mathematical and statistical functions and use them correctly in Excel.
And the below worksheet shows the example formulas provided in the above cheat sheet for mathematical and statistical functions in Excel, implemented for the given set of values.
While cell C3 and cell range C7:C12 contain the source data, cells D3:F3 contain the three Excel mathematical functions: SIN function, COS function, and TAN function. And cells D7 and E7 contain the Excel Statistical functions: MEDIAN function and STDEV.P function.
Table of contents
Key Takeaways
- The cheat sheet of Excel formulas is a handy guide for looking up inbuilt functions and formulas used frequently in workbooks.
- A cheat sheet must include details such as the listed functions’ descriptions and syntaxes. And adding keyboard shortcuts, sample formulas, and the corresponding explanation to the cheat sheet makes it more resourceful.
- The Excel formulas cheat sheet is customizable. We can create different function categories, add more functions to the list and remove the unwanted ones. And we can add supplementary information, such as the functions’ return value formats and scenarios when the functions return error values.
- Users can use the Excel formulas cheat sheet to have all the information at one location about the functions they use daily to perform smart and error-free work.
Excel Formulas For Cheat Sheet
We can create a cheat sheet of Excel formulas for the following functions:
- Text Functions in Excel
- Statistical Functions in Excel
- Date and Time Functions in Excel
- Mathematical Functions in Excel
While we can have cheat sheets for complex formulas, such as advanced Statistical functions in Excel, we shall see cheat sheets for the more common functions in this article.
#1 – Text Functions In Excel
The Text functions category includes inbuilt functions to manage text strings.
And consider that we typically use Excel Text functions: LEN function, LEFT function, RIGHT function, CONCAT, and TRIM in our daily tasks. So, we can prepare a cheat sheet for the abovementioned Text functions, as shown below.
The cheat sheet contains the function name, a brief description and the syntax. Also, it includes the Text functions in Excel with examples and explanations.
We can look up the formulas from the Example column in the cheat sheet to apply the required function in a target cell. And then, we can enter the noted formula in the required cell by supplying the appropriate argument values to obtain the desired output.
For example, if we must apply the Excel LEN function in cell C4 of our worksheet. And the source data is in cell B4. Then, we can refer to the Text functions cheat sheet, look up the corresponding formula in the Example column, and use it in cell C4. And then, press Enter to execute the formula and view the output in cell C4 for the given source data.
Further, using the cheat sheet, we can enter the LEFT, RIGHT, CONCAT, and TRIM function formulas in cells D4:G4.
Likewise, we can add more Text functions in Excel with examples to the cheat sheet or remove unnecessary ones to suit our requirements.
#2 – Statistical Functions In Excel
We can prepare a cheat sheet of Excel formulas for Statistical functions.
For example, the below cheat sheet lists and explains the most frequently used Excel Statistical functions: COUNT function, LARGE function, MAX function, MIN function, and SMALL function.
While we can create a cheat sheet for advanced Statistical functions in Excel, the above one is for simpler ones.
And as mentioned in the previous section, we can check the formulas in the above cheat sheet to apply the required function in a worksheet.
For example, we must apply the Excel COUNT function in cell C4 for the source data in the cell range B4:B11. Then, we can check the COUNT() formula in the above cheat sheet and use it in cell C4. And then, press Enter to execute the formula and view the outcome.
Likewise, we can apply the LARGE, MAX, MIN, and SMALL function formulas in cells D4:G4 in the required worksheet, as shown above.
#3 – Date And Time Functions In Excel
We can face issues when using date and time functions in Excel since understanding the data formats of the values these functions return can be challenging.
Thus, a cheat sheet of Excel formulas containing the commonly-used Date & Time functions can be resourceful in such a scenario.
For example, if we need to frequently use the Excel Date & Time functions: DATE function, DAY function, MONTH function, WORKDAY function, and WEEKNUM, in our project.
Then, we can prepare a cheat sheet using date and time functions in Excel, which includes all the critical information about the required functions.
So, the cheat sheet of Excel formulas for the required Date & Time functions ensures referring to and applying them in a worksheet is more straightforward.
Also, we can check the function description in the cheat to be well-informed about the expected output format for each function.
For example, the Excel DATE function description suggests the function output is the serial number equivalent of the specified date. But, when we refer to the DATE() formula in the cheat sheet and apply it in a worksheet with the required arguments, we see the output in the Short Date format in Excel, as shown below.
Thus, the cheat sheet helps us to quickly verify and understand the function output format Excel considers at the backend for calculations.
#4 – Mathematical Functions In Excel
While there are many mathematical functions in Excel, the below cheat sheet of Excel formulas is for the most common mathematical functions we use regularly.
The cheat sheet of Excel formulas lists the Excel mathematical functions: SUM function, PRODUCT function, MOD function, POWER function, and ROUND function.
We can quickly refer to their syntax and example formulas in the cheat sheet to avoid typo errors while using the mathematical functions.
For example, cells H5:L5 in the below-shown worksheet contain the formulas listed in the Example column of the cheat sheet. Each function uses the specified cell ranges and references of the given source data in the worksheet to return the desired results.
Excel Best Practices For Using Cheat Sheet Of Excel Formulas
The best practices for using the cheat sheet of Excel formulas are as follows:
- Easily change the cell or range reference type: Once we enter a formula in a cell, we can select the specified cell or range reference in the formula and press F4 to change the reference type to absolute, mixed, or relative.
- Quickly copy a formula in the required cell range: We can select a cell range and enter the formula we wish to apply in the entire cell range. And then, press the key combination Ctrl + Enter to view the formula executed in all the cells in the chosen range.
- Utilize the Formula Autocomplete option: The Formula Autocomplete option helps avoid typos and syntax errors. So, when we wish to enter a formula in a cell or range, type the ‘=’ sign and the first or first few letters of the required function. Next, Excel will display the list of functions starting with the specified characters. And then, we can double-click the required function from the list to enter it in the target cell.
- Utilize the ScreenTips option: We can use the ScreenTips option when unsure about a formula. We can view the ScreenTips hovering over the cell in a worksheet when we type the ‘=’ sign, the function name, and the opening bracket in the specific cell.
Further, we can click the function name in the hovering message to check the corresponding Microsoft support page to know the function better. Also, clicking the argument in the hovering message will select the corresponding argument value in the entered formula.
Important Things To Note
- When we create a cheat sheet of Excel formulas, we must ensure the entered function syntaxes and example formulas are correct. Otherwise, we may end up referring to incorrect formulas from the cheat sheet, and using them will give incorrect output or errors in Excel.
- We should use the Excel formulas cheat sheets only to refer to the critical details about the required functions and formulas. Avoid copying and pasting the formulas directly from them into the target cells, as it can result in incorrect output.
Frequently Asked Questions (FAQs)
We can combine two Text functions in Excel using a cheat sheet containing the required Text functions.
For example, we must combine two Text functions, CONCAT and LEN, to create a formula in cell D4 that displays the total characters in the specified book name as a comment.
Then the steps are as follows:
Step 1: Create a cheat sheet containing the Text functions CONCAT and LEN, as shown below:
The cheat sheet must contain the functions, their descriptions, syntaxes, and example formulas for reference.
Step 2: Look up the CONCAT() syntax and formula from the cheat sheet for reference. And then, choose cell D4 and enter the CONCAT().
Step 3: Enter the first argument value and a comma.
Step 4: We must enter the LEN() to display the total characters in the specified text. And for that, refer to the cheat sheet for the LEN() syntax and example formula.
Next, click inside the target cell to place the cursor after the comma and enter the observed LEN() formula with the required argument value. And enter a comma.
And then, complete the CONCAT() by providing the remaining argument values, separated by commas.
Step 5: Press Enter to view the required output in the target cell.
Thus, in this way, we can prepare a cheat sheet containing the required functions’ details. And then, we can use it to use and combine any of the listed functions in a worksheet.
There are 110 Statistical functions in Excel.
And the ideal way to use them efficiently is to make a cheat sheet of the most commonly used Statistical functions. And the sheet must include details such as the functions’ description and syntax, which will help us refer to them quickly.
We can apply the current date and time function in Excel using the following steps:
1. Create a cheat sheet containing the Date & Time functions, DATE, TIME, and NOW.
And the sheet must contain all the details, such as the functions’ definition, syntax, and example formula for reference.
2. Choose a target cell in the required worksheet to view the required output.
3. Go to the cheat sheet and refer to the example formula of the DATE().
4. Go to the active worksheet and enter the noted DATE() formula in the target cell.
5. Supply the current year, month, and date as the argument values to the DATE() and press Enter to view the current date in the target cell.
6. Repeat steps 2 to 5 to apply and execute the TIME() in a target cell in the required worksheet, except we must refer to the TIME() example formula in the cheat sheet. And update the current hour, minute, and second, as the TIME() argument values to view the current time.
7. Repeat steps 2 to 5 to apply and execute the NOW() in a target cell in the required worksheet, except we must refer to the NOW() example formula in the cheat sheet. And then, use it in the target cell to exhibit the current date and time in the same cell.
Download Template
This article must be helpful to understand the Cheat Sheet Of Excel Formulas, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Cheat Sheet Of Excel Formulas. We create it for basic Excel functions with examples & explain the best practices to use them. You can learn more from the following articles –
Leave a Reply