MICROSOFT OFFICE EXCEL 2007

MICROSOFT OFFICE EXCEL 2007

Introduction
Ms Excel is a Spreadsheet Application, Which is a component of Microsoft Office. It is a very popular
Spreadsheet application. It helps us to store raw data or analyzed Data in the form of Tables. It also
provides facility to calculate and manipulate their values.
Ms Excel ,d Spreadsheet Application gS] tks Microsoft Office dk ,d Hkkx gSA ;g ,d cgqr çfl)
Spreadsheet application gSA ;g gesa data store djus esa ;k Data dks analyze dj Table ds form esa store djus
esa enn djrk gSA ;g mudks calculate vkSj manipulate djus dh lqfo/kk çnku djrk gSA
The file of Ms Excel is called workbook, and workbook is a collection of worksheets. Each worksheet is
divided into multiple rows and columns, rows and columns divide worksheet into multiple Cells. A cell is
an individual key element of Excel, which has a unique name. Rows are labeled with numbers i.e. 1, 2, 3,
4…1048576 and columns are labeled with alphabets i.e. A, B, C, D…XFD. The total number of rows in a
worksheet is 1048576 and the total numbers of columns are 16384 in a worksheet. Like this, each cell has
its unique name; the first cell comes under column A and row 1 so its name is A1. And the Last cell comes
under the Column XFD and Row 1048576 so its name is XFD1048576. We can insert text as well as
numbers, symbols, functions and formulas into a cell. Its extension name is (.xlsx).
Ms Excel dk file, workbook dgykrk gS] vkSj workbook, worksheets dk ,d lewg gSA izR;sd worksheet, vusd
Rows vkSj columns esa caVs gksrs gSa, rows vkSj columns, worksheet dks vusd cells esa ckaVrs gSaA cell Excel dk ,d
[kkl key element gksrk gSA ftldk fo’ks"k uke gksrk gSA Rows uacjksa (1,2,3,4…1048576) ls vkSj columns v{kjksa
(A,B,C,D…XFD) ds }kjk label fd;s gksrs gSaA ,d worksheet esa Rows dh dqy la[;k 1048576 vkSj columns dh
dqy la[;k 16384 gksrh gS] blh çdkj gj cell dk ,d fo’ks"k uke gS] igyk cell column A rFkk row 1 esa vkrk gS]
blfy, bldk uke A1 gksrk gS] blh rjg B1,C10,D5 bR;kfn gS vkSj vafre cell column XFD rFkk rows 1048576 esa
vkrk gS] blfy, bldk uke XFD1048576 gSA ge text ds vykok cell esa numbers, symbols, functions vkSj
formula dks Hkh insert dj ldrs gSaA bldk extention name (.xlsx) gksrk gSA

How to start Ms Excel?

1. Start  All Programs  Microsoft Office  Microsoft Office Excel 2007
2. Start  All Programs  Accessories  Run  Type “EXCEL”  OK
3. Start  Type “EXCEL” in Search Box  Press Enter Key

Microsoft Office Excel 2007 Screenshot

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 1 of 19
MICROSOFT OFFICE EXCEL 2007

Q. To store four numbers after that find the sum of all that numbers.
NUMBER 1 NUMBER 2 NUMBER 3 NUMBER 4 SUM
23 63 2 45 ?
65 56 5 23 ?
23 34 6 21 ?
25 63 9 45 ?
SUM → =A2+B2+C2+D2 OR =SUM(A2..D2)
Q. To store four numbers after that find the product of all that numbers.
NUMBER 1 NUMBER 2 NUMBER 3 NUMBER 4 PRODUCT
23 63 2 45 ?
65 56 5 23 ?
23 34 6 21 ?
25 63 9 45 ?
PRODUCT → =A2*B2*C2*D2 OR =PRODUCT(A2..D2)
Q. To store five numbers after that find the total & average of all that numbers.
NUMBER 1 NUMBER 2 NUMBER 3 NUMBER 4 NUMBER 5 TOTAL AVERAGE
23 63 2 45 69 ? ?
65 56 5 23 45 ? ?
23 34 6 21 86 ? ?
25 63 9 45 98 ? ?
TOTAL → =A2+B2+C2+D2+E2 OR =SUM(A2..E2)
AVERAGE → =(A2+B2+C2+D2+E2)/5 OR =AVERAGE(A2..E2) OR =F2/5
Q. To store length & base of rectangle and find its area & perimeter.
LENGTH OF RECTANGLE BASE OF RECTANGLE AREA PERIMETER
23 63 ? ?
65 56 ? ?
23 34 ? ?
25 63 ? ?
AREA → =A2*B2
PERIMETER → =2*(A2+B2)
Q. To store diameter of a circle after that find its radius, area & circumference.
DIAMETER RADIUS AREA CIRCUMFERENCE
23 ? ? ?
65 ? ? ?
23 ? ? ?
25 ? ? ?
RADIUS → =A2/2
AREA → =3.14*B2^2
CIRCUMFERENCE → =2*3.14*B2

2 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

