Cheat Sheet Of Excel Formulas

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

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.

  • 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:

  1. Text Functions in Excel
  2. Statistical Functions in Excel
  3. Date and Time Functions in Excel
  4. 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. NoFunctionMeaningSyntaxExampleExplanation
1LEFT function in ExcelReturns the specified number of characters from the left of the stringLEFT (string, no_of_characters)=LEFT(B2,1)Where B2 is the cell containing the text.
2RIGHT function in ExcelReturns the specified number of characters from the right of the stringRIGHT (string, no_of_characters)= RIGHT (B2,5)Where B2 is the cell containing text.
3MID function in ExcelReturns the specified number of characters from the string starting from the given positionMID (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.
4LENReturns the number of characters in the given stringLEN (String_to_be_measured)=LEN(B2)Where B2 is the cell containing text.
5CONCATENATE function in ExcelMerges two given strings to form oneCONCATENATE (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.

text functions

#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. NoFunctionMeaningSyntaxExampleExplanation
1AVERAGEReturns the average of the series of values given=AVERAGE (Value1, Value 2..., ValueN)=AVERAGE (B2:B5)Value1,.., value is the range of values
2MINReturns the minimum value from the given range of cells.=MIN (Value1, Value 2..., ValueN)=MIN (B2:B5)Value1..., valueN is the range of values
3COUNTReturns 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
4COUNTAReturns 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
5MAXMerges 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.

Statistical Function

#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. NoFunctionMeaningSyntaxExampleExplanation
1DATEReturns the serial number of a specific date=DATE (year, month, day)=DATE (2018,12,21)12/21/2018 Is the result.
2NOWReturns the current date and time=NOW ()=NOW ()7/20/2018 11:26
3WEEKDAYReturns the day of the week=WEEKDAY(serial_no)=WEEKDAY(B1)6
4WEEKNUMReturns the week number of the week in a year=WEEKNUM(serial_no)=WEEKNUM (NOW ())29
5YEARReturns 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.

date and time functions

#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. NoFunctionMeaningSyntaxExampleExplanation
1SUM function in ExcelReturns 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.
2PRODUCT functionReturns 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.
3SQRTReturns 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
4CEILINGReturns 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.
5FLOORReturns 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.

Cheat Sheet of Excel Formulas Mathematical Functions

Excel Best Practices For Using Cheat Sheets Of Excel Formulas

Follow this Best PracticeBy 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)

1. Name some common Basic Excel formulas included in Cheat Sheet of Excel Formulas.

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.

2. What are the steps to merge cells in Excel?

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.
Excel Formulas - Merger & Center
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.
Excel Formulas - Merge cell
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.

3. Name some Ctrl shortcuts found in the Cheat Sheet of Excel Formulas.

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: -