WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Cheat Sheet of Excel Formulas

Cheat Sheet of Excel Formulas

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.

You can download this Cheat Sheet of Excel Formulas Template here – Cheat Sheet of Excel Formulas Template

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

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

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 name of the function, the meaning of the function, the syntax, and the example.

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

Cheat Sheet of Excel Formulas Mathematical Functions

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, fx 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 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 the handiest 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 –

  • Excel Max IF
  • Exponential Smoothing in Excel
  • How to use a MONTH in Excel
  • MAX in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Cheat Sheet of Excel Formulas Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More