If you have ever worked on a computer or have a laptop or PC, then you must have heard about Microsoft Excel. Microsoft Excel is a spreadsheet software application designed for Windows, Mac OS, Android iOS, and iPadOS. It is widely used all over the world as it makes your work very easy.
The main function of MS Excel is to organize data in tabular format, to allow analysis, and to automate tasks using mathematical calculations such as graphs, charts, and Visual Basic programming.
Isn’t this interesting software? There are many more features in Microsoft Excel that are important for you to know. Today, we will share with you an MS Excel Formulas List PDF with the example which includes all the formulas from basic to advanced.
What are Excel Formulas?
Excel formula is a mathematical expression by which calculations are done and these formulas make mathematical calculations even easier. Formulas allow us to automate tasks and perform complex calculations quickly and efficiently. Such as multiplication, percentage, addition, subtraction of numbers, or any conditional statement.
Formulas Quick Look
- Select the cell where you can enter your value.
- Enter a value in the first cell, also in the second cell, now put an equal = sign.
- Enter the address of the cell after applying = or select the cell from the list
- Now you enter operators, such as (*,+,-,/)
- Now enter the address of the next cell.
- Now Press Enter
You can understand it very well through the image.
Formula: “=A1*B1” Here we have used the mathematics multiplication operator (*) and the formula “=A1*B1”
Formula: “=PRODUCT(A1:A7)” Now to use the PRODUCT function on the same range of data (A2:A11), use the following formula under cell B1.
Have you seen how easy it is? Similarly, you can use Excel formulas and functions to perform tasks efficiently. It completes the task without any mistakes and saves your time.
Arithmetic & Comparison Operators
Here is a list of the most commonly used and basic operators that can be used anytime to make work easier. (also you should know)
|–||Subtraction or Negative||A1-B1||Numeric Value|
|=||Equal to||A1=B1||Logical Value (TRUE or FALSE)|
|>||Greater than||A1>B1||Logical Value (TRUE or FALSE|
|<||Less than||A1<B1||Logical Value (TRUE or FALSE)|
|>=||Greater than or equal to||A1>=B1||Logical Value (TRUE or FALSE)|
|<=||Less than or equal to||A1<=B1||Logical Value (TRUE or FALSE)|
|<>||Not equal to||A1<>B1||Logical Value (TRUE or FALSE)|
Formula: “=A2>B2” Here we have used Logical Value and the result will show (TRUE or FALSE)
Excel Formulas List
We will look at some mathematical operations that can be performed on the datasheet, such as calculating sums, analysis tasks, and working with date and time values using formulas and functions.
Excel is typically used for Analysis, Data entry, Data management, Accounting, Budgeting, Data analysis, Visuals and graphs, Programming, Financial modeling, and much, much more!
1. SUM Function
The SUM() is used to add a series of numbers in an Excel sheet. Which is a commonly used function. It is useful for calculating totals, and values on a worksheet.
The syntax is =SUM(number1,[number2],[…])
Note: The SUM function adds cells in both a positive and negative range.
2. COUNT Function
COUNT() Returns the number of only numeric values in a datasheet.
The syntax is =COUNT(value1, [value2], …)
Result = 4 (This will exclude cell A3 since this formula calculates the only numeric value. Please see the image above).
3. COUNTA Function
COUNTA() Returns text and numeric values in a datasheet.
The syntax is =COUNTA(value1, [value2], …)
Result = 5 (This will Include Cell A3 since this formula calculates both text and numeric value. See Image above).
4. COUNTBLANK Function
COUNTBLANK() is a built-in function in Microsoft Excel that counts the number of blank cells within a range.
The syntax is =COUNTBLANK(range)
Result = 3 (This will calculate the number of blank cells in the range. See image).
5. AVERAGE Function
The Average function is used in Microsoft Excel and Google Spreadsheets to find the average of one or more cells or ranges.
The syntax is =AVERAGE(value1, [value2], …)
Result = 260 (This will calculate the average of the selected range of cell values.
6. MIN Function
MIN() is used in Microsoft Excel and Google Spreadsheet to get the minimum value in given cells or ranges.
The syntax is =MIN(value1, [value2], …)
7. MAX Function
MAX() is used in Microsoft Excel and Google Spreadsheet to get the maximum value in given cells or ranges.
The syntax is =MAX(value1, [value2], …)
8. LEN Function
LEN() is used in Microsoft Excel and Google Spreadsheets that return the length of a text string.
The syntax is =LEN(text) or =LEN(A1)
Cell A1 value is “JOBLESS”, which has a 7-character length. So, the result would be 7.
Note: LEN counts all characters, even spaces, and nonprinting characters.
9. TRIM Function
TRIM function is used in MS Excel and Google Spreadsheets to remove unnecessary space from cells or text.
The syntax is =TRIM(text)
see cell A1 there is some space between sentences. using TRIM Function we can remove leading, trailing, and repeated spaces in the text.
10. IF Function
IF Function in Excel is a logical function, this function checks the given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.
Syntax is =IF(logical_test, value_if_true, value_if_false)
=IF(A1>25, True, False). A value in cell A1 is 20, and a logical test is if the value is greater than 25. Then the result would be True. Else, the result would be False.