Updated August 16, 2023
Excel Formulas Cheat Sheet
The cheat sheet of Excel formulas is like a customized worksheet that shows you how to use different functions and formulas in Excel. It includes shortcuts to quickly execute Excel functions and instructions on combining multiple functions in your way.
It can also include more complicated formulas that may be difficult to remember or use. Overall, an Excel Formulas Cheat Sheet is a handy tool for anyone who wants to improve using Excel.
Calculations Used in the Cheat Sheet of Excel Formulas
This article on excel formulas cheat sheet will cover Excel formulas that are most frequently used in calculations.
- Text Functions in Excel
- Statistical Functions in Excel
- Date & Time Functions in Excel
- Mathematical Functions in Excel
Let’s take some examples to understand the uses and workings of these functions.
TEXT Functions in Excel
TEXT functions in Excel allow you to manage and format text within a cell and help perform various tasks, including converting dates to text, extracting specific characters from a string, and more.
To access this function, follow the below steps:
- Go to the FORMULAS tab.
- Click on TEXT functions.
A drop-down list will open.
Refer to the below screenshot.
As we can see here, several string functions are available. The most commonly used text functions are RIGHT, LEFT, MID, CONCATENATE, LOWER, LEN, etc.
Example #1
We will understand how to apply the following text functions.
- LEFT
- RIGHT
- MID
- CONCATENATE
- LEN
- LOWER
We will use the following data to apply the text function:
Please refer to the following table for the result of text functions in Excel.
|
Sr. No. |
Name | TEXT Function | Method of Function | Result | Description |
| 1 | Samuel Martin | LEFT | =LEFT(A4,3) | Sam | It counts the string from the left. It returns the specified no. of characters. |
| 2 | Ronica Brave Joyce | RIGHT | =RIGHT(A5,5) | Joyce
|
It counts the string from the right and returns the specified no. of characters. |
| 3 | Phillip Studer | MID | =MID(A6,5,9) | lip Stude | It counts the string from starting position and returns the no. of characters. Space also counts as a character. |
| 4 | Ian Smith | CONCAT | =CONCATENATE(A7,A6) | Ian SmithPhillip Studer | It merges the strings mentioned and forms them as one string. Here we have passed A7 & A6 as an argument. If one needs to add space between the contents of A7 & A6, include (” “), wherever necessary i.e., =CONCATENATE(A7,” “,A6). |
|
Ian Smith Phillip Studer
|
|||||
| 5 | Fedrick Rodger | LEN | =LEN(A8) | 14 | It counts the no. of characters and returns the length of the string. |
| 6 | Petrick Henderson | LOWER | =LOWER(A9) | Petrick Henderson | It converts the string into lowercase. |
STATISTICAL Functions in Excel
Statistical functions in Excel allow you to perform statistical analysis on data, like calculating measures of central tendency, variability, correlation, regression, and more.
To access this function, follow the below steps:
- Go to the FORMULA tab.
- Click on the More Functions option.
- Choose the Statistical Functions category.
- It will open a drop-down list of functions.
The most commonly used statistical functions are MIN, MAX, COUNT, AVERAGE, MEDIAN, etc.
Example #2
We will understand how to apply the following statistical functions.
- MAX
- MIN
- AVERAGE
- COUNT
- MEDIAN
We will use the following data for calculations:
Please refer to the table below to note the statistical function result in Excel.
|
Sr. No. |
Number Values | Max | Min | Average | Count |
Median |
| 1 | 34 |
=MAX(A4:A8)
|
=MIN(A4:A8)
|
=AVERAGE(A4:A8)
|
=COUNT(A4:A8)
|
=MEDIAN(A4:A8)
|
| 2 | 56 | 60 | 10 | 41 | 5 | 45 |
| 3 | 10 | It returns the maximum value from the list. | It returns the lower value from the list | It returns the average of the list of values | Counts the values in a list. | It first arranges the values in increasing order and then returns the mid value. If the number of values is odd, it returns the mid value. If the no.of values is even, then it takes the average of mid-two values. |
DATE & TIME Functions in Excel
Date and time functions in Excel allow you to manage and format dates and times in cells. These functions can be helpful for various tasks, such as calculating the difference between two dates, extracting the month or day from a date, adding or subtracting time from a given date, and more.
To access this function, follow the below steps:
- Go to the FORMULAS tab.
- Click on the Date & Time functions category.
It will open a drop-down list of functions, as shown below.
The most commonly used DATE & TIME functions are NOW, DATE, WEEKDAY, TODAY, WORKDAY, etc.
Let’s understand this with some examples in the below screenshot.
Example #3
We will understand how to apply the following date and time functions.
- DATE
- NOW
- TODAY
- WEEKDAY
- TIME
Please refer to the below table to understand how to use date and time functions in Excel.
|
Sr. No. |
Function | Syntax | Example | Result |
Explanation |
| 1 | DATE | DATE(year,month,day) | =DATE(1996,4,24)
|
4/24/1996
|
This function returns the serial number of a date. |
| 2 | NOW | NOW() | =NOW()
|
4/24/2023 10:12
|
This function returns the current date and time. |
| 3 | TODAY | TODAY() | =TODAY()
|
4/24/2023
|
This function returns the current date as formatted. |
| 4 | WEEKDAY | WEEKDAY(serial_no) | =WEEKDAY(D6)
|
2
|
This function returns the day of the week. |
| 5 | TIME | TIME(hour,minute, second) | =TIME(1,15,60)
|
1:16 AM
|
It converts the hour, minute, and second to an Excel serial number in time format. |
MATHEMATICAL Functions in Excel
Mathematical functions in Excel enable you to perform mathematical operations on data and carry out basic arithmetic operations like subtraction, addition, multiplication, and division and more complex operations like trigonometry, logarithms, and exponentials.
To access this function, follow the below steps:
- Go to the FORMULAS tab.
- Click on the Math & Trig functions category.
It will open a drop-down list of functions, per the screenshot below.
The most commonly used mathematical functions are SUM, SUBTOTAL, RANDBETWEEN, PRODUCT, SQRT, etc.
We will understand the working of this function with some examples.
Example #4
We will understand how to apply the following mathematical functions.
- SUM
- PRODUCT
- SUBTOTAL
- RANDBETWEEN
- SQRT
We will use the following data for calculations:
Given below is the application of the respective functions.
|
Sr. No. |
Number | SUM | PRODUCT | SUBTOTAL | RANDBETWEEN |
SQRT |
| 1 | 11 | =SUM(A4:A8) | =PRODUCT(A4:A8) | =SUBTOTAL(4,A4:A8) | =RANDBETWEEN(23,38) | =SQRT(A4) |
| 2 | 12 | 65 | 360360 | 15 | 25 | 3.31662479 |
| 3 | 13 | It returns the sum of all number values passed as an argument
|
It multiplies all the number values passed as an argument in the function | It returns the subtotal in a list.
|
It returns the random number between the passed argument values.
|
It returns the square root of a number.
|
| 4 | 14 | Here, we have passed the number values range A4:A8 as an argument. | Here we have passed the number values range A4:A8 as an argument. | Here we have passed the number values range A4:A8 as the second argument and want to see the maximum number value out of these, which comes under no. 4 passed as the first argument of this function. |
Here we have passed two values, 23 as the bottom value and 38 as the top value. This function returns any random value between them. |
Here we have passed A4 values as an argument. If we select the entire range, it will provide the square roots of every number. |
| 5 | 15 |
Additional Excel Formulas for Daily Use
|
Formula |
Data in Excel Cells | Example |
Result |
| AVERAGE | A2: 6 A3: 8 A4: 3 A5: 5 A6: 9 A7: 7 A8: 2 A9: 10 A10: 4 |
=AVERAGE(A2:A10) This formula will calculate the average values in cells A2 to A10. |
6 |
| MAX | A2: 6 A3: 8 A4: 3 A5: 5 A6: 9 A7: 7 A8: 2 A9: 10 A10: 4 |
=MAX(A2:A10)
This formula will return the highest value in cells A2 to A10. |
10 |
| MIN | A2: 6 A3: 8 A4: 3 A5: 5 A6: 9 A7: 7 A8: 2 A9: 10 A10: 4 |
=MIN(A2:A10)
This formula will return the lowest value in cells A2 to A10. |
2 |
| COUNT | A2: 6 A3: David A4: 3 A5: 5 A6: Ashley A7: 7 A8: 2 A9: 10 A10: 4 |
=COUNT(A2:A10)
This formula will count the number of cells in cells A2 to A10 that contain numbers. |
7 |
| COUNTIF | A2: 6 A3: 8 A4: 3 A5: 5 |
=COUNTIF(A2:A5,”>7″)
This formula will allow us to count the number of cells in the range A2:A5 greater than 7. |
1 |
| COUNTBLANK | A2: 6 A3: 8 A4: 3 A5: 5 A6: 9 A7: 7 A8: 2 A9: 10 A10: 4 |
=COUNTBLANK(A2:A10)
This formula will help to count the number of blank cells in cells A2 to A10. |
0 |
| IF | A2: 6 | =IF(A2>8, “Yes”, “No”)
This formula will return “Yes” if the value in cell A2 exceeds 8 and “No” otherwise. |
No |
| SUMIF | A2: 6 A3: 8 A4: 3 A5: 5 A6: 9 A7: 7 A8: 2 A9: 10 A10: 4 B2: 5 B3: 8 B4: 4 B5: 6 B6: 7 B7: 10 B8: 5 B9: 6 |
=SUMIF(A2:A10, “>5”, B2:B10)
This formula helps calculate the sum of values in cells B2 to B10, where the corresponding values in column A are more significant than 5. |
22 |
| VLOOKUP | A2: Rock A3: Paper A4: Scissor B2: 56 B3: 21 B4: 89 |
=VLOOKUP(“Paper”, A2:B4, 2, FALSE)
This formula will search for the value “Paper” in the first column (in table A2:B4) and return the value in the second column. |
21 |
Things to Remember About Excel Formulas Cheat Sheet
- If you missed or forgot any function syntax, go to the FORMULAS tab, click the Insert Function button below the screenshot, or press the SHIFT+F3. It will open a dialog box, as shown below.
And choose the function as per the requirement from the list.
- Frequently used shortcut keys are:
CTRL+X – Cut
CTRL+V – Paste
CTRL+C – Copy
CTRL+ALT+V / ALT+E+S– Paste Special
CTRL+B – Bold
CTRL+U – Underline
CTRL+I – Italic
CTRL+F – Find
CTRL+P – Print
CTRL+O – Open
CTRL+Z – Undo, etc.
Frequently Asked Questions
Q1. What are the ten basic Excel formulas?
Answer: Excel has many valuable formulas, but some of the most common, helpful, and basic ones are:
- SUM: Adds the value in several cells together.
- AVERAGE: This function computes the average of a set of cells.
- MAX: Finds the maximum value in a cell range.
- MIN: Finds the minimum value in a cell range.
- COUNT: Counts the number of cells containing numbers in a particular range.
- COUNTA: Counts the number of cells other than blank ones in a range of cells.
- IF: Tests a particular condition and returns one value if the given condition proves true and another if it turns out false.
- ROUND: A number rounds to the specified number of digits.
- CONCATENATE: Combines two or more strings of text or otherwise into a single string.
- VLOOKUP: Finds a value in the first column of a table and returns a value from another column in the same row.
Q2. How to merge cells in Excel?
Answer: To merge cells in Excel, follow these steps:
- Select the cells you need to merge. These cells should be adjacent and not contain any data you want to keep.
- Click the “Home” tab from the ribbon at the top of the screen.
- Find the “Alignment” group and click the “Merge & Center” button. This button is usually in the middle of the group.
- To center the text in the merged cell, click the “Merge & Center” button. If you’re going to keep the text aligned to the left or right, click on the arrow next to “Merge & Center” and select “Merge Across” or “Merge Cells,” depending on your preference.
- Your cells should now merge into one cell. If there were any text in the original cells, it would appear centered in the new merged cell.
Q3. What are the Ctrl shortcuts in Excel?
Answer: You can use many keyboard shortcuts in Excel to save time and increase productivity. Below are some useful shortcuts that use the Ctrl key:
- Ctrl + Y: Redo the last undone action.
- Ctrl + A: Select all cells in a worksheet.
- Ctrl + S: Save the current workbook.
- Ctrl + N: Open a new workbook.
- Ctrl + -: Delete the selected cells, rows, or columns.
- Ctrl + Shift + =: Insert a new column or row into the worksheet.
- Ctrl + Shift + $: Apply currency formatting to the selected cells.
- Ctrl + Shift + #: Apply date formatting to the selected cells.
- Ctrl + Shift + &: Apply border formatting to the selected cells.
- Ctrl + Shift + ~: Apply general number formatting to the selected cells.
Recommended Articles
This has been a guide to Excel Formulas Cheat Sheets. Here we discuss the TEXT, STATISTICAL, DATE & TIME, and MATHEMATICAL Functions in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles.
