- Why Microsoft Excel is so important in our daily office work or life?
- Excel Worksheet Basic Structure and Term
- Function Structure Explain with an example
- How to use Excel formulas? Or How to use Excel for formulas? How to put formulas in Excel?
- 20+ Most useful Excel formulas are?
- Excel Order of operations
- Math Operators
- Logical operators
- Useful Excel formula for Subtraction
- The Most Useful Excel Multiply Formula
- Useful Excel formulas for Percentage with Example
- MS Excel Basic and Advanced Group of Formulas and Functions
- How to use the Excel Count function? Or MS Excel – Count Functions
- The formulas of the Sum Function
- MS Excel – IF Function
- How to use the Excel Average function?
- Proper, UPPER, and lower functions are used for text formatting.
- Trim function is used to remove white space from specific cell
- Examples of left, mid, right, and len functions
- Concatenate functions used as examples
- Dynamic Data Validation using Excel
- INDEX & MATCH FUNCTIONS the alternative function of the lookup
- Choose a function in Excel
- How does it look in Excel?
- Function lookup examples
- Video tutorial of Excel formulas
Why Microsoft Excel is so important in our daily office work or life?
- It helps to quick Financial analysis of your company or business.
- 20+ Excel Formulas Every Beginner Should Know – Excel with Examples
- Excel Formulas help you quickly analyze your mass volume data.
- Know the Best Excel Formulas for Managing Your Budget
- 20+ Simple Excel Formulas Every Architect Needs to Know
- Most importantly Excel Formulas make you confident to face any kind of interview.
Excel Worksheet Basic Structure
Microsoft Excel Basic Terms
Function Structure
Function Structure Example:
How to use Excel formulas?
OR
How to use a function in Excel?
How to put a formula in Excel?
- Select a cell
- Enter an equals sign (=)
- Type the formula, and press enter.
20 + Most used important and useful Excel formulas are:
| S.L | Functions | S.L | Functions |
|---|---|---|---|
| 1. | SUM() | 11. | VLOOKUP() |
| 2. | COUNT() | 12. | CONCATENATE() |
| 3. | MAX() | 13. | PROPER() |
| 4. | MIN() | 14. | UPPER() |
| 5. | AVERAGE() | 15. | LOWER() |
| 6. | IF() | 16. | TRIM() |
| 7. | IF(AND(C2>=C4,C2<=C5),C6,C7) | 17. | LEN() |
| 8. | IF(OR(C2>=C4,C2<=C5),C6,C7) | 18-20. | LEFT(), MID(), RIGHT(), |
| 9. | INDEX() | 21. | CHOOSE() |
| 10. | MATCH() | 22. | DATA VALIDATION |
Order of operations
Excel follows a sequence called “order of operations“. First, any expressions in parentheses are evaluated. Next Excel will solve for any exponents. After exponents, Excel will perform multiplication and division, then addition and subtraction.
If the formula involves concatenation, this will happen after standard math operations. Finally, Excel will evaluate logical operators, if present.
- Parentheses
- Exponents
- Multiplication and Division
- Addition and Subtraction
- Concatenation
- Logical operators
Math Operators
The table below shows the standard math operators available in Excel:
| Symbol | Operation | Example |
| + | Addition | =2+3=5 |
| – | Subtraction | =9-2=7 |
| * | Multiplication | =6*7=42 |
| / | Division | =9/3=3 |
| ^ | Exponentiation | =4^2=16 |
| () | Parentheses | =(2+4)/3=2 |
Logical operators
| Operator | Meaning | Example |
| = | Equal to | =A1=10 |
| <> | Not equal to | =A1<>10 |
| > | Greater than | =A1>100 |
| < | Less than | =A1<100 |
| >= | Greater than or equal to | =A1>=75 |
| <= | Less than or equal to | =A1<=0 |
Useful Excel Formula for Subtraction
=2-1 // returns 1, using the subtraction formula.
The most useful Excel Multiply formula
=2*3 // returns 6 using the multiply formula.
Useful Excel formulas for Percentage
MS Excel Basic and Advanced useful Group of Formulas and Functions
Similar type functions list with their single or multiple arguments
Note:
- ? = a question mark (?) matches exactly one character.
- * = asterisk (*) matches a series of characters.
Most used Excel formulas return results by dummy data table :
How to use the Excel Count function?
COUNT()
Most used Excel formulas of count function return results by dummy data table :
There are similar types of functions but names and uses are different and argument has variety.
Note: That is, unlike COUNT which only counts numerics, it (COUNTA) also counts dates, times, strings, logical values, errors, empty string, or text.
The formulas of the Sum Function
SUM()
The SUM function usually aggregates values from a selection or reference of columns or rows from your selected range.
MS Excel formulas of sum function return results by dummy data :
MS Excel – IF Function
IF()
The IF function always returns based on the logical test. The best part of the IF formula is that you can embed formulas and functions in it.
=IF(logical_test, [value_if_true], [value_if_false])
Example: =IF(H2<33, ‘TRUE,’ ‘FALSE’) – Checks if the value at H2 is less than the value at 33. If the logic is true, let the cell value be TRUE, else, FALSE.
Here mark 85 is greater than 32 so the return result is Pass.
Nested if examples:
How to use the Excel Average function?
MAX() , MIN() & Average()
Example of max(), min(), and average() from students table.
Proper, UPPER, and lower functions are used for text formatting.
A comparable example is given below to understand these functions’ natures.
TRIM functions:
When to use Excel TRIM Function?
Excel TRIM Function – Examples
The example given above is a combination of trim and proper functions.
Examples of left, mid, right, and len functions:
Details break down to realize the specified function’s nature:
CONCATENATE functions used as examples
Dynamic Data Validation using Excel
Selection data validation menu screenshot in Excel.
The next snapshot shows a selection of references of cells.
Index & Match functions are the alternative functions of the lookup
Choose a function in Excel
As an example, let’s calculate the commission of each seller depending on their sales:
How does it look in Excel?
- value – The value to look for in the first column of a table.
- table – The table from which to retrieve a value.
- col_index – The column in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
Function lookup examples
