This guide will provide an overview and a list of basic

**useful**

**Excel Formulas/functions used with examples**, where you will find the following.

- 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

=A1+A2+A3 // If each cell value is 3 then

**returns**

**9**

Relative and absolute references

=A1 // relative reference

=$A$1 // absolute reference.

## Microsoft Excel Basic Terms

Formulas and Functions.

**What is a formula in Excel?**

A formula in Excel is an expression that returns a specific result.

For example: =1+2 // returns 3.

Note: all formulas in Excel must begin with an equals sign (=).

**What is a function in Excel?**

Functions are predefined

**formulas**in Excel. You probably know the SUM function, which returns the sum of given references:

For example: =SUM(A1:A3).

The function returns sums of all the values from A1 to A3.

## Function Structure

**arguments**“.

A function’s arguments appear after the function name, inside parentheses, separated by commas.

All functions require matching

**opening**and

**closing**parentheses

**()**. The pattern looks like this:

**=FUNCTIONNAME(argument1,argument2,argument3)**

These inputs are called “arguments”.

A function’s arguments appear after the function name, inside parentheses, separated by commas.

## Function Structure Example:

Enter the equals sign (=) and start typing. Excel will list matching functions based as you type:

All formulas return a result.

Note: All formulas in Excel return a result, even when the result is

**an error**.

## How to use Excel formulas?

OR

How to use a function in Excel?

Enter the equals sign (=) and start typing.

Excel will list matching functions based as you type:

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

Enter the equals sign (=) and start typing.

Excel will list matching functions based as you type:

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

*(Part/Whole)*100 = Percentage*

For example, if you had 20 mangos and you gave 5 to your friends, how much did you give, percentage-wise?

By performing a simple calculation

**=5/20*100**

you get the answer – 25%.

The basic formula to calculate

**percentages in Excel**is this:

*Part/Total = Percentage*

**Percentage Example:**

To find out the percentage of delivered products, perform the following steps:

Enter the formula

**=C2/B2**in cell D2, and copy it down to as many rows as you need.

Click the

**Percent Style**button (

*Home*tab >

*Number*group) to display the resulting decimal fractions as percentages.

Remember to increase the number of decimal places if needed, as explained in Percentage tips.

## 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()

**=COUNT(**

**value1, [value2], …****)**

Example:

**COUNT(A**:

**A)**– Counts all values that are numerical in the A column. However, you must adjust the range inside the formula to count rows.

**COUNT(A1:C1)**– Now it can count rows.

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

**=SUM(**

**number1****, [number2], …)**

Example:

**=SUM(B2:G2)**– A simple selection that sums the values of a row.

**=SUM(A2:A8)**– A simple selection that sums the values of a column.

**=SUM(A2:A7, A9, A12:A15)**– A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.

**=SUM(A2:A8)/20**– This shows you can also turn your function into a formula.

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.

**logical_test, [value_if_true], [value_if_false]**

Here mark 85 is greater than 32 so the return result is Pass.

**=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10))**– An example of a complex IF logic. First, it sums

*C1 to C10*and

*D1 to D10*, then it compares the sum.

If the sum of C1 to C10 is greater than the sum of

*D1 to D10*, then it makes the value of a cell equal to the sum of

*C1 to C10*. Otherwise, it makes it the SUM of

*C1 to C10*.

**Nested if examples:**

## How to use the Excel Average function?

### MAX() , MIN() & Average()

**=MIN(**

**number1****, [number2], …)**

Example:

**=MIN(B2:C11)**– Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

**=MAX(**

**number1****, [number2], …)**

Example:

**=MAX(B2:C11)**– Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

**=AVERAGE(**

**number1****, [number2], …)**

Example:

**= AVERAGE (B2:C11)**–It finds the average number among a reference of cells.

Example of max(), min(), and average() from students table.

## Proper, UPPER, and lower functions are used for text formatting.

**Proper function: Proper()**The Excel PROPER function capitalizes words given text string. Numbers and punctuation are not affected. Only

**Capitalize**the first letter in each word

**Return value**Text in proper case.

**Syntax**=PROPER (text)

**Arguments.**

**text**– The text that should be converted to a proper case.

**Usage notes:**Use PROPER to capitalize each word in a given string.

All letters in the

**text**will be converted to lowercase before the first letter in each word is capitalized.

Numbers and punctuation characters are not affected.

**UPPER, lower functions: upper(), lower()**

like a proper, The

**UPPER**function capitalizes all letters of each word.

On the other hand, the

**lower**function converts Text in lowercase.

The rest of the things behave like proper functions.

**A comparable example** is given below to understand these functions’ natures.

## TRIM functions:

### When to use Excel TRIM Function?

What it Returns?

It returns the text string

**REMOVING**white space from the cell.

Syntax: =TRIM(text)

Input Arguments

Here, ‘text’ is the text string from which you want to remove the extra spaces. This could either be entered manually (within double quotes) or can be a cell reference that contains the text string.

For example, if you want to remove extra spaces from the text in cell A1, then you can use the formula

=TRIM(A1)

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

**=**

**CONCATENATE (**

**text1****, text2, …)**

Example:

## Dynamic Data Validation using Excel

For example, make sure a text entry is less than 15 characters.

I am not going to elaborate discussion on this. Just describe the availability of the menu and select reference of cells for accuracy of data what can one objective of data validation? be

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

You can use INDEX to retrieve individual values or entire rows and columns.

INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Example of index function:

**INDEX ()**

**POSITION**in a range or array.

INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Example of match function:

**MATCH ()**

*The following example shows the use of*

**index**() &**match**() functions combined.## Choose a function in Excel

**CHOOSE(index_num, value1, [value2], …)**

Where:

**Index_nu m**(required) – the position of the value to return. It can be any number between 1 and 254, a cell reference, or another formula.

**Value1, value2, …**– a list of up to 254 values from which to choose. Value1 is required, other values are optional. These can be numbers, text values, cell references, formulas, or defined names.

Here’s an example of a CHOOSE formula in the simplest form:

**=CHOOSE(2, “5%”, “12%”, “15%”)**

The formula returns 0.12 because

*index_num*is 2 and 0.12 is the 2

^{nd}value in the list:

As an example, let’s calculate the commission of each seller depending on their sales:

=CHOOSE((B2>0) + (B2>=51) + (B2>=101), B2*5%, B2*7%, B2*10%)

## How does it look in Excel?

VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.

Lookup values must appear in the

*first*column of the table passed into VLOOKUP.

Note:

? = a question mark (?) matches exactly one character.

* = asterisk (*) matches a series of characters.

Return value: The matched value from a table.

Syntax : =VLOOKUP (value, table, col_index, [range_lookup])

Arguments:

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

Alhamdulillah!

My new (2 weeks earlier) created Facebook Group based on this article members are just now reach 2000+(2K+).

Pls, join this group if you are interested in a new post related to MS Office.

https://www.facebook.com/groups/1258010048003516