What is the use of Formula Tab in Microsoft Excel?

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.