Mosharraf | Writer,Trainer and Digital Marketer

Explain useful Excel formulas and functions with examples essential in 2024.

Similar type of functions

Written by

Md. Mosharraf Hossain

in

  1. Why Microsoft Excel is so important in our daily office work or life?
  2. Excel Worksheet Basic Structure and Term
  3. Function Structure Explain with an example
  4. How to use Excel formulas? Or How to use Excel for formulas? How to put formulas in Excel?
  5. 20+ Most useful Excel formulas are?
  6. Excel Order of operations
  7. Math Operators
  8. Logical operators
  9. Useful Excel formula for Subtraction
  10. The Most Useful Excel Multiply Formula
  11. Useful Excel formulas for Percentage with Example
  12. MS Excel Basic and Advanced Group of Formulas and Functions
  13. How to use the Excel Count function? Or MS Excel – Count Functions
  14. The formulas of the Sum Function
  15. MS Excel – IF Function
  16. How to use the Excel Average function?
  17. Proper, UPPER, and lower functions are used for text formatting.
  18. Trim function is used to remove white space from specific cell
  19. Examples of left, mid, right, and len functions
  20. Concatenate functions used as examples
  21. Dynamic Data Validation using Excel
  22. INDEX & MATCH FUNCTIONS the alternative function of the lookup
  23. Choose a function in Excel
  24. How does it look in Excel?
  25. Function lookup examples
  26. 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

excel worksheet basic structure
excel worksheet basic structure

Microsoft Excel Basic Terms

Function Structure

structure of function
structure of function

Function Structure Example:

Function structure examples
Function structure examples

How to use Excel formulas?
OR
How to use a function in Excel?

How to put a formula in Excel?

  1. Select a cell
  2. Enter an equals sign (=)
  3. Type the formula, and press enter.

20 + Most used important and useful Excel formulas are:

S.LFunctionsS.LFunctions
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
list of functions

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
Operator Precedence
Operator Precedence

Math Operators

The table below shows the standard math operators available in Excel:

SymbolOperationExample
+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
Math Operators

Logical operators

OperatorMeaningExample
=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
Logical Operators

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

excel-percentage-formula
excel-percentage-formula

MS Excel Basic and Advanced useful Group of Formulas and Functions

Most used excel predefined functions
Most used Excel predefined functions

Similar type functions list with their single or multiple arguments

Similar type of functions
Similar types of functions

Note:

  1. ?  = a question mark (?) matches exactly one character.
  2. * = asterisk (*) matches a series of characters.

Most used Excel formulas return results by dummy data table :

Excel Formulas example
Example of Excel Formulas

How to use the Excel Count function?

COUNT()

Most used Excel formulas of count function return results by dummy data table :

example of count function

There are similar types of functions but names and uses are different and argument has variety.

similar count function
similar count function

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 :

example of sum function
example of the sum function
combination of sum and if formulas
combination of sum and if formulas

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.

example of if function
example of if function

Nested if examples:

nested if function
nested if function

How to use the Excel Average function?

MAX() , MIN() & Average()

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

example of max-min-avg function
example of max-min-avg function

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

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

trim upper lower proper functions
trim upper-lower proper functions

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:

len, mid, left, right functions use with example
len, mid, left, and right functions used with example

Details break down to realize the specified function’s nature:

Excel mid- len- left- right functions
Excel mid, Len, left, and right functions

CONCATENATE functions used as examples

concatenate function
Example of concatenate function
Example of concatenate function

Dynamic Data Validation using Excel

Data Validation Output
Data Validation Output

Selection data validation menu screenshot in Excel.

Data Validation Menu
Data Validation Menu

The next snapshot shows a selection of references of cells.

Reference of cells selection for data validation
Reference of cell selection for data validation

Index & Match functions are the alternative functions of the lookup

index function
index function
match functions example
match functions example
combination of INDEX and MATCH formulas
combination of INDEX and MATCH formulas

Choose a function in Excel

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

example of choose function
example of choose function
choose function using nested if
choose function using nested if

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

vlookup function
lookup function

Video tutorial of Excel formulas

Comments

Leave a Reply

More posts