Q. To store Principle amount, Rate, Time after that find Simple Interest.
PRINCIPLE AMOUNT RATE TIME SIMPLE INTEREST
5200 8 5 ?
3200 6 3 ?
4526 2 2 ?
3254 6 6 ?
SIMPLE INTEREST → =A2*B2*C2/100
Q. To store diameter & height of a cylinder after that find its Radius, Volume, Total Surface
Area, Curved Surface Area.
DIAMETER HEIGHT RADIUS VOLUME TSA CSA
23 52 ? ? ? ?
65 3 ? ? ? ?
23 6 ? ? ? ?
25 4 ? ? ? ?
RADIUS → =A2/2
VOLUME → =3.14*C2^2*B2
TSA → =2*3.14*C2*(C2+B2)
CSA → =2*3.14*C2*B2
Q. To store Name of Item, Quantity and Price after that find Amount & Total Amount.
NAME OF ITEMS QUANTITY PRICE AMOUNT
ORIO 22 56 ?
DAIRY MILK 16 23 ?
MUNCH 56 12 ?
KURKURE 78 45 ?
TOTAL AMOUNT ?
AMOUNT → =B2*C2
Q. To store Name, Age of some persons after that find that the person is Eligible for Vote or
Not Eligible for Vote.
NAME AGE ELIGIBILITY FOR VOTE
TANIYA SHARMA 22 ?
SAKET MISHRA 16 ?
SHALINI SHUKLA 56 ?
SOMYA RAJ 8 ?
ELIGIBILITY FOR VOTE → =IF(B2>=18,"ELIGIBLE","NOT ELIGIBLE")
Q. To store any number after that find the stored number is Even or Odd.
NUMBER 1 NUMBER IS EVEN OR ODD
66 ?
24 ?
23 ?
25 ?
NUMBER IS EVEN OR ODD → =IF(MOD(A2,2)=0,"EVEN","ODD")

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 3 of 19
MICROSOFT OFFICE EXCEL 2007

