What is the use of Formula Tab in Microsoft Excel?

Formula Tab in Microsoft Excel

Formula tab

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

AB
1ExpenseAmount
2Rent2000
3Food900
4Rent2500
5Recharge700
6Rent3500

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.

AB
1ExpenseAmount
2Rent2000
3Food900
4Rent2500
5Recharge700
6Rent3500

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.

ABC
1NameMarksResult
2Amit78Pass
3Ajay42Fail

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.

ABC
1NameMarksResult
2Amit78First
3Rohit43Second
4Prateek25Fail
5Ravi39Third

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”

ABC
1First NameLast MarksFull Name
2AmitSinghAmit Singh

Formula : =concatenate(text1,text2…)

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

14. Upper

To convert the cell text with UPPER case.

AB
1NameUpper Result
2Amit singhAMIT SINGH

Formula : =upper(text)

Example : =UPPER(A2)

15. Lower

To convert the text with lowercase.

AB
1NameLower Result
2AMIT SINGHamit singh

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

Example : =LOWER(A2)

16. Proper

To convert the text with proper case.

AB
1NameProper Result
2AMit SinGHAmit 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.

AB
1Phone NumberLeft 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.

AB
1Phone NumberRight Result
2+9188432111438843211143

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.

AB
1Phone NumberMiddle Result
2+9188432111438843

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.

AB
1EmailFind 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.

AB
1EmailReplace 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.

AB
1Channel NameRepeat Result
2Star Plus*****
3Sony Tv***

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

Example : = REPT(“*”, 5)

23. Substitute

To change any symbol or spaces in another data.

ABC
1DataFormulaSubstitute Result
2jan-feb-march=substitute(A2,”-“,” “)jan feb march
3jan,feb,march=substitute(A3,”,”,”-“)jan-feb-march
4pin 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)

ABC
1ValuesFormulaResult
212.789=ROUND(A2,2)12.79
3240.436=ROUND(A3,0)240
4526.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.

AB
1EmailLength 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.

AB
1AddressTrim Result
2Near B. N. Inter College, Chauk, KanpurNear 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
PersonAgeUniqueFrequency Result
A33333
B23231
C54542
D33651
E65761
F76
G33
H54

38. PMT

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

ABC
1Loan Amount100000Pv(Principal Value)
2Period in Month10Nper (No of Period)
3Interest Rate10%Rate
4EMI₹10,464.04=PMT(B3/12,B2,-B1)
5Total Paid₹1,04,640.38=B4*B2
6Total Interest₹4,640.38=B5-B1

39. Simple and Compound Interest

ABC
1Principal Amount500
2Interest Rate Monthly10
3Time in Month6
4Simple Interest300=B1*B2*B3/100
5Compound Interest885.7805=B1*(1+B2/100)^B3

40. Index

To find a value or text in a range.


ABC
1TimeSonyStar Plus
28 ammoviecricket
39 amnewsramayan
510 amcidcartoon

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

ABC
1TimeSonyStar Plus
28 ammoviecricket
39 amnewsramayan
510 amcidcartoon

=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.
Share This Post

Leave a Reply

Your email address will not be published. Required fields are marked *