**Topics in this Post**show

#### Formula Tab in Microsoft Excel

Formula Tab in Microsoft Excel contain many functions, formulas and options to calculate the data values of the worksheets. The ribbon of Formula Tab is divided in to multiple sections with the name Function Library, Defined Names, Formula Auditing and Calculations. The detailed explanation about Formula Tab in Microsoft Excel is given below.

##### Function Library

**Insert Function**– In this section we can use large number of predefined Excel functions. There are Auto Sum, financial, logical, text, date and time, lookup and reference, math and trig, and additional functions. It also provides a section for recently used functions, so we can get back to the ones that we use the most quickly. Some of the most useful functions given below.

###### Excel Operators

Before working with functions and formulas, it is most important to know various types of operators used in Excel.

Operator Name | Operator | Example | |

Plus (Add) | + | =A1+B1 | |

Minus (Subtract) | – | =A1-B1 | |

Astric (Multiply) | * | =A1*B1 | |

Divide | / | =A1/B1 | |

Equal To | = | =A1=B1 | |

Greater Than | > | =A1>B1 | |

Greater Than Equal To | >= | =A1>=B1 | |

Less Than | < | =A1<B1 | |

Less Than Equal To | <= | =A1<=B1 | |

Not Equal To | <> | =A1<>B1 | |

Percent | % | =A1% | |

And | & | =A1&B1 | |

Colon | : | =A1:A10 | |

Power | ^ | =A1^B1 | |

Comma | , | =Sum(A1:A10,B1:B5) |

###### Microsoft Excel Formula List

**1. Sum**

To sum a range, use the SUM formula. Range is a selection of values, all the values that come in it are added. For adding more than one range, use a comma.

Formula : =sum(range1,range2,range3…)

Example : =SUM(A1:A5,C1:C5)

**2. Sum If**

The SUMIF formula is used to sum a range based on a criteria. Criteria can be any name or value, that is repeated many times in a range and we want to add their values. In given table we will total of rent using sumif.

A | B | |

1 | Expense | Amount |

2 | Rent | 2000 |

3 | Food | 900 |

4 | Rent | 2500 |

5 | Recharge | 700 |

6 | Rent | 3500 |

Formula : =sumif(criteria_range, criteria, sum_range)

Example : =SUMIF(A2:A6,A2, B2:B6)

Ans : 80000

To add the values in given range which are greater than 1000.

Example : =SUMIF(B2:B6,”>1000”)

**3. Count**

To count the cells that contains only values.

Formula : =count(range)

Example : =COUNT(A2:A10)

**4. Count Blank**

To count only blank cells in given range.

Formula : =countblank(range)

Example : =COUNTBLANK(A2:A10)

**5. Count A**

To count cells that contains text.

Formula : =counta(range)

Example : =COUNTA(A2:A10)

**6. Count If**

To count the specific text or value in given range.

Formula : =countif(range,”text or value”)

Example : =COUNTIF(A2:A10,”M”)

An Example given below to use all count functions.

**7. Max**

To find the maximum value in given range.

Formula : =max(range1,range2,…)

Example : =MAX(A2:A10)

**8. Min**

To find the minimum value in given range.

Formula : =min(range1,range2,…)

Example : =MIN(A2:A10)

**9. Average**

To find the average value in given range.

Formula : =average(range1,range2,…)

Example : =AVERAGE(A2:A10)

**10. Average If**

To find the average of specific criteria in given range. In given table you can find the average of rent using “AVERAGEIF” formula.

A | B | |

1 | Expense | Amount |

2 | Rent | 2000 |

3 | Food | 900 |

4 | Rent | 2500 |

5 | Recharge | 700 |

6 | Rent | 3500 |

Formula : =averageif(criteria_range, criteria, average_range)

Example : =AVERAGEIF(A2:A6,A2,B2:B6)

Ans : 2666.66

**11. If**

In if formula we can define a condition to calculate the values. Here is an example to calculate result of students. The condition is, if percentage marks is greater than 50 then student will be passed otherwise fail.

A | B | C | |

1 | Name | Marks | Result |

2 | Amit | 78 | Pass |

3 | Ajay | 42 | Fail |

Formula : =if(logical_condition,”True_Msg”,”False_Msg”)

Example : =IF(B2>50,”Pass”,”Fail”)

**12. Nested If**

When we define multiple if conditions in a if formula, that is called nested if. Here is an example to calculate the division of students according to percentage value using nested if.

conditions : Marks<33 = Fail, Marks<45 = Third, Marks<60 = Second otherwise First.

A | B | C | |

1 | Name | Marks | Result |

2 | Amit | 78 | First |

3 | Rohit | 43 | Second |

4 | Prateek | 25 | Fail |

5 | Ravi | 39 | Third |

Formula : =if(logical_condition,”True_Msg”,if(logical_condition,”True_Msg”,”False_Msg”))

Example : =IF(B2<33,”Fail”,IF(B2<45,”Third”,IF(B2<60,”Second”,”First”)))

**13. Concatenate**

To join the multiple cells or text into one cell. Text always written in formula with double quote. Ex – “text”

A | B | C | |

1 | First Name | Last Marks | Full Name |

2 | Amit | Singh | Amit Singh |

Formula : =concatenate(text1,text2…)

Example : =CONCATENATE(A2,” “,B2)

**14. Upper**

To convert the cell text with UPPER case.

A | B | |

1 | Name | Upper Result |

2 | Amit singh | AMIT SINGH |

Formula : =upper(text)

Example : =UPPER(A2)

**15. Lower**

To convert the text with lowercase.

A | B | |

1 | Name | Lower Result |

2 | AMIT SINGH | amit singh |

Formula : =lower(cell no or “text”)

Example : =LOWER(A2)

**16. Proper**

To convert the text with proper case.

A | B | |

1 | Name | Proper Result |

2 | AMit SinGH | Amit Singh |

Formula : =proper(“text” or cell_no)

Example : =PROPER(A2)

**17. Left**

To collect numbers or characters from the left side of the cell value, we can give the value in num_chars to collect number of character. In given example, Left formula is use to collect country code from the phone number.

A | B | |

1 | Phone Number | Left Result |

2 | +918843211143 | +91 |

Formula : =left(cell_no, num_chars)

Example : =LEFT(A2, 3)

**18. Right**

To collect numbers or characters from the right side of the cell value. In given example, Left formula is use to collect 10 digit phone number.

A | B | |

1 | Phone Number | Right Result |

2 | +918843211143 | 8843211143 |

Formula : =right(cell no, num_chars)

Example : =RIGHT(A2,10)

**19. Middle**

To pick the characters or numbers from the cell value, start_num means starting character number from where to pick and num_chars means how many character to pick.

A | B | |

1 | Phone Number | Middle Result |

2 | +918843211143 | 8843 |

Formula : =mid(text or cell_no, start_num, num_char)

Example : =MID(A2, 4, 4)

**20. Find**

To find any text or character in any cell.

A | B | |

1 | Email | Find Result |

2 | [email protected] | 8 |

Formula : =find(“char”, cell_no)

Example : FIND(“@”, A2)

**21. Replace**

To replace the text or character from the cell, start_num means starting character number from where to replace and num_chars means how many character to replace.

A | B | |

1 | Email | Replace Result |

2 | [email protected] | [email protected] |

Formula : =replace(cell_no, start_num, num_chars, “text”)

Example : = REPLACE(A2, 9, 5, “hotmail”)

**22. Repeat**

To repeat the character according to the given number_times.

A | B | |

1 | Channel Name | Repeat Result |

2 | Star Plus | ***** |

3 | Sony Tv | *** |

Formula : =rept(“char”, number or cell_no)

Example : = REPT(“*”, 5)

**23. Substitute**

To change any symbol or spaces in another data.

A | B | C | |

1 | Data | Formula | Substitute Result |

2 | jan-feb-march | =substitute(A2,”-“,” “) | jan feb march |

3 | jan,feb,march | =substitute(A3,”,”,”-“) | jan-feb-march |

4 | pin 241303 | =substitute(A4,” “,”-“) | pin-241303 |

**24. Now**

To check the current time and date.

Formula : =NOW()

**25. Today**

To check the current date.

Formula : =TODAY()

**26. Dated If**

Using this formula we can check the age of person. To calculate age uses “y” for years, “ym” for months and “md” for days.

To check the days of age.

=DATEDIF(birth_date_cell_no, today(),”md”)

To check the months of age.

=DATEDIF(birth_date_cell_no, today(),”ym”)

To check the years of age.

=DATEDIF(birth_date_cell_no, today(),”y”)

**27. Transpose**

=transpose(range)

Using this formula we can convert the data of rows in to columns and columns into rows. To use this formula follow these steps.

- Select criteria where want to paste
- On the first cell of selected criteria write formula =TRANSPOSE(Data_array)
- In data array select range of data
- After writing formula press ctrl+shift+enter to action.

Alternatively use this function from paste special option.

**28. Power**

To raises a number to a user specified power. It is same as using the ^ operator, such as 3^4, which result is 81. Both the power() function and the ^ operator are the same as using 3*3*3*3.

Formula : =power(number, power)

Example : =POWER(3, 4)

**29. Even**

To find next even value of the given odd value.

Formula : =even(value or cell_no)

Example : =EVEN(13)

Ans : 14

**30. Odd**

To find next odd value of the given even value.

Formula : =odd(value or cell_no)

Example : ODD(14)

Ans : 15

**31. Modulus**

This function calculates the remainder of a value after divided by a number.

Formula : =mod(number, diviser_value)

Example : =MOD(30, 4)

Ans : 2

**32. Round**

The round formula round a value by given num_digits.

Formula : =round(number, num_digits)

A | B | C | |

1 | Values | Formula | Result |

2 | 12.789 | =ROUND(A2,2) | 12.79 |

3 | 240.436 | =ROUND(A3,0) | 240 |

4 | 526.5 | =ROUND(A4,-3) | 1000 |

- If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
- If num_digits is 0, the number is rounded to the nearest integer.
- If num_digits is less than 0, the number is rounded to the left of the decimal point. -1 returns nearest to 10, -2 returns nearest to 100 and -3 returns nearest to 1000.

**33. Integer**

This function rounds a number down to the nearest whole number.

Formula : =int(value or cell_no)

Example : =INT(13.66)

Ans : 13

**34. Length**

To check the length of the text or number, it count the characters with spaces.

A | B | |

1 | Email | Length Result |

2 | [email protected] | 19 |

Formula : =len(“text” or cell_no)

Example : =LEN(A2)

**35. Char**

This formula is use to find ASCII character of the value between 0 to 255.

Formula : =char(value)

Example : =CHAR(65)

Ans : A

**36. Trim**

To remove extra spaces between words.

A | B | |

1 | Address | Trim Result |

2 | Near B. N. Inter College, Chauk, Kanpur | Near B. N. Inter College, Chauk, Kanpur |

Formula : =trim(“text” or cell_no)

Example : =TRIM(A2)

**37. Frequency**

=frequency(data_array, bins_array)

With the help of FREQUENCY formula, we can find out how many times a value is repeated in any range. Let’s assume that in an Age column, it is to be found that the maximum age of the person is there. Follow the steps below to use the formula.

- First of all, in the range we want to see, we will select that range and copy it, then paste it in a new column, which will be named Unique.
- Then remove duplicate value from unique column.
- Now put the formula in the new column =FREQUENCY(data_array, bins_array)
- We will give the range of age column in Data_array and Unique column range in bins_array.
- After entering formula, drag down for all, and let the value remain selected, now press f2 to first cell in which the formula is applied and press ctrl+shift+enter.
- In this way we will be able to see which number is repeated how many times.
- Below table showing the example of above method

Person | Age | Unique | Frequency Result |

A | 33 | 33 | 3 |

B | 23 | 23 | 1 |

C | 54 | 54 | 2 |

D | 33 | 65 | 1 |

E | 65 | 76 | 1 |

F | 76 | ||

G | 33 | ||

H | 54 |

**38. PMT**

To calculate EMI of a Loan, we can use PMT Formula. In given table we use PMT Formula to calculate Installment.

A | B | C | |

1 | Loan Amount | 100000 | Pv(Principal Value) |

2 | Period in Month | 10 | Nper (No of Period) |

3 | Interest Rate | 10% | Rate |

4 | EMI | ₹10,464.04 | =PMT(B3/12,B2,-B1) |

5 | Total Paid | ₹1,04,640.38 | =B4*B2 |

6 | Total Interest | ₹4,640.38 | =B5-B1 |

**39. Simple and Compound Interest**

A | B | C | |

1 | Principal Amount | 500 | |

2 | Interest Rate Monthly | 10 | |

3 | Time in Month | 6 | |

4 | Simple Interest | 300 | =B1*B2*B3/100 |

5 | Compound Interest | 885.7805 | =B1*(1+B2/100)^B3 |

**40. Index**

To find a value or text in a range.

A | B | C | |

1 | Time | Sony | Star Plus |

2 | 8 am | movie | cricket |

3 | 9 am | news | ramayan |

5 | 10 am | cid | cartoon |

Formula : =index(array, row_no, column_no)

Example : =INDEX(A1:C5, 3, 3)

Ans : ramayan

**41. Match**

To find a column or row number by given a value or text in a row or column range. Match type is 0 for exact match, 1 is less than, -1 is greater than.

Formula : =match(lookup_value, lookup_array, match_type)

To find column number

Example : =MATCH(“news”, A3:C3, 0)

Result : 2

To find row number

Example : =MATCH(“news”, B1:B5, 0)

Result : 3

**42. Index and Match**

A | B | C | |

1 | Time | Sony | Star Plus |

2 | 8 am | movie | cricket |

3 | 9 am | news | ramayan |

5 | 10 am | cid | cartoon |

**=index(a1:c5,match(“9 am”, a1:a5, 0), match(“star plus”, a1:c1, 0))**

Answer : ramayan

**43. Vlookup**

=vlookup(lookup_value, table_array, column_index_no, range_lookup)

To show values vertically from database by given lookup_value, table_array, column_index_no.

**44. Hlookup**

=hlookup(lookup_value, table_array, row_index_no, range_lookup)

To show values horizontally from database by given lookup_value, table_array, row_index_no.

##### Defined Names

**Name Manager-**This option is use to create, edit, delete and find all the names used in the workbook. Name can be used in formulas as substitute of cell reference. Ex- =sum(sale) instead of =sum(A1:A10)**Define Names-**Define name is used to define a name of the cell range.**Used in formula-**This function use to choose a defined name to paste with formula in the workbook.

##### Formula Auditing

**Trace Precedents-**This option shows the arrow to the formulated cell that indicates how many cells are affected.**Trace Dependence-**This option shows the arrows to the selected cells from a formulated cell.**Remove Arrows-**This option removes all the arrows.**Show Formulas-**This option shows the formulas of the calculated values.**Error Checking-**This option check the common errors that occurs in formulas or cells.**Evaluate Formula-**This option opens a dialog box that displays how formula calculated the value step by step.**Watch Window-**This option add a watch option to the active cell that display the information as- cell no, sheet no, workbook no, value and applied formula.

##### Calculation

- This section use to turn on or off automatic calculations to formulas in worksheet or workbook. If you turn off automatic calculations then you can use this area to calculate the current cell or the whole sheet manually.