Cheat Sheet of Excel Formulas

Excel Functions Tutorials

This article is the cheat sheet of formulas available in Microsoft Excel. Different Excel Formulas with respective examples is explained in the article below. The functions in Excel range across Text functions, Mathematical functions, Logical function, Lookup functions and many more.

This Cheat Sheet of Excel Formulas article focuses on Text functions, Statistical functions, Date and Time functions and Mathematical Functions.

#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 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.

You can download the sample Excel file containing an example of Cheat Sheet of Excel Formulas Here – Cheat Sheet of Excel Formulas Template

Cheat Sheet of Excel Formulas