Q. To store Name of Item, Cost Price & Sale Price after that find Profit and Loss.
NAME OF ITEMS COST PRICE SALE PRICE PROFIT LOSS
ORIO 22 56 ? ?
DAIRY MILK 16 23 ? ?
MUNCH 56 12 ? ?
KURKURE 78 45 ? ?
PROFIT → =IF(C2>B2,C2-B2,"NO PROFIT")
LOSS → =IF(C2<B2,B2-C2,"NO LOSS")
Q. To store two numbers after that find Biggest & Smallest number.
NUMBER 1 NUMBER 2 BIGGEST NUMBER SMALLEST NUMBER
23 52 ? ?
65 3 ? ?
23 6 ? ?
25 4 ? ?
BIGGEST NUMBER → =IF(A2>B2,A2,B2)
SMALLEST NUMBER → =IF(A2<B2,A2,B2)
Q. To store three numbers after that find Biggest & Smallest number.
NUMBER 1 NUMBER 2 NUMBER 3 BIGGEST NUMBER SMALLEST NUMBER
23 52 69 ? ?
65 3 32 ? ?
23 6 56 ? ?
25 4 89 ? ?
BIGGEST NUMBER →
=IF(AND(A2>B2,A2>C2),A2,IF(AND(B2>A2,B2>C2),B2,IF(C2>A2,C2>B2),C2)))
SMALLEST NUMBER →
=IF(AND(A2<B2,A2<C2),A2,IF(AND(B2<A2,B2<C2),B2,IF(C2<A2,C2<B2),C2)))
OR, BIGGEST NUMBER →
=IF(AND(A2>B2,A2>C2),A2,IF(AND(B2>A2,B2>C2),B2,IF(AND(C2>A2,C2>B2),C2,IF(AND(A2>B2,A2=
C2),A2,IF(AND(B2>C2,B2=A2),B2,IF(AND(C2>A2,C2=B2),C2,IF(AND(A2=B2,B2=C2,C2=A2),"ALL ARE
EQUAL")))))))
OR, SMALLEST NUMBER →
=IF(AND(A2<B2,A2<C2),A2,IF(AND(B2<A2,B2<C2),B2,IF(AND(C2<A2,C2<B2),C2,IF(AND(A2<B2,A2=
C2),A2,IF(AND(B2<C2,B2=A2),B2,IF(AND(C2<A2,C2=B2),C2,IF(AND(A2=B2,B2=C2,C2=A2),"ALL ARE
EQUAL")))))))

4 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

Q. To store Name, Class, Marks of Math, Physics, Biology and English after that find Total,
Percentage and Division.
NAME CLASS MATH PHYSICS CHEMISTRY BIOLOGY ENGLISH TOTAL PERCENTAGE DIVISION
SHYAM 5 89 41 96 45 36 ? ? ?
RADHA 5 56 63 45 89 45 ? ? ?
TANIYA 5 23 41 63 96 98 ? ? ?
SONU 5 12 95 32 15 88 ? ? ?
TOTAL → =SUM(C2..G2)
PERCENTAGE → =H2*100/500 OR =(C2+D2+E2+F2+G2)*100/500
DIVISION →
=IF(OR(C2<33,D2<33,E2<33,F2<33,G2<33),"FAIL",IF(I2>=60,"FIRST",IF(I2>=45,"SECOND",IF(I2>=33,"
THIRD"))))
Q. To store Name, Class, Marks of Math, Physics, Biology and English after that find Total,
Percentage and Grade where the conditions are given.
SHYAM 5 89 41 96 45 36 ? ? ?
RADHA 5 56 63 45 89 45 ? ? ?
TANIYA 5 23 41 63 96 98 ? ? ?
SONU 5 12 95 32 15 88 ? ? ?
TOTAL → =SUM(C2..G2)
PERCENTAGE → =H2*100/500 OR =(C2+D2+E2+F2+G2)*100/500
CONDITIONS FOR GRADE →
1. IF PERCENTAGE GREATER & EQUAL 80% THEN GRADE=A+
2. IF PERCENTAGE GREATER & EQUAL 60% THEN GRADE=A
3. IF PERCENTAGE GREATER & EQUAL 45% THEN GRADE=B+
4. IF PERCENTAGE GREATER & EQUAL 30% THEN GRADE=B
5. IF PERCENTAGE LESS THAN 30% THEN GRADE=C
Q. To store Name, Class, Marks of Math, Physics, Biology and English after that find Total,
Percentage, Grade and Rank where the conditions are given.
NAME CLASS MATH PHYSICS CHEMISTRY BIOLOGY ENGLISH TOTAL PERCENTAGE GRADE RANK
SHYAM 5 89 41 96 45 36 ? ? ? ?
RADHA 5 56 63 45 89 45 ? ? ? ?
TANIYA 5 23 41 63 96 98 ? ? ? ?
SONU 5 12 95 32 15 88 ? ? ? ?
TOTAL → =SUM(C2..G2)
PERCENTAGE → =H2*100/500 OR =(C2+D2+E2+F2+G2)*100/500
RANK → =RANK(H2,$H$2..$H$5)
CONDITION →
1. IF PERCENTAGE GREATER & EQUAL 80% THEN GRADE=A+
2. IF PERCENTAGE GREATER & EQUAL 60% THEN GRADE=A
3. IF PERCENTAGE GREATER & EQUAL 45% THEN GRADE=B+
4. IF PERCENTAGE GREATER & EQUAL 30% THEN GRADE=B
5. IF PERCENTAGE LESS THAN 30% THEN GRADE=C

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 5 of 19
MICROSOFT OFFICE EXCEL 2007

Q. To store Name, City and Basic Salary after that find TA, DA, HRA and Gross Salary where
the conditions are given.
NAME CITY BASIC SALARY TA DA HRA GROSS SALARY
ROHAN SINHA GUMLA 45000 ? ? ? ?
SAKET SHARMA RANCHI 23000 ? ? ? ?
DIPTI JHA BOKARO 20500 ? ? ? ?
TANIYA SINHA GUMLA 15000 ? ? ? ?
TA → =IF(C2>=25000,C2*15%,IF(C2>=15000,C2*10%,IF(C2<15000,C2*5%)))
DA → =IF(C2>=25000,C2*15%,IF(C2>=15000,C2*10%,IF(C2<15000,C2*5%)))
HRA → =IF(C2>=25000,C2*15%,IF(C2>=15000,C2*10%,IF(C2<15000,C2*5%)))
GROSS SALARY → =C2+D2+E2+F2
Q. To store Name, City and Basic Salary after that find TA, DA, HRA and Gross Salary where
the conditions are given.
NAME CITY BASIC SALARY TA DA HRA GROSS SALARY
ROHAN SINHA GUMLA 45000 ? ? ? ?
SAKET SHARMA RANCHI 23000 ? ? ? ?
DIPTI JHA BOKARO 20500 ? ? ? ?
TANIYA SINHA GUMLA 15000 ? ? ? ?
CONDITION →
1. IF SALARY GREATER & EQUAL 45000 THEN TA 20%, DA 18%, HRA 16%
2. IF SALARY GREATER & EQUAL 25000 THEN TA 17%, DA 16%, HRA 15%
3. IF SALARY LESS THAN 25000 THEN TA 15%, DA 12%, HRA 8%

6 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

Q. To store Product Name, Price and Quantity of 10 items after that find Amount, Discount,
Discount Amount, Payable Amount and Total Amount.
DISCOUNT TOTAL
PRODUCT NAME PRICE QUANTITY AMOUNT DISCOUNT
AMOUNT AMOUNT
SHIRT 500 5 ? ? ? ?
JEANS 1500 3 ? ? ? ?
PANT 800 9 ? ? ? ?
JACKET 3000 4 ? ? ? ?
FROCK 300 6 ? ? ? ?
T-SHIRT 1000 15 ? ? ? ?
SCARF 100 30 ? ? ? ?
SAREE 5000 8 ? ? ? ?
SUIT 3500 4 ? ? ? ?
TOP 600 9 ? ? ? ?
PAYABLE AMOUNT ?
CONDITION →
1. IF QUANTITY IS GREATER THAN OR EQUAL TO 15 THEN DISCOUNT IS 20% OF AMOUNT
2. IF QUANTITY IS GREATER THAN OR EQUAL TO 10 THEN DISCOUNT IS 15% OF AMOUNT
3. IF QUANTITY IS GREATER THAN OR EQUAL T0 5 THEN DISCOUNT IS 10% OF AMOUNT
4. IF QUANTITY IS LESS THAN 10 THEN DISCOUNT IS 5% OF AMOUNT
SOLUTION →
AMOUNT → =B2*C2 TOTAL AMOUNT → =D2-F2 PAYABLE AMOUNT → =SUM(G2..G11)
DISCOUNT → =IF(C2>=15,"20%",IF(C2>=10,"15%",IF(C2>=5,"10%",IF(C2<5,"5%"))))
DISCOUNT AMOUNT→
=IF(C2>=15,D2*20%,IF(C2>=10,D2*15%,IF(C2>=5,D2*10%,IF(C2<5,D2*5%))))

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 7 of 19
MICROSOFT OFFICE EXCEL 2007

Q. To store Product Name, Price and Quantity of 10 items after that find Amount, Prize,
Payable Amount and Total Amount.
PRODUCT NAME PRICE QUANTITY AMOUNT PRIZE

SHIRT 500 5 ? ?
JEANS 1500 3 ? ?
PANT 800 9 ? ?
JACKET 3000 4 ? ?
FROCK 300 6 ? ?
T-SHIRT 1000 15 ? ?
SCARF 100 30 ? ?
SAREE 5000 8 ? ?
SUIT 3500 4 ? ?
TOP 600 9 ? ?
PAYABLE AMOUNT ?
CONDITION →
1. IF AMOUNT IS GREATER THAN OR EQUAL TO 5000 THEN PRIZE IS WRIST WATCH
2. IF AMOUNT IS GREATER THAN OR EQUAL TO 3000 THEN PRIZE IS DIGITAL WATCH
3. IF AMOUNT IS GREATER THAN OR EQUAL TO 1000 THEN PRIZE IS HAND BAG
4. IF AMOUNT IS LESS THAN 1000 THEN PRIZE IS SHIRT
SOLUTION →
AMOUNT → =B2*C2 PAYABLE AMOUNT → =SUM(D2..D11)
PRIZE → =IF(D2>=5000,"WRIST WATCH",IF(D2>=3000,"DIGITAL WATCH",IF(D2>=1000,"HAND
BAG", IF(D2<1000,"SHIRT"))))

8 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

Q. To store Product Name, Price and Quantity of 10 items after that find Amount, Discount on
selected items, Payable Amount, Discount Amount and Total Amount.
DISCOUNT TOTAL
PRODUCT NAME PRICE QUANTITY AMOUNT DISCOUNT
AMOUNT AMOUNT
SHIRT 500 5 ? ? ? ?
JEANS 1500 3 ? ? ? ?
PANT 800 9 ? ? ? ?
JACKET 3000 4 ? ? ? ?
FROCK 300 6 ? ? ? ?
T-SHIRT 1000 15 ? ? ? ?
SCARF 100 30 ? ? ? ?
SAREE 5000 8 ? ? ? ?
SUIT 3500 4 ? ? ? ?
TOP 600 9 ? ? ? ?
PAYABLE AMOUNT ?
CONDITION →
1. IF ITEM IS EQUAL TO SAREE THEN DISCOUNT IS 20% OF AMOUNT
2. IF ITEM IS EQUAL TO JEANS THEN DISCOUNT IS 15% OF AMOUNT
3. IF ITEM IS EQUAL T0 T-SHIRT THEN DISCOUNT 10% OF AMOUNT
4. IF ITEM IS EQUAL TO SHIRT THEN DISCOUNT IS 5% OF AMOUNT
SOLUTION →
AMOUNT → =B2*C2 TOTAL AMOUNT → =D2-F2 PAYABLE AMOUNT → =SUM(G2..G11)
DISCOUNT → =IF(A2="SAREE","20%",IF(A2="JEANS","15%",IF(A2="T-SHIRT","10%",
IF(A2="SHIRT","5%"))))
DISCOUNT AMOUNT→IF(A2="SAREE",D2*20%,IF(A2="JEANS",D2*15%,IF(A2="T-
SHIRT",D2*10%,IF(A2="SHIRT",D2*5%))))

Excel Functions
Functions are the readymade programs which is used to calculate values. Each functions in Ms Excel are
built in , means we don’t have to install it. They can used in numbers, strings and characters.
Functions readymade programs gksrs gSa ftudk iz;ksx values dh x.kuk djus ds fy, djrs gSaA Excel esa ;s
function build in gksrs gSa] eryc bUgas vyx ls Install djus dh t:jr ugha gksrh gSA budk iz;ksx numbers]
Strings rFkk v{kjkas esa fd;k tkrk gSA

1)ABS( ): This function returns the absolute value of given argument.

;s function fn;s x, value dk absolute value fudkyrk gSA
Syntax: =ABS(number)
=ABS(Cell address) 
Example: =ABS(-30) 
Result: 30

2) AVERAGE( ): This function returns the average of given numbers or range of cells.
;s function fn;s x, value vFkok cells ds lewg dk vkSlr fudkyrk gSA
Syntax: =AVERAGE(Num1,Num2,Num…) 
=AVERAGE(Cell Range) 
Example: =AVERAGE(10,20,30,40,50) 
Result: 30

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 9 of 19
MICROSOFT OFFICE EXCEL 2007

3) SUM( ): This function returns the addition/Sum of given numbers or range of cells.
;s function fn;s x, value vFkok cells ds lewg dk ;ksx fudkyrk gSA
Syntax: =SUM(Num1,Num2,…, …., ...) 
=SUM(Cell Range) 
Example: =SUM(10,20,30,40,50) 
Result: 150

4) PRODUCT( ): This function returns the product of given numbers or range of cells.
;s function fn;s x, value vFkok cells ds lewg dk xq.ku[k.M fudkyrk gSA
Syntax: =PRODUCT(Num1,Num2,Num3, …, …) 
=PRODUCT(Cell Range) 
Example: =PRODUCT(1,2,3,4,5) 
Result: 120

5) FACT( ): This function returns the Factorial of a number or a cell.

;s function fn;s x, value vFkok cell dk factorial fudkyrk gSA
Syntax: =FACT(Number) 
=FACT(Cell address ) 
Example: =FACT(5) 
Result: 120

6) MAX( ): This function returns the maximum value from the given numbers or range of cells.
;s function fn;s x, values vFkok cells ds lewg esa ls lcls cM+k value fudkyrk gSA
Syntax: =MAX(Num1,Num2,Num3, …, …) 
=MAX(Cell Range ) 
Example: =MAX(10,220,30,40,50) 
Result: 220

7) MIN( ): This function returns the minimum value from the given numbers or range of cells.
;s function fn;s x, values vFkok cells ds lewg esa ls lcls NksVk value fudkyrk gSA
Syntax: =MIN(Num1,Num2, …, …, ….) 
=MIN(Cell Range) 
Example: =MIN(10,205,30,4,50) 
Result: 4

8) SQRT( ): This function returns the square root of a given number or a cell’s value.
;s function fn;s x, value vFkok cell dk oxZewy fudkyrk gSA
Syntax: =SQRT(Number) 
=SQRT(Cell address) 
Example: =SQRT(16) 
Result: 4

9) POWER( ): This function returns the result of Number raised to given power.
;s function fn;s x, value vFkok cell dks fn;s x,s power ds vuqlkj cjkcj dk value fudkyrk gSA
Syntax: =POWER(Number, Power value) 
=POWER(Cell address, Power value) 
Example: =POWER(5,3) 
Result: 125

10) ROMAN( ): This function returns the roman number for a given Arabic number as Text.
;s function fn;s x, value vFkok cell ds vuqlkj cjkcj dk Roman number fudkyrk gSA
Syntax: =ROMAN(Number) 
=ROMAN(Cell address) 
Example: =ROMAN(15) 
Result: XV

11) MOD( ): This functions returns the remainder of given number after dividing by divisor.

10 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

;s function fn;s x, values dks Hkkx djds ‘ks”kQy fudkyrk gSA

Syntax: =MOD(Number, Divisor) 
=MOD(Cell address, Divisor) 
=MOD(Cell address, Divisor cell address) 
Example: =MOD(20,3) 
Result: 2

12) LEN( ): This functions returns the number of characters in a given text string.
;s function fn;s x, ‘kCn esa v{kjksa dh la[;k fudkyrk gSA
Syntax: =LEN(“String”) 
=LEN(Cell address) 
Example: =LEN(“Media”) 
Result: 5

13) LEFT( ): This function returns the left first character or characters in a text string, based on the
number of characters you specify.
;s function fn;s x, ‘kCn dks ckWa;h vksj ls mrus gh v{kjksa dks i<+ dj fudkyrk gS] ftruh la[;k
ge nsrs gSaA
Syntax: =LEFT(“String”, Number) 
=LEFT((Cell address, Number) 
Example: =LEFT(“Media”,3) 
Result: Med

14) RIGHT ( ): This function returns the right first character or characters in a text string, based on the
number of characters you specify.
;s function fn;s x, ‘kCn dks nkfgus vksj ls mrus gh v{kjkas dks i<+ dj fudkyrk gS] ftruh la[;k
ge nsrs gSaA
Syntax: =RIGHT(“String”, Number) 
=RIGHT(Cell address, Number) 
Example: =RIGHT(“Media”,3) 
Result: dia

15) CHAR( ): This functions returns the character specified by given ASCII code.
;s function fn;s x, ASCII code dk v{kj fudkyrk gSA
Syntax: =CHAR(ASCII Code) 
Example: =CHAR(65) 
Result: A

16) CODE( ): Returns a numeric code for the first character in a text string.
;s function fn;s x, v{kj dk ASCII code fudkyrk gSA
Syntax: =CODE(“Char”) 
Example: =CODE(“A”) 
Result: 65

17) ROUND ( ): This function Rounds a floating number to a specified number of digits.
;s function fn;s x, number eas ls n’keyo ds ckn ds uEcjksa dh la[;k dks de dj ldrs gSaA
Syntax: =ROUND(Floating number, Num digit) 
=ROUND(Cell address, Num digit) 
Example: =ROUND(12.236584,2) 
Result: 12.24

18) INT( ): Rounds a floating number in Integer number down to the nearest integer.
;s function fn;s x, number esa ls n’keyo ds ckn ds lHkh uEcjksa dks gVk ldrs gSaA
Syntax: =INT(Floating Number) 
Example: =INT(45.63985) 
Result: 45
19) COUNT( ): This functions Counts the number of cells that contain numbers and also numbers
within the list of arguments.

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 11 of 19
MICROSOFT OFFICE EXCEL 2007

;s function fn;s x, values esa ls uEcjkas dh la[;k fudkyrk gSA

Syntax: =COUNT(Value, Value, Value, ……, ……)
=COUNT(Cell Range) 
Example: =COUNT(“12”,“er”,5,66,“s”) 
Result: 3

20) LOWER( ): This functions Converts all uppercase letters in a text string to lowercase.
;s function fn;s x, ‘kCn dks vaxzsth ds NksVs v{kjksa esa cny nsrk gSA
Syntax: =LOWER(“STRING”) 
=LOWER(Cell address) 
Example: =LOWER(“MEDIA”) 
Result: media

21) Upper ( ): This function Converts all Lowercase letters in a text string to uppercase.
;s function fn;s x, ‘kCn dks vaxzsth ds cMs+ v{kjksa esa cny nsrk gSA
Syntax: =UPPER(“string”) 
=UPPER(Cell address) 
Example: =UPPER(“media”) 
Result: MEDIA

22) TRIM ( ): This function Removes all spaces from text string except for single space between
words.
;s function fn;s x, ‘kCn esa ls vfrfjDr Lisl dks gVk nsrk gSA
Syntax: =TRIM(“STRING WITH SPACE”) 
Example: =TRIM(“ MEDIA ”) 
Result: MEDIA

23) Date( ): This function converts given arguments into a date.

;s function fn;s x, vadksa dks Date esa cny nsrk gSA
Syntax: =DATE(yyyy,mm,dd) 
Example: =DATE(2010, 2, 6) 
Result: 2/6/2010

24) MONTH( ): This functions returns the month of a date. The month is given as an integer, ranging
from 1 (January) to 12 (December).
;s function fn;s x, date esa ls eghuk fudkyrk gSA
Syntax: =MONTH(“Date”) 
Example: =MONTH(“2/11/2010”) 
Result: 2

25) Year( ): This functions returns the year corresponding to a date. The year is returned as an
integer in the range 1900-9999.
;s function fn;s x, date eas ls o”kZ fudkyrk gS] tks vadks es 1900-9999 ds chp gksrk gSA
Syntax: =YEAR(“Date”) 
Example: =YEAR(“2/11/2010”) 
Result: 2010

26) DAY( ): This functions returns the day of a date. The day is given as an integer ranging from 1 to
31.
;s function fn;s x, date es ls month day fudkyrk gS] tks vadksa es 1-31 ds chp gksrk gS
Syntax: =DAY(“Date”) 
Example: =DAY(“2/11/2010”) 
Result: 11

12 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

27) NOW( ): This function returns the current date and time.
;s function, current date and time fudkyrk gSA
Syntax + Example: =NOW( ) 
Result: 3/20/2010 14:20

28) TODAY ( ): This functions returns current date of computer.

;s function, current date fudkyrk gSA
Syntax + Example: =TODAY( ) 
Result : 3/20/2010

29) WEEKDAY( ): This function returns the day of the week corresponding to a date. The day is given as
an integer, ranging from 1 (Sunday) to 7 (Saturday).
;s function fn;s x, date dk lIrkg ds fnu dk dzekad fudkyrk gS] tks vadks esa 1 (Sunday) ls
7 (Saturday).ds chp gksrk gSA
Syntax: =WEEKDAY(date) 
Example: =WEEKDAY(“3/20/2010”) 
Result: 7

30) MEDIAN( ): This function returns the median, or the middle number of the set of given numbers or
range of cells.
;s function fn;s x, values dk median fudkyrk gSA
Syntax: =MEDIAN(Num1,Num2,Num3…., …..)
=MEDIAN(Cell Range) 
Example: =MEDIAN(10,15,20,30) 
Result: 17.5

31) MID( ): This function returns the characters from the middle of a text string, given a starting
position and length.
;g function fn;s x, ‘kCn eas ls chp ds v{kjksa dks fudkyrk gS] ftlds fy, gesa ‘kq: ds v{kj dk
dzekad rFkk v{kjksa dh la[;k nsuh gksrh gSA
Syntax: =MID(“String”, Start Position, Num Character) 
Example: =MID(“MEDIA INFOTECH”,3,10) 
Result: DIA INFOTE

32) ODD( ): Rounds the positive Number Up and negative number down to the nearest odd Integer
of Even number only.
;s function fn;s x, number dks vkxs c<-k dj utnhdh fo”ke la[;k fudkyrk gSA
Syntax: =ODD(number) 
=ODD(Cell address) 
Example: =ODD(-90) 
Result: -91
Example: =ODD(90) 
Result: 91

33) EVEN( ): Rounds the positive Number Up and negative number down to the nearest Even integer
of Odd number only.
;s function fn;s x, number dks vkxs c<+k dj utnhdh le la[;k fudkyrk gSA
Syntax: =EVEN(number) 
=EVEN(Cell address) 
Example: =EVEN(-91) 
Result: -92
Example: =EVEN(91) 
Result: 92

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 13 of 19
MICROSOFT OFFICE EXCEL 2007

Home Tab

Paste as Hyperlink: To insert only the link of copied cell.

;g copy fd, gq, cell ds dsoy link dks Paste djrk gSA

Orientation: Rotate text to a diagonal angle or vertical orientation.

Text dks diagonal rFkk vertical ?kqekus ds fy,A
Wrap Text: Make all content visible within a cell by displaying it on multiple lines.
Cell esa ekStwn lkjs content dks visible cukdj fofHkUu lines esa n’kkZus ds fy,A
Merge and center: It merges selected cells and changes its alignment to center.
;g selected cells dks merge dj mldk alignment dks center dj nsrk gSA

Number Format: Choose how the values in a cell are displayed: as a percentage, as currency, as a date or
time, etc.
Cell ds value dks percentage, currency, date ;k time bR;kfn ds #i esa pquus ds fy,A
Accounting Number Format: To apply currency style into the selected cells.
Selected cell esa currency style apply djus ds fy, bldk iz;ksx djrs gSaA
Percent Style : To apply percent style into the selected cells.
Selected cell ds value d¨ percent style esa cnYkus ds fy, bldk iz;ksx djrs gSaA
Comma style: To apply comma style into selected cells.
Selected cell ds value esa comma style apply djus ds fy, bldk iz;ksx djrs gSaA
Increase decimal: To increase decimal points into selected cells.
Selected cell ds value esa decimal points d¨ c<+kus ds fy, bldk iz;ksx djrs gSaA
Decrease Decimal: To decrease decimal points into selected cells.
Selected cell ds value esa decimal points d¨ ?kVkus ds fy, bldk iz;ksx djrs gSaA

Conditional Formatting: It helps us to add, change or remove Conditional formatting of selected cell/cells.
It sets the formatting of cell according to the content of cell.
;g gesa selected cell/cells esa condition ds vuqlkj value dks format djus esa enn djrk gSA ;g cell ds
formatting dks mlds value ds vuqlkj set djrk gSA
Format As Table: It helps us to quickly format current table, we can apply the set of formatting into table
that changes its fill color, font color, font, borders etc.
;g gesa current table dks tYn format djus esa enn djrk gS] ge blls vyx & vyx izdkj ds formatting dks
table esa apply dj ldrs gSa, tks blds fill color, font color, font borders bR;kfn dks cny nsrk gSA
Cell Styles: It helps us to change the normal and other styles of excel.
;g gesa excel ds normal vkSj nwljs styles dks cnyus esa enn djrk gSA

Insert: To insert cells into selected cells by shifting existing cells right or down.
blls select fd, gq, cells ds ckbZa vksj ;k Åij esa cells insert gksrk gSA
Delete: It helps us to delete selected cells, rows or columns.

14 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

;g selected cells, rows or columns dks delete djus esa enn djrk gSA
Format: Change the row height or column width, organize sheets, or protect or hide cells.
;g row ds Å¡pkbZ dks cnyus] column ds pkSM+kbZ dks cnyus] sheet dks organize djus protect djus rFkk cells dks
Nqikus esa enn djrk gSA
Row: It provides facility to change the height of selected rows. We can also hide or display selected rows.
;g selected rows ds height dks cnyus ds fy, iz;ksx fd;k tkrk gSA lkFk gh blls ge rows dks fNik ;k fn[kk ldrs
gSaA
Column: It helps us to change the width of selected columns. We can also hide or display them.
;g selected columns ds pkSM+kbZ cnyus esa enn djrk gS] blls ge mUgsa Nqik vFkok fn[kk Hkh ldrs gSaA
Sheet: It helps us to change the name, tab color of current sheet.
;g gesas current sheet dk uke] tab color dks cnyus esa enn djrk gSA

Autosum: It helps us to calculate the sum of selected cells values.

;g gesa pqus gq, cells ds values dks tksM+ dj mldk addition crkrk gSA
Fill: It helps us to insert a series into row or column, we can also copy contents from left, above, right or
down cell/cells.
;g gesa row ;k column eas series insert djus esa enn djrk gSA ge current cell esa mlds left, above , right ;k
down esa ekStwn cell/cells ls contents dks Hkh copy dj ldrs gSaA
Clear: It helps us to clear the format, content, comment or all from selected cell/cells.
;g gesa selected cell/cells ls format, content, comment ;k lHkh dks clear djus esa enn djrk gSA
Sort & Filter: It helps us to sort the selected data in ascending or descending order or tempororarily filter
out specific value.
;g selected data dks c<+rs rFkk ?kVrs dze esa ltkus esa enn djrk gS vkSj fdlh [kkl data dks vLFkk;h :i ls pqudj
vyx djrk gSA
Find & Select: Find and select specific text, formatting, or type of information within the workbook. It is
also replace the information with new text or formatting.
fo'ks”k text, formatting ;k workbook ds vUnj ekStwn tkudkfj;ksa dks <+w<+¡rk vkSj select djrk gSA ;g text vkSj
information dks u, text vkSj formatting esa cnyrk Hkh gSA

Insert Tab

Charts Section: It helps us to insert chart according to selected data.

;g gesa select fd, gq, task ds vuqlkj chart insert djus esa enn djrk gSA

Header and Footer: To set the header and footer.

Header vkSj Footer dks set djus ds fy, bldk iz;ksx djrs gSaA

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 15 of 19
MICROSOFT OFFICE EXCEL 2007

Page Layout Tab

Print Area: To set or remove print area.

blls Print area set ;k remove fd;k tkrk gSA
Breaks: To insert a page break line into the selected cell.
Selected cell esa page break line insert djus ds fy, bldk iz;ksx djrs gaSA
Background: It helps us to choose any picture as a background of current sheet.
;g gesas current sheet esa dksbZ picture choose djds background dks cnyus esa enn djrk gSA
Print titles: Specify rows and columns to repeat on each printed pages.
izR;sd printed pages esa fdlh [kkl rows rFkk columns dks repeat djus ds fy,A

Width: Shrink the width of printed output to fit a maximum number of pages.
,d ls T;knk page dks shrink djds printed output ds pkSM+kbZ esa fit djus ds fy,A
Height: Shrink the heigth of printed output to fit a maximum number of pages.
,d ls T;knk page dks shrink djds printed output ds Å¡pkbZ esa fit djus ds fy,A
Scale: Stretch or shrink the printed output to a percentage of its actual size.
Printed output ds okLrfod size dks percentage ds vk/kkj ij stretch ;k shrink fd;k tk ldrk gSA

View: To show & hide gridlines in the current sheet.

Current sheet esa gridlines dks fn[kkus rFkk Nqikus ds fy,A
Print: To print gridlines in the current sheet.
Current sheet esa gridlines dks print djus ds fy,A
View: To show & hide headings (Name) of row and columns.
Row rFkk columns ds heading vFkkZr~ uke dks fn[kkus rFkk Nqikus ds fy,A
Print: To print headings (Name) of row and columns.
Row rFkk columns ds heading vFkkZr~ uke dks print djus ds fy,A

Formulas Tab

Function Library: To insert different type of function into current cell, functions are ready made small
programs which are used for complex computation like to find Maximum, Minimum, Factorial, Square
root etc..
blls current cell esa fofHkUu izdkj ds function insert fd;k tkrk gS A Functions NksVs & NksVs Programs gksrs gSa,
ftudk iz;ksx tfVy dk;ksZa tSls Maximum, Minimum, Factorial, Square root bR;kfn fudkyus ds fy, fd;k tkrk
gSA

16 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

Trace Precedent: It helps us to display all those cells which are used in the formula of selected formulated
cell through arrows.
Current formulated cell esa iz;ksx fd;s x, cells dks Arrows ds }kjk n’kkZus esa ;s gekjh enn djrk gSA
Trace Dependence: It helps us to display those formulated cells, where the selected cell is used in its
formula through arrows.
Formulated cell esa iz;ksx fd, x, cell ds value dks arrow ds ek/;e ls fn[kkrk gS, ftlds }kjk formula cuk gSA
Remove All Arrows: It removes all the arrows inserted through formula auditing.
Formula auditing }kjk insert fd;s x;s lHkh arrows dks remove djus esa enn djrk gSA
Show Formulas: Display the formula in each cell instead of the resulting values.
izR;sd result okys value ds LFkku ij formula dks n’kkZrk gS] tgk¡ formula fy[kk x;k gksA
Error checking: To find all the formula errors present in active sheet.
Active sheet esa mifLFkr lHkh formula errors dks [kkstus ds fy, bldk iz;ksx djrs gSaA
Evaluate Formula: It is used to open evaluate formula dialog box after that evaluating each part of
formulas individually.
;g evaluate formula ds dialog box dks [kksyrk gS rFkk mlds ckn formula ds izR;sd Hkkx dks ,d&,d dj tk¡prk
gSA

Data Tab

Sort Assending: To sort current table according to selected column into ascending order.
Selected column ds vuqlkj current table dks c<+rs Øe esa ltkus ds fy, bldk iz;ksx djrs gaSA
Sort Desending: To sort current table according to selected column into desending order.
Selected column ds vuqlkj current table dks ?kVrs Øe esa ltkus ds fy, bldk iz;ksx djrs gaSA
Sort: It is used to open sort dialog box.
;g sort ds dialog box dks [kksyrk gSA
Filter: It helps us to filter current table, that displays only similar rows.
;g gesa current table dks filter djus esa enn djrk gS, tks dsoy leku rows dks n’kkZrk gSA
Clear: It removes all the filtration from current table.
;g Filter fd;s x, Table ds lHkh Records dks okil fn[kkrk gSA
Advanced: It helps us to filter current table according to given criteria.
;g gesa current table ds fdlh particular record dks fn;s x;s criteria ds vuqlkj filter djus esa enn djrk gSA

Text to column: It separates the data of a column into different columns.

;g column ds data dks vyx-vyx column esa foHkkftr dj nsrk gSA
Data Validation: It helps us to restrict the limitation in the selected cells for value. We can also insert
validation and error massege for values.
;g gekjh enn selected cell ds value dks lhfer djus ds fy, enn djrk gSA lkFk gh value esa ge validation vkSj
error massege Hkh Mky ldrs gSaA

Group: It helps us to group selected rows and columns.

;g selected rows vkSj columns dks group djus esa enn djrk gSA

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 17 of 19
MICROSOFT OFFICE EXCEL 2007

Ungroup: It helps us to ungroup, grouped rows and columns.

;g group fd;s gq, rows vkSj columns dks ungroup djus esa enn djrk gSA
Subtotals: It helps us to calculate the subtotals by separating data. It automatically inserts the rows and
also calculate grand average/total/maximum etc.
;s gesa data dks vyx&vyx dj mldk subtotal insert djus esa enn djrk gSA ;s Lor% u;s rows insert djrk gS rFkk
grand average/total/maximum insert dj nsrk gSA

Review Tab

New Comment: It is used to insert new comment in selected cell.

;g selected cell esa comment insert djus esa enn djrk gSA
Delete: It is used to remove selected comment.
;g selected comment dks remove djus esa enn djrk gSA
Previous: It is used to jump previous comment.
;g igys okys comment esa jump djus esa enn djrk gSA
Next: It is used to jump next comment.
;g ckn okys comment esa jump djus esa enn djrk gSA
Show/ Hide Comment: It is used to show or hide comment.
;g comment dks fn[kkus rFkk Nqikus esa enn djrk gSA
Show All Comment: It is used to show all comments of worksheet.
;g worksheet ds lkjs comments dks fn[kkrk rFkk Nqikrk gSA

Protect Sheet: It protects current worksheet with a password and also provides facilities to unlock.
;g current worksheet dks Password ls Lock dj nsrk gS vkSj lkFk gh mls unlock djus dh Hkh lqfo/kk nsrk gSA
Track Changes: It records all the changes done into current worksheets area.
;g current worksheet area esa fd;s x;s lHkh cnyko dks record djrk gSA

View Tab

Normal: : When we open Ms excel, it starts in Normal view, this view displays Grid.
Tkc ge Ms Excel dks open djrs gSa] rc og Normal view esa start gksrk gS] blesa gesa Grid fn[kkbZ nsrk gSA
Page Layout: It is used to display the document as it will appear on the printed page.
;g document dks printed page ds :i esa tSlk fn[kkbZ nsxk oSlk n’kkZrk gSA
Page Break Preview: It splits the worksheet into multiple pages. In this view we can see only that area
where data is entered .We can also change the page breaks lines.
;g worksheet dks vusd pages eas split djrk gSA bl view esa ge dsoy ml area dks ns[k ikrs gSa] tgk¡ data dks
enter fd;k x;k gksA blls ge page break lines dks Hkh cny ldrs gSaA

18 of 19 DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY)
 MICROSOFT OFFICE EXCEL 2007

Custom Views: It helps us to save the current view of worksheet. Through this we can easily apply the
customized view.
;g gesa worksheet ds current view dks save djus esa vkSj save fd;s x, Custom View dks apply djus enn
djrk gSA
Full Screen: It is used to display document in full screen mode.
;g document dks full screen mode esa n’kkZrk gSA

Ruler: To hide or display ruler bar.

blls Ruler dks fn[kk;k vFkok Nqik;k tkrk gSA
Gridlines: To hide or display Gridlines.
blls Gridlines dks fn[kk;k vFkok Nqik;k tkrk gSA
Formula Bar: To hide or display formula bar.
blls Formula bar dks fn[kk;k vFkok Nqik;k tkrk gSA
Headings: To hide or display Headings.
blls Headings dks fn[kk;k vFkok Nqik;k tkrk gSA

Zoom to Selection: It is used to zoom selected cells.

;g selected cells dks zoom djus esa enn djrk gSA

Freeze Panes: It freezes all the rows and columns before and above selected cell.
;g selected cell ds igys vkSj Åij ekStwn lHkh rows vkSj columns dks freeze dj nsrk gSA
Split: It splits the workbook into different areas so that we can easily display all the areas of workbook.
;g workbook dks vyx-vyx areas esa split djrk gS] ftlls fd ge workbook ds lHkh areas dks vklkuh ls ns[k
ldrs gSaA
Hide: It helps us to hide current window.
;g gesa current window dks Nqikus esa enn djrk gSA
Unhide: It helps us to unhide, hide window.
;g gesa hide window dks fn[kkus esa enn djrk gSA
Save Workspace: To save all the open workbooks with a name, which is called workspace.
lHkh open workbooks dks ,d uke ds lkFk save djus ds fy, bldk iz;ksx djrs gSa] tks workspace dgykrk gSA

DELTA EDUCATIONAL SYSTEMS PRIVATE LIMITED (AN ISO 9001:2008 CERTIFIED COMPANY) 19 of 19