Excel:
2003
Excel ,d ,e ,l vkWfQl dk ,d software gSA ftldh lgk;rk ls ge
MkVkcsl ij MkVkizkslsflax dk dk;Z dj ldrs gSA ,oa MkVk dks LVksj dj ldrs gSA
blesa MkVkcsl dks eSust djus ds fy;s fofHkUu izdkj ds Vwy gksrs gSA ftldk
iz;ksx djds MkVkcsl dh QkWesafVax dj ldrs gsA bl ,Iyhds”ku lkW¶Vos;j dh ehuw
ckj esa ukS ehuw gksrs gSA blesa fofHkUu izdkj dh dh Vwyckj gksrh gSA ftldk
iz;ksx djds vius dk;Z dks vklkuh ls dj ldrs gSA
blesa ,d ,Iyhds”ku foaMks
gksrh gSA ftlds vUnj odZcqd gksrh gSa odZcqd ds vUnj odZlhV gksrh gSA ,Dlsy esa
MkVk dks odZ”khV esas LVksj fd;k tkrk gSA blesa jks vksj dkWye gksrs gSA jsk
vkSj dkWye ls feydj lsy curh gSa blesa izR;sd lsy dk ,d address gksrk gSA ftls lsy ,sMl dgk
tkrk gSA ;g ,sMªl dkWye vkSj jks ds uke ls feydj cuk gksrk gSA tSls A1, BB10 vkfnA
,d odZlhV esas 65536 jks vkSj 256 dkWye gksrs gSA jsk dk uke Number ls jgrk gSA vkSj dkWye dk uke
alphabet esa jgrk gSA blesa dqy lsy dh la[;k fUkEu gksrh gSA 65536*256=16777216 ,d
odZoqd esa 256 odZlhV gksrh gSA blesa tks
QkbZy curh gSA mldk f}rh;d uke .XLS
gksrk
gSA
odZcqd%& ;g ,d ,sDly
QkbZy gksrh gSA ftlds vUnj dbZ odZlhV gksrh gSA ftlesa MkVk dks LVksj fd;k tkrk
gSA ,d odZcqd ds vUnj 256 odZlhV gksrh gSA ok; fMQkYV rhu odZlhV gksrh
gSA blesa ubZ odZlhV dks tksMk ;k fMfyV fd;k tk ldrk gSA jhuse fd;k tk ldrk gSA
vkSj blesa lhV dks dkWih ewo vkfn dk dk;Z ljyrk ls fd;k tk ldrk gSA odZcqd open djus ij odZlhV
vius vki [kqy gksrh gSA ,d le; esa ,d gh odZcqd ij dk;Z fd;k tkrk ldrk gSA ftls
,sfDVo cdZ lhV dgk tkrk gSA
odZlhV
%&
odZlhV cqd ds ist dh rjg gksrh gSA ftleas ge MkVk dks LVksj dj ldrs gSA ,d
odZlhV esas 65536 jks
vkSj 256 dkWye
gksrs gSA ,oa 65536*256=16777216
lsy
gksrh gSA izR;sd dkWye dk ,d uke gksrk gSA tks ,YQkcsV gksrs gSA ;g jsat A ls IV =256 rd gksrh gSA ,oa
jks dk uke U;wesafjd gksrk gSA bldh jsat 1 To 5536 rd gksrh gSA
bldks jhuse fd;k tk ldrk gSA
lsYk%& jks vkSj dkWye
ds feyus ls lsy curh gSA ,d odZlhV esa 65536*256=16777216 cells gksrh gSA lsy esa MkVk
dks fy[kk tkrk gSA ,d lsy esa 255
v{kj
fy[ks tk ldrs gSA dkWye ,oa jks ds uke dks feykdj lsy dk uke curk gSA ;g lsy dk
,sMl gksrk gSA nks lsy ,sMl feydj jsat ,sMl cukrs gSA blesa nks ;k nks ls vf/kd
lsy dks vkil esa etZ fd;k tk ldrk gSA vkSj lsy dh QkWesfVax dk dk;Z Hkh fd;k
tk
ldrk gSA
What is Excel?
MS Excel is advanced version of lotus
– 123. It is Application software .By using these software we can create
reports, chart and calculation of any row or column or any type of data. The ms
– excel provide many function like as mathematical, statically function etc.
Opening Microsoft Excel - 2003:
- By Start Menu/ Mouse
Command:
“Start” >> “Programs” >> “Microsoft Office”
>> “Microsoft Office Excel 2003.”
- By Run Command:
Window + R >> Run = Excel
·
Extension
Name: .XLS
·
File
Type Name:
Workbook
·
By
Default File Name:
Book1
Ø WHAT
IS WORKBOOK?
Workbook such as a file in which are
kept the spreadsheet and chart, in a workbook we can keep chart, in a workbook,
in a workbook we can keep only one type of or many spreadsheet related to one
work.
Ø
WHAT IS WORKSHEET?
Spreadsheet is also said to the
worksheet. Worksheet is a rectangular sheet in which there are made small
boxes. Every box of worksheet is used to store the data as in the form of table
and it can be moved or copied as per requirement this data can be calculated by
giving formula according to our work.
Ø
WORKSHEET
Spreadsheet is the most important and
the biggest part of excel. Which appears like a graph? As spreadsheet is big in
size it is not completely visible on screen but its right side is always seen
on the screen, from there we can start to input our data. The each and every
small box of spreadsheet is known as cell which we can turn to big or small and
also format is, depending upon the size of our data.
Ø
COLUMN HEADING
In spreadsheet, going from left to
right is not recognized by the number but they are through the alphabets such
as A, B, C they are known as column heading.
Ø Formula
Bar:-
The
formula bar displays the contents of the active cell including any formulas. As
you enter or edit data, the changes will appear in the formula bar.
Ø Name
Box:-
The name box displays the active cell
reference.
Ø Row
- Rows
(horizontal) are labeled by their row headings from 1 thru 65,536.
Ø Column
- Columns
(vertical) are labeled along their column heading A thru Z, AA thru AZ, up to
column XFD.
Ø FILL
HANDLE AND FILL SERIES COMMAND
There are three ways to
click and drag on a cell – each having a different effect.
1. When you have the mouse
inside a cell, the cursor will be shaped like the Swiss Cross. If you click and
drag when this symbol is showing you will select a range of cells.
2. When you point to any
border of a cell the cursor changes to a 4-sided arrow (in Excel XP) or an
arrow (versions prior to XP). Clicking and dragging with this symbol will drag
and drop the contents of the cell (to copy instead of move, hold down the CTRL
key as you drag the contents of the cell).
3.
In
the right-hand bottom corner of every cell there is a small square. If you hold
the mouse above this, the cursor changes into a thick plus sign. In this mode,
if you click and drag, you are using the FILL HANDLE to fill cells you drag
over.
4.
If
the fill handle is not showing, go to TOOLS, OPTIONS, EDIT and ensure the ALLOW
5. CELL DRAG AND DROP option
is enabled.
6.
The
fill handle is a copying tool for copying formulas, and values; however it is
also used to fill in a variety of series such as sequential numbers, dates and
mixed text.
Example:-
|
This
entry
|
Can
be extended to
|
|
1,2,3
|
4,5,6,7 etc
|
|
January
|
February, March, April
etc
|
|
Jan
|
Feb, Mar, Apr etc
|
|
Monday
|
Tuesday, Wednesday,
Thursday etc
|
|
Mon
|
Tue, Wed, Thu etc
|
|
Qtr 1
|
Qtr2, Qtr3, Qtr4
|
|
1 June
|
2 June, 3 June, 4 June
etc
|
|
01/04/04
|
02/04/04, 03/04/04,
04/04/04 etc.
|
|
1st period
|
2nd Period, 3rd Period
etc.
|
|
9.00 am
|
10:00 AM, 11:00 AM etc.
|
F Overview of
formulas
Formulas are equations
that perform calculations on values in your worksheet. A formula starts with an
equal sign (=). For example, the following formula multiplies 2 by 3 and then
ads 5 to the result.
=5+2*3
A formula can also contain
any or all of the following: functions, references, operators, and constants.
Operators specify the type of calculation that you want to perform
on the elements of a formula. There is a default order in which calculations
occur, but you can change this order by using parentheses.
1.
Types of operators
There are four
different types of calculation operators: arithmetic, comparison, text
concatenation, and reference.
1.
Arithmetic operators
2.
Comparison operators
3.
Text concatenation operator
4.
Reference operators
1.
Arithmetic operators
To
perform basic mathematical operations such as addition, subtraction, or
multiplication; combine numbers; and produce numeric results, use the following
arithmetic operators.
|
Arithmetic operator
|
Meaning
|
Example
|
|
+ (plus sign)
|
Addition
|
3+3
|
|
– (minus sign)
|
Subtraction Negation
|
3–1–1
|
|
* (asterisk)
|
Multiplication
|
3*3
|
|
/ (forward slash)
|
Division
|
3/3
|
|
% (percent sign)
|
Percent
|
20%
|
|
^ (caret)
|
Exponentiation)
|
3^2
|
2.
Comparison operators
You
can compare two values with the following operators. When two values are
compared by using this operator.
|
Comparison operator
|
Meaning
|
Example
|
|
= (equal sign)
|
Equal to
|
=10=5
|
|
> (greater than sign)
|
Greater than
|
=10>5
|
|
< (less than sign)
|
Less than
|
=10<5
|
|
>= (greater than or
equal to sign)
|
Greater than or equal to
|
=10>=5
|
|
<= (less than or
equal to sign)
|
Less than or equal to
|
=10<=5
|
|
<> (not equal to
sign)
|
Not equal to
|
=10<>5
|
Actors,
the result are a logical value either TRUE or FALSE.
3.
Text concatenation operator
Use
the ampersand (&) to join, or concatenate, one or more text strings to
produce a single piece of text.
|
Text operator
|
Meaning
|
Example
|
|
& (ampersand)
|
Connects, or
concatenates, two values to produce one continuous text value
|
="North"&"wind"
|
4.
Reference operators
Combine
ranges of cells for calculations with the following operators.
|
Reference operator
|
Meaning
|
Example
|
|
: (colon)
|
Range operator, which
produces one reference to all the cells between two references, including the
two references
|
B5:B15
|
|
, (comma)
|
Union operator, which
combines multiple references into one reference
|
SUM(B5:B15,D5:D15)
|
|
(space)
|
Intersection operator,
which produces on reference to cells common to the two references
|
B7:D7 C6:C8
|
v The order in
which Excel performs operations in formulas
In some cases, the order in which calculation is performed can
affect the return value of the formula, so it's important to understand how the
order is determined and how you can change the order to obtain desired results.
F Calculation
order
Formulas calculate values in a specific order. A formula in Excel
always begins with an equal sign (=). The equal sign tells Excel that the
succeeding characters constitute a formula. Following the equal sign are the
elements to be calculated (the operands), which are separated by calculation
operators. Excel calculates the formula from left to right, according to a
specific order for each operator in the formula.
F The A1
reference style
The default
reference style by default, Excel uses the A1 reference style, which
refers to columns with letters (A through XFD, for a total of 16,384 columns)
and refers to rows with numbers (1 through 1,048,576). These letters and
numbers are called row and column headings. To refer to a cell, enter the
column letter followed by the row number. For example, B2 refers to the cell at
the intersection of column B and row 2.
|
To refer to
|
Use
|
|
The cell in column A and
row 10
|
A10
|
|
The range of cells in
column A and rows 10 through 20
|
A10:A20
|
|
The range of cells in
row 15 and columns B through E
|
B15:E15
|
|
All cells in row 5
|
5:5
|
|
All cells in rows 5
through 10
|
5:10
|
|
All cells in column H
|
H:H
|
|
All cells in columns H
through J
|
H:J
|
|
The range of cells in
columns A through E and rows 10 through 20
|
A10:E20
|
v The difference
between absolute, relative and mixed references
F Relative
references
A relative cell
reference in a formula, such as A1, is based on the relative position of the
cell that contains the formula and the cell the reference refers to. If the
position of the cell that contains the formula changes, the reference is
changed. If you copy or fill the formula across rows or down columns, the
reference automatically adjusts. By default, new formulas use relative
references. For example, if you copy or fill a relative reference in cell B2 to
cell B3, it automatically adjusts from =A1 to =A2.
F Absolute
references
An absolute
cell reference in a formula, such as $A$1, always refer to a cell in a specific
location. If the position of the cell that contains the formula changes, the
absolute reference remains the same. If you copy or fill the formula across
rows or down columns, the absolute reference does not adjust. By default, new
formulas use relative references, and you may need to switch them to absolute
references. For example, if you copy or fill an absolute reference in cell B2
to cell B3, it stays the same in both cells =$A$1.
F
Mixed references
A mixed
reference has either an absolute column and relative row, or absolute row and
relative column. An absolute column reference takes the form $A1, $B1, and so
on. An absolute row reference takes the form A$1, B$1, and so on. If the
position of the cell that contains the formula changes, the relative reference
is changed, and the absolute reference does not change. If you copy or fill the
formula across rows or down columns, the relative reference automatically adjusts,
and the absolute reference does not adjust. For example, if you copy or fill a
mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.
F Common Error in
Excel
1.
##### Error: When your cell contains
this error code,
the column isn't wide enough to display the value.
2.
#NAME? Error: The #NAME? Error occurs when Excel does not recognize text in a
formula.
3.
#VALUE! Error: Excel displays
the #VALUE! error when a formula has the wrong type of argument.
4.
#DIV/0! Error: Excel displays
the #DIV/0! Error when a formula tries to divide a number by 0 or an empty
cell.
5. #REF! Error:
Excel displays the #REF!
Error when a formula refers to a cell that is not valid.
1. Cell C1 references cell
A1 and cell B1.
2. Delete column B. To
achieve this, right click the column B header and click Delete.
3. Select cell B1. The
reference to cell B1 is not valid anymore.
5. To
fix this error, you can either delete +#REF! in the formula of cell B1 or you
can undo your action by clicking Undo in the Quick Access Toolbar (or press
CTRL + z).
Ms Excel Menubar:
blesa
dqy ukS ehuw gksrk gSA tks fuEu gSA
1.
File Menu:
bl ehuw dh lgk;rk ls QkbZy ls
lacaf/kr dk;Z dks fd;k tkrk gSA blds vanj lksyg vki”ku gksrs gSA ftudk iz;ksx
QkbZYk es fd;k tkrk gSA bldh gkWV dh alt+ F gksrh gSA
1.
New:-
bldh lgk;rk ls u;s odZcqd dk
fuekZ.k fd;k tkrk gSA bldh shortcut key ctrl+N gksrh
gSA New ij fDyd djus ij New workbook uke dh ,d foaMks
vkrh gS ftlls ge Blank Workbook ij fDYd djrs gSA ftlls ubZ
odZcqd vksfiu gks tkrh gSA
2.
Open
:- blls igys ls cus odZcqd dks open fd;k tkrk gSA
bldh shortcut
key ctrl+O gksrh
gSA blesa Open
uke
dh foaMks vkrh gS ftlesa ,Dlsy QkbZy dks flysDV djds Open Button ij fDyd djds
QkbZy dks Open
fd;k
tkrk gSA ;k QkbZYk ij Mkcy fDyd djrs gS ftlls ,Dlsy QkbZYk open gks tkrh gSA
3.
Save:-
blls
orZeku esa [kqyh odZcqd dks lso fd;k tkrk gS bldh shortcut key Ctrl+S gS LVSMZM Vwyokj
dh lgk;rk ls Hkh MkD;wesaV dks lso fd;k tk ldrk gSA blesa save in ;g pqurs gSA fd QkbZYk dks
dgkW ij lso djuk vkSj QkbZYk ckWDl esa QkbZy ns dj lso cVu ij fDYkd djrs gSA
ftlsl QkbZYk lso gks tkrh gSA
4. Close:- blls orZeku esa [kqys gq;s MkD;wesaV dks can
fd;k tkrk gSA
5. Save as:- blls orZeku esa [kqys
MkD;wesaV dks iuq% lso fd;k tkrk gS bldh shortcut key F12 gS
tks
egRoiw.kZ MkD;wesaV gksrs gS mudks ges”kk lso ,t djuk pkfg;s A ;g ,d lqj{kk Vwy
gSA
6. Page Setup:- bl vki”ku ls ,Dlsy cdZcqd dh
lsafVax fd tkrh gSA
blds MkW;ykx ckDl esa pkj Vso
gksrs gSA
·
Paper:-
blls
odZcqd dh orientation
, Scaling and paper size dks flysDV djrs gSA
·
Margins
:- bl
Vso ls ist dk ekftZu lsV djrs gSA isij esa pkj ekftZu gksrs gSA ,oa gsMj
,oaQqVj ds fy;s Lisl flysDV djrs gSA
·
Header
/ Footer:- blls
ist esa gsMj ,oa QqVj dks lsV djrs gSA Custom
Button
ij fDyd djds lhV esa gSMj ,oa QqVj yxkrs gSA
·
Sheet:- blesa lhV dk
fizaV ,sfj;k flYksDV djrs gSA ] fizaV VkbZfVy ] lhV ls D;k D;k fizaV djuk gSA
mldks pqurs gSA ,oa fizaV vkMZj flysDV djrs gSA
bl
Mk;ykx ckDl esa rhu cVu gksrs gSA gksrs print,
print priview , option button
7. Print Priview :-
bldk
iz;ksx djds odZcqd dks fizaV djus ds igys ;g ns[k ldrs gSA fd bldk fizaV fdl
izdkj dk vk;sxk A vkSj ;fn dksbZ xyrh gksrh gS rks mldk lq/kkj Hkh ldrs gSA
blds LkkFk fiazV izh O;w Vwyckj vkrh gS ftldh lgk;rk ls fofHkUu izdkj fizaV izh
O;w ns[k ldrs gSA blls le; ,oa iSls nksuks dh cpr gksrh gSA
8. Print:-
bldh
lgk;rk ls MkD;wesaV dk fizaV vkmV fudkyk tkrk gSA bles dbZ vki”ku gksrs gS A ftuls
fofHkUu izdkj ls fizaV fudkyk tk ldrk gSA bl Mk;ykWx ckDl esa fizaVj dk uke ] page range, Number of
copies , print What vkfn
dks lsV djrs gSA ok ij fDyd djds fizaV fudky ldrs gSA
9. Print Area:- bl vki”ku ls fizaV ,sfj;k lsV
djrs gSA ftl ,sfj;k dks fizaV djuk gksrk gSA mldks flysDV djds fizaV ,sfj;k vki”ku
ds vki”ku set print area ij fDyd djds fizaV ,sfj;k lsV
djrs gSA vkSj fizaV ,sfj;k dks fjlsV ;k fMfyV Hkh dj ldrs gSA
10. Exit:- blls MS Excel dks can fd;k tkrk
gSA bldh Shortcut
key alt +F4 gSA
2.
Edit Menu
bl ehuw ls orZeku esa [kqyh
QkbZy esa Editing
dj
ldrs gSA bldh gkWV dh alt+E gSA blesa dqy ianzg
vki”ku
gksrs gSA
1.
Undo:-
bl
vki”ku ls tks dk;Z ;wtj }kjk fd;s tkrs gS mldks fujLr fd;k tkrk gSA bldh “kkVZdV
dh ctrl+U
gSA
2.
:- bl vki”ku dk iz;ksx undo ds
ckn fd;k tkrk gSA bldk eryc gksrk gS tks vuMw fd;k gS mldks iqu% okfil ykukA
bldh “kkVZdV dh Ctrl+Y
gSA
3.
Cut:-
blls
flysDV fd;s x;s eSVj dks dV fd;k tkrk gSA ;g dV gksdj fDyicksMZ esa pyk tkrk
gSA vkSj Paste
djds
MkD;wesaV esa fpidk;k tkrk gSA bldh Sortcut key Ctrl+X gS bldks LVSMMZ ckj ds dV cVu
ij fDyd djd dV fd;k tk ldrk gS bldk jkbZV fDyd djds Hkh dV dj ldrs gSA ekml dh
lgk;rk ls Hkh esVj dks ,d LFkku ls nwljs LFkku ij ewo fd;k tk ldrk gSA
4.
Copy:-
blls
flysDV fd;s x;s eSVj dks dkWih fd;k tkrk gSA ;g dkWih gksdj fDyicksMZ esa pyk
tkrk gSA vkSj Paste
djds
MkD;wesaV esa fpidk;k tkrk gSA bldh Sortcut key Ctrl+C gS bldks LVSMMZ ckj ds dkWih
cVu ij fDyd djd dkWih fd;k tk ldrk gS bldk jkbZV fDyd djds Hkh dkWih dj ldrs gSA
5.
Paste:-
bl
vki”ku dh lgk;rk ls dV ;k dkWih fd;s x;s eSVj dks isLV fd;k tkrk gSA bldh Shortcut
key ctrl+V gksrh
gSA LVSMMZ Vwy ckj dh lgk;rk ls Hkh isLV fd;k tkrk gSA ;k jkbZV fDyd djds paste fd;k tkrk gSA
6.
Paste special :-
bl
vki”ku dh lgk;rk ls dV ;k dkWih fd;s x;s eSVj dks Lis”ky isLV fd;k tkrk gSA
bles ,d MkW;ykx ckDl vkrk gSA ftlls fofHkUUk izdkj ls isLV dj ldrs gSA ;g ,d
cgqr gh mi;ksxh Vwy gksrk gSA
7.
Clear :-
blls
ge VsDl ;k mldh Formatting dks fMfyV dj ldrs gSA bldh “kkVZdV
dh del gSA
blesa eSVj dks flysDV djuk iMrk gSA
8.
Select all:-
blls
iwjs MkD;wesaV ds eSVj dks ,d LkkFk flysDV dj ldrs gSA bldh Shortcut key ctrl+A gksrh gSA
9.
Find:-
bl
vki”ku ls odZcqd ,oa odZlhV esa fdlh Hkh VSDl] “kCn ,oa okD; dks Find fd;k tk ldrk gSAbldh shortcut key ctrl+F gSA more option esa fofHkUu izdkj
ls Searching
dj
ldrs gSA
10. Replace:-
bl
vki”ku ls odZcqd ,oa odZlhV esa fdlh Hkh VSDl] “kCn ,oa okD; dks Replace fd;k tk ldrk gSA
bldh shortcut
key ctrl+H gSA
more option esa fofHkUu izdkj ds vki”ku flysDV dj ldrs gSA
blesa pkj cVu gksrs gSA
·
Replace Button blls ,d ,d “kCn Replace gksrk gSA
·
Replace all Button:- blls ,d lkFk
iwjs MkD;wesaV esa ,d LkkFk Replace gksrk gSA
·
Find Next Button: blls word dks find
dj
ldrs gSA
·
Find What text box: esa ml “kCn dks
fy[krs ftldks replace djuk gksrk gSA
·
Replace With: esa ftlls replace
djuk
gksrk gSA mldks fy[kuk gksrk gSA
11. Goto:-
blls
odZcqd ,oa odZlhV es fdlh Hkh lsy ij tk ldrs gSA bldh Shortcut
key ctrl+G gSA
blesa ml lsy dk use nsuk gksrk gSA
3. View Menu:
bl ehuw dk iz;ksx MkD;wesaV
dks fofHkUu O;w esa ns[kus ds fy;s fd;k tkrk gS A ,oa “kks ,oa gVk;k tk ldrk
gSA
blesa dqy pkSng vki”ku gksrs
gSA tks fUkEu gSA
1.
Normal:- bl vki”ku ls lhV
dks ukWeZy fLFkfr esa ns[kk tk ldrk gSA
2.
Page Break Priview:- print
area set djus
ds ckn ist czsd fizO;w esa ns[krs gSA rks lhV esa ftruk fizaV ,sfj;k lsV gksrk
gS mruh lhV fn[kkbZ nsrh gSA bldh lkbZt dks [khpdj cMk fd;k tk ldrk gSA blesa
dk;Z djus esa vklkuh gksrh gSA
3.
Toolbars:- MS Excel dh lHkh Vwyckj dks ;gkW ls
yk;k ,oa gVk;k tkrk gSA blesa dqy vBkjg Vwyckj gksrh gSA vkSj vko”;drk ds
vuqlkj Vwyckj dk fuekZ.k Hkh fd;k tk ldrk gSA ;g Vwyckj vius vki dk;Z ds vuqlkj
vk tkrh gSA vkSj gV tkrh gSA
4.
Formula
Bar :- Formula bar excel dh ,d egRoiw.kZ okj gksrh gS bldh lgk;rk ls
lsy esa MkVk ]QkWewZyk ,oa Functions
dk
iz;ksx fd;k tkrk gSA
5.
Full
Screen:- bl
vki”ku ls MkD;wesaV dks Qwy Ldhu esa ns[kk tk ldrk gSA
6. Zoom:- bl vki”ku ls MkD;wesaV dks
fofHkUu izdkj ls twe djds ns[kk tk ldrk gSA
7.
Header & Footer:-
bl vki”ku ls ,Dlsy lhV esa
gsMj ,oa QqVj dks yxk ldrs gS blesa tks esVj lsV fd;k tkrk gSA og izR;sd ist ij
vkrk gSA gsMj yxkkus ds fy;s custom
header ij
fDyd djrs gSA QqVj yxkus ds fy;s Custom
Footer ij
fDyd djrs gSA vkSj QqVj ;k gsMj dks lsV djrs gSA
8.
Custom header or footer ij fDyd djus ij
fuEu foaMks vkrh gSA bles rhu section
gksrs
gSA ftlesa ist
uacj
;k dksbZ vU; gsfMax yxk ldrsa gSA
4. Insert Menu:
bl ehuw dh lgk;rk ls
MkD;wesaV esa fofHkUu izdkj ds vkCtsDV dks tksMk tkrk gSA blesa dqy ianzg vki”ku
gksrs
gSA
1.
Cells: bl vki”ku ls lhV
esa lsy dks f”k¶V fd;k tkrk gSA insert menu ds bl vki”ku ij
fDyd djus ij fuEu Mk;ykx ckDl vkrk gSA blesa lsy dks nks izdkj ls f”k¶V fd;k
tkrk gSA blds igys vki”ku dks flysDV djus ij orZeku lsy dk MkVk jkbZV lkbZM f”k¶V
gks tkrk gSAvkSj og lsy [kkyh gks tkrh gSA nwljs vki”ku dks flysDV djus ij
orZeku lsy dk MkVk uhps okyh lsy esa f”k¶V gks tkrk gS vko”;drk ds vuqlkj vki”ku
dks flysDV djrs gSA
2. Rows(jks):- bl vki”ku ls lhV
esa jks dks tksMk tk ldrk gS blds fy;s entire
row redio button
dks pqurs gSAvkSj ok
button ij
fDYkd djus ij jks lhV esa tqM tkrh gSA lhV esa nks izdkj ls jks dks tksMk tkrk
gSaSA
1.
Insert menu " Rows
2. Right Click on Cell" insert
" select
entire row " ok
3. Columns(dkWye):- bl vki”ku ls lhV esa dkWye
dks tksMk tk ldrk gS blds fy;s entire
column redio button dks
pqurs gSAvkSj ok
button ij
fDYkd djus ij dkWye lhV esa tqM tkrk gSA lhV esa nks izdkj ls dkWye dks tksMk tkrk
gSaSA
Insert menu " column
Right
Click on Cell" insert
" select
entire column " ok
WorkSheet(odZlhV):- bl vki”ku ls odZcqd esa ubZ
odZlhV dks tksMk tkrk gS
Ø odZcqd esa nsk
izdkj ls odZlhV dks tksMk tkrk gSA
Insert menu "
worksheet
Right Click on sheet"
insert
"
worksheet"
ok
4. Function: - ,sDly esa igys
ls ifjHkkf"kr QkeZwys gksrs gS Aftuds }kjk fofHkUu izdkj ds dk;Z fd;s tk
ldrs gSA Function dk tkrk gSA ;g MkVk ds izdkj
ij vusd izdkj ds gksrs gSA Tksk fuEu gSA
1.
Math or String
2.
Date
3.
Text
4.
Financial
5.
Logical
6.
Statically
7. Lookup or reference
Funtion dk iz;ksx djuk
%&
bldk iz;ksx nks izdkj ls fd;k
tkrk gSA
Director Type djds%& blesa Funtion dks = ds fpUg ds ckn VkbZi dj nsrs
gS vkSj mlesa argument insert djk nsrs gSA vkSj
var esa baVj ;k fDyd djds mldk ifj.kke izkIr dj ldrs gSA
Menu ds }kjk %&
Insert menu "
Function
ij
fDyd djus ij Function Mk;ykWx ckDl vkrk gSA ftlesa
QkUd”ku flysDV djrs gSA vkSj ok button ij fDyd djrs gSaA vkSj mlds ckn mlesa
mlds vkWxwZesaV nsrs gS vkSj ok
Button
ij fDYkd djrs gSA
Chart
Excel Data dks xzkQ esa iznf”kZr fd;k tk
ldrk gSA MkVk dks pkVZ ls le>us esa vklkuh gksRkh gSA ,sDlsy es
bldks
fotkMZ ds }kjk cuk;k tkrk gSA
Excel esa pkVZ cukuk%& blesa
pkVZ dks pkj LVSi esa cuk;k tkrk gSA tks fuEu gSA
Step First:-
Insert menu Chart
Or
Standard
Tool Bar è
Click
on Chart Button
pkVZ cVu ij fDyd djus ij chart wizard uke dk Mk;ykWx ckDl vkrk gSA
blesa pkVZ ds izdkj dks pqurs gSA ,sDlsy esa pkSng izdkj ds pkVZ gksrs gSA ftl
pkVZ dks flysDV djrs muds lc pkVZ dks lkbM esa izfn”kZr gksrs gSA ftlesa ls vko”;drk
ds vuqlkj pkVZ dks pqurs gSA
vkSj Next Button ij fDYkd djrs gSA
Step Second: - Second Step esa pkVZ ds fy;s
MkVkcsl dks pqurs gSA bl Mk;ykWXk ckDl esa nks Vsc gksrs gSA izFke Vsc esa MkVk
jsat nsrs gSA ,oa ;g pqurs gSA fd MkVk jks esa gSA ;k dkWye esa nwljs Vsc series dk gksrk gSA blesa lhjht dk
uke ,oa mldh jsat nsrs gSA blesa ubZ lhjht dks tksMk tk ldrk gSA ,oa igys ls
mifLFkr lhjht dks fMfyV fd;k tk ldrk gSA ,oa X Axis ij tks MkVk iznf”kZr djuk gSA
mldh jsat nsrs gSA
vkSj Next Button ij fDyd djrs gSA
Step Third: - bl aMk;ykWx ckDl esa N% Vsc
gksrs gSA ftudh lgk;rk ls pkVZ dh lsafVax fd tkrh gSA
;g
Vsc fuEu gSA
1. Titles:- blesa pkVZ dk VkbZfVy X and Y Axis dk VkbZfVy nsrs gSA
2. Axes: bl Vsc ls ;g fu/kkZfjr djrs
gSA fd pkVZ esa X and Y Axes ij ysfcy iznf”Zkr djuk gS ;k
ughA
3. Gridlines :- bl Vsc esa pkVZ esa Gridlines dks fu/kZkfjr fd;k tkrk gSA
4. Legend :- :- bl Vsc esa pkVZ
esa legend dh fLFkfr dks fu/kZkfjr fd;k
tkrk gSA fd Legend dks
pkVZ esa dgkW ij “kks djuk
gSA
5. Data label :- chart esa ysfcy ds rkSj
ij D;k iznf”kZr djkuk gSA bldks pqurs gSA
6.
Data Table:- Chart ds
MkVk dh Vsfcy dks “kks djuk gS ;k ugh bldks lsV fd;k tkkrk gSA
blh izdkj lHkh lsfVaXk djus
ds ckn Next Button ij fDyd djrs gSA
Step Fourth:- bl LVsi esa ;g fu/kkZfjr fd;k
tkrk gSA fd pkVZ dks dgkW ij yksdsV djuk gS orZeku lhV ij ;k ubZ lhV ij A
vkSj Finish Button ij fDYkd djrs gh pkVZ dk
fuekZ.k gks tkrk gSA
blds
ckn ml ij jkbZV fDyd djds mldh Formatting dh tk ldrh gSA
5. Format
Menu:
bl ehuw dh lgk;rk ls
MkD;wesaV dh formatting
dh
tk ldrh gSA blesa lkSyg vki”ku gksrs gSA bldh gkWV
dh alt+o gSA
1.
Cell:- QkWeZV
ehuw ds bl vki”ku ls lsy dh QkWeZVsafxa dj ldrs gSA blls jks ;k dkWye esa vyx vyx
izdkj ds MkVk VkbZi dks lsV dj ldrs gSA blds Mk;ykWx ckDl esa N% Vsc gksrs gSA
ftlsl vyx vyx izdkj dh QkWesaZfVax gksrh gSA
Number
Tab:-
Data Type:-
Data Type lsV djus ds
fy;s jks ] dkWye ;k ,sfj;k dks flysDV djuk gksrk gSA blds ckn MkVk VkbZi dks lsV
djuk gksrk gSA ,sDlsy esa ckjg izdkj ds MkVk VkbZi gksrs gSA tks fuEu gSA
General :- bl MsVk VkbZi esa
lHkh izdkj ds MkVk dks LVksj fd;k tkrk gSA ;g ,d dkWeu MkVk VkbZi gSA
Number:- bl MsVk VkbZi esa
Uakcj izdkj ds MkVk dks LVksj fd;k tkrk gSA blesa n”keyo ds vadks dks Hkh lsV
djuk gksrk gksrk gSA
Currency :- bl MsVk VkbZi esa
djsalh izdkj ds MkVk dks LVksj fd;k tkrk gSA blesa n”keyo ds vadks dks ,oa
djsalh fpUg dks lsV djuk gksrk gksrk gSA
Date :- bl MsVk VkbZi esa
MsV izdkj ds MkVk dks LVksj fd;k tkrk gSA blesa MsV ds QkWeZV dks flysDV djuk
gksrk gSA
Time :- bl MsVk VkbZi esa
VkbZe izdkj ds MkVk dks LVksj fd;k tkrk gSA blesa VkbZe ds QkWeZV dks flysDV
djuk gksrk gSA
Percentage:- bl MsVk VkbZi
esa Percentage izdkj ds MkVk dks
LVksj fd;k tkrk gSA blesa n”keyo ds vadks dks Hkh lsV djuk gksrk gksrk gSA
blesa Percentage foUg vkrk gSA
Scientific:- bl MsVk VkbZi esa
uacj izdkj ds MkVk dks LVksj fd;k tkrk gSA blesa n”keyo ds vadks dks lsV djuk
gksrk gksrk gSA
Text :- bl MsVk VkbZi esa
VsDl izdkj ds MkVk dks LVksj fd;k tkrk gSA bles xf.krh;s x.kuk;sa ugh dh tk ldrh
gSA
Special :- bl MsVk VkbZi esa
fo”sk"k izdkj ds MkVk dks LVksj fd;k tkrk gSA fti dksM ,oa Qksu uacj vkfn
dks LVksj fd;k tkrk gSA
Accounting:- bl MsVk VkbZi esa
uacj izdkj ds MkVk dks LVksj fd;k tkrk gSA blesa n”keyo ds vadks dks lsV djuk
gksrk gksrk gSA ,oa dkjsalh dk fpUg pquk iMk gSA
Custom:- bl MsVk VkbZi esa vko”;drk ds
vuqlkj MkVk ds izdkj dks lsV dj ldrs gSA
Alignment Tab:-
bl VSc ls lsy dk Alignment lsV djrs gSA ,oa
lsy dks wrap djuk ] Marge djuk ,oa Sink to Fit fd;k tk ldrk gSA
blds vykok VSDl dks jksVsV ¼?kqek½ ldrs gSA
Font :-
bl
VSc ls cdZlhV dk QkW.V ] lkbZTk ] LVkbZy ,oa QkW.V dyj dks cnyk tk ldrk gSA
Border:-
bl VSc ls lsy esa okMZj yxk
ldrs gSA
Patterns:-
bl
VSc ls lsy dk cSdxzkmM dyj lsV fd;k tk ldrk gSA
Height
:- bl
vki”ku ij fDyd djus ij fuEu Mk;ykWXk ckDl vkrk gSA blesa jks gkbZV dks VkbZi
djds ok ij fDYd djrs gSA
ftl
jsk dh gkbZV de ;k T;knk djuk gksrh gS mldks flysDV djuk gksrk gSA
Auto Fit :- row
Text ds
vuqlkj vius vki jks lsV gks tkrh gSA blds vkWVks fQV dgk tkrk gSA
fNikuk (Hide):-
bl
vki”ku ls jsk dks fNik;k tk ldrk gSA
fn[kkuk (Unhide):-
bl
vki”ku ls jsk dks bugkbZM fd;k tk ldrk gSA
dkWye (Colum):-
bl
vki”kWu ls dkWye dh pkSMkbZ dks cnyk tk ldrk gSA blesa pkj lc vki”ku gksrs gsA
Width :-
bl
vki”ku ij fDyd djus ij fuEu Mk;ykWXk ckDl vkrk gSA blesa dkWye dh pkSMkbZ dks
VkbZi djds ok
ij
fDYd djrs gSA
ftlls dkWye dh pkSMkbZ dks de
;k T;knk fd;k tk ldrk gS ftl dkWye dh pkSMkbZ dks de ;k T;knk djuk gksrk gSA
mldks flysDV djuk gksrk gSA
Auto Fit
Selection :- Column Text ds vuqlkj vius vki dkWye lsV gks tkrk gSA
bldsk vkWVks fQV dgk tkrk gSA
fNikuk (Hide):-
bl
vki”ku ls dkWye dks fNik;k tk ldrk gSA
fn[kkuk (Unhide):-
bl
vki”ku ls dkWye dks mugkbZM fd;k tk ldrk gSA
lhV(Sheet):- blls ge lhV dh QkesaZfVsx dh
tkrh gSA blesa esa ikWp vki”ku gksrs gSA
jhuse (Rename):-
blls
orZEkku lhV dks fjuse fd;k tk ldrk gSA lhV dks nks izdkj ls fjuse fd;k tkrk gSA
1. Format
menu è sheetèrename
ètype
New Nameèenter
2. Right
Click on Sheet nameèRenameèType
New NameèEnter
Hide:- blls lhV dks fNik;k tk ldrk
gSA tks egRoiw.kZ lhV gksrh gSA mldks ge fNik ldrs gSA
Format
MenuèSheet èHide
Format
MenuèèUnhide
BackGround:- blls lhV ds cSdxzkm.M esa
fiDpj dks lsV dj ldrs gSA
Tab
Color:- blls
lhV ds VSc esa dyj lsV dj ldrs gSA
Conditional Formatting :- QkesZV ehuw ds bl
vki”ku ls “kZr ds vk/kkj ij QkWesZfVZx fd tk ldrh gSA tks vyx vyx MkVk ij vyx
vyx QkWesafVax dj ldrs gSA blesa ,d “kzZr gksrh gSA mlds fy;s uhps QkWesaV CkVu
gksrk gsA ftlls QkesafVax djrs gSA ,d lkFk dbZ dUMh”ku yxk ldrs gSA
6.
Tool Menu:
Goal Seek:- Goal Seek ls ,sDlsy esa Answer ds vk/kkj ij value dsk cnyk tkrk gSA vFkkZr~ Calculation ds mRrj irk gksrk gSA mlds
vk/kkj ij Goal Seek ls Value vius vki cny tkrh gSA Tool menu è Goal Seek
tgkW ij Goal Seek dk iz;ksx djuk gS aogkW ij
igys ls Formula set gksuk pkfg;s A To Change VsDl ckWDl esa ml Value dks fy[krs gSA tks Result esa pkfg;s A By Change Cell VSDl ckWDl esa ml lsy dk Address nsrs gS ftl dks cnyuk gksrk
gSA blds ckn ok Button ij fDYkd djrs gSA
Macro:- Macro dh lgk;rk ls ge
fdlh Hkh dk;Z dks fjdkMZ dj dj ldrs gSA vkSj vko”;drk iMus ij ml esdzks dks ju
dj nsrs gSA rks og dk;Z dqN gh lsdsaM esa gks tkrk gSA ;g ,d cgqr gh mi;ksxh
Vwy gSA
Macro dks Record djuk%&
Tool
Menu èMacro èRecord
New Macro ij
fDyd djus ij fuEu foaMks vkrh gSA
blesa igys VsDl ckWDl esa Macro dk uke nsrs gSA ] Shortcut key esa dh dks nsrs gSA vkSj Store Macro in esa ;g fu/kkZfjr djrs gSA fd
esadzks dks dgkW ij LVksj djuk gSA blds ckn ok Button ij fDYkd djrs rks esdzks dh
fjdkfMZsx gksuk pkyw gks tkrh gSA
Recording djus ds ckn Recording dks stop djrs gSA vkSj fQj tc Hkh ml
dk;Z dks iqUk% djuk gksrk gSA rks lh/ks esadzks dks ju dj nsrs gSA rks og dk;Z
vius vki dqN gh lsdsM esa gks tkrk gSA ,d ckj esdzks dks fjdkMZ djus ls le; dh
cpr gksrh gSA vkSj og dk;Z T;ska dk R;kS gks tkrk gSA
Protaction: - bl vki”ku ls lhV ,oa odZcqd
nks esa ikloMZ dks yxk;k tk ldrk gSA
7. Data
menu:-
bl
ehuw ls MkVk ls lacaf/kr dk;Z dks fd;k tkrk gSA
Sorting :- bl vki”ku ls ,sDlsy lhV ds
MkVk dks Sort fd;k tk ldrk gSA Sorting
nsk
izdkj dh gksrh gSA
1. Ascending :- blesa A To Z dze esa MkVk dks “kkWVZ fd;k
tkrk gSA
2.
Descending blesa Z To A dze esa MkVk dks “kkWVZ fd;k
tkrk gSA
ftl dkWye ds vk/kkj ij Sorting djuh gSA mldks flysDV djrs
gSA vkSj mlds izdkj dks pqurs gSA ,d lkFk ,d ls vf/kd dkWye dh Sorting dh tk ldrh gSA bl dk;Z dks
Standard Tool bar dh lgk;rk ls Hkh fd;k tk ldrk gSA
Filter: - Data Menu ds bl vki”ku ls lhV ds MkVk
dks QhYVj fd;k tk ldrk gSA blesa fofHkUu izdkj dh “krksZ ds vk/kkj ij Filtering fd tk ldrh gSA
Validation :- bl vki”ku ls lhV
esa MkVk dks input djus ds fy;s MkVk ds vk/kkj
ij Validation rS;kj dj ldrs gSA ;g dkWye
;k jsk dks lhysDV djds ykxk;k tkrk gSA vyx vyx dkWYke esa vyx vyx Validation yxkk ldrs gSA lhV esa MkVk
dks QhM djus ds fy;s ;g ,d egRoiw.kZ vki”ku gSa blls MkVk QhfMax esa dksbZ Error ugh gks ldrh gSA
8. Windows Menu:-
bl ehuw ls ,e,l ,sDlsy esa ,d
ls vf/kd foaMksl [kqyh gksrh gS rks mu foaMksl dks eSaust fd;k tkrk gSA ,oa ,e,l
,sDlsy MkD;wesaV foMks dks spilt fd;k tk ldrk gSA bl vki”ku ls lhV dks Freez fd;k tk ldrk gSA Freeze djus ij ml ,sfj;k dks ewo
ugh fd;k tk ldrk gSA Data input djrs le; lhV dh gsMj dks
Qhfjt fd;k tkrk gSA ftlls MkVk dks lgh rjhds ls buiqV fd;k tk ldrk gSA dkWYke
dsk Freeze djus ds ckn Unfreeze fd;k tkrk gSA
9. Help Menu:- blls ,e,l ,sDlsy
ds ckjs esa gsYi ys ldrs gSA bldh “kkVZdV dh F1 gSA
Microsoft Excel
Functions
v Math
& Trig Functions
1. ABS: Returns the absolute value of
a number. The absolute value of a number is the number without its sign.
Syntax: ABS (number)
Example: =ABS (-4) Ans
: 4
2.
SIN: Returns the
sine of the given angle.
Syntax: SIN (number)
Example: =SIN(30*PI()/180) Ans: 0.5
3.
COS: Returns the
cosine of the given angle.
Syntax : COS(number)
Example: =COS(60*PI()/180) Ans:
0.5
4. TAN: Returns the tangent of the
given angle.
Syntax : TAN(number)
Example: =TAN(45*PI()/180) Ans: 1
5.
DEGREES: Converts
radians into degrees.
Syntax:
DEGREES(angle)
Example: =DEGREES(PI( )) Ans : 180
6. EVEN: Returns number
rounded up to the nearest even integer.
Syntax: EVEN(number)
Example: =EVEN(3) Ans: 4
7.
EXP: Returns ‘e’
raised to the power of number. The constant ‘e’ equals 2.71828182845904, the
base of the natural logarithm.
Syntax: EXP(number)
Number is the exponent applied to the base ‘e’.
Example: =EXP(2) Ans: 7.389056
- FACT: Returns the factorial
of a number. The factorial of a number is equal to 1*2*3*...* number.
Syntax:
FACT(number)
Example: =FACT(5) Ans: 120( 5×4×3×2×1=120)
9. GCD: Returns
the greatest common divisor of two or more integers. The greatest common
divisor is the largest integer that divides both number1 and number2 without a
remainder.
Syntax: GCD(number1,number2, ...)
Example: =GCD(25,15) Ans: 5
10.
INT:
Rounds
a number down to the nearest integer.
Syntax:
INT(number)
Example: =INT(8.9) Ans: 8
11. LCM: Returns the
least common multiple of integers. The least common multiple is the smallest
positive integer that is a multiple of all integer arguments number1, number2,
and so on.
Syntax:
LCM (number1, number2…)
Example: =LCM(5, 2) Ans: 10
12. LOG: Returns the
logarithm of a number to the base you specify.
Syntax: LOG(number,base)
Example: =LOG(2,10) Ans: 0.30103
13. LOG10: Returns the
base-10 logarithm of a number.
Syntax: LOG10(number)
Example: =LOG10(2) Ans:
0.30103
14.
MOD: Returns the remainder after number is divided by divisor. The
result has the same sign as divisor.
Syntax: MOD (number, divisor)
Example: =MOD(5, 2) Ans: 10
15. ODD: Returns number
rounded up to the nearest odd integer
Syntax: odd(number)
Example: =odd(2) Ans: 3
16. PI: Returns the
number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
Syntax: PI( )
Example:
=PI ( ) AnS:
3.14159265358979
17. POWER: Returns the
result of a number raised to a power.
Syntax: POWER (number, power)
Example: =POWER (5, 2) Ans: 25 (52 = 25)
18. PRODUCT: Multiplies all
the numbers given as arguments and returns the product.
Syntax: PRODUCT (number1, number2…)
Example: =PRODUCT (5, 4, 2) Ans: 40
19. ROMAN: Converts an
arabic numeral to roman, as text.
Syntax: ROMAN (number, [form])
Example: =ROMAN(20,2) Ans: XX
20. ROUND: Let's say you
want to round a number to the nearest whole number because decimal values are
not significant to you or round a number to multiples of 10 to simplify an
approximation of amounts.
Syntax: ROUND(number,
num_digits)
Example: =ROUND(12.3456,2) Ans: 12.35
21. SQRT: Returns a positive square
root.
Syntax: SQRT(number)
Example: = SQRT(16) Ans: 4
22.
SUM: Adds all the
numbers in a range of cells.
Syntax: SUM(number1,
number2, number3…)
Example: =SUM(4,5,6) Ans: 15
23. SUMIF: Adds the cells
specified by a given criteria.
Syntax: SUMIF(range, criteria,
sum_ range)
Example:
|
A
|
B
|
|
Property
Value
|
Commission
|
|
100,000
|
7,000
|
|
200,000
|
14,000
|
|
300,000
|
21,000
|
|
400,000
|
28,000
|
|
Formula
|
Description
(Result)
|
|
=SUMIF(A2:A5,">160000",B2:B5)
|
·
Sum of the commissions for property values over 160,000
Ans: 63,000
|
|
=SUMIF(A2:A5,">160000")
|
·
Sum of the property values over 160,000
Ans: 900,000
|
|
=SUMIF(A2:A5,"=300000",B2:B3)
|
·
Sum of the commissions for property values equal to 300,000
Ans:
21,000
|
24. TRUNC: Truncates a
number to an integer by removing the fractional part of the number.
Syntax: TRUNC(number,num_digits)
Example: =TRUNC(1256.5687,2) Ans: 1256.56
v Logical
Functions
1. AND: Returns TRUE if all its
arguments are TRUE; returns FALSE if one or more argument is FALSE.
Syntax:
AND(logical1,logical2, ...)
Example: =AND(5>2,6>3) Ans: True
=AND(5<2,6>3) Ans: False
=AND(5>2,6<3) Ans: False
=AND(5<2,6<3) Ans: False
2. IF: Returns one value if a
condition you specify evaluates to TRUE and another value if it evaluates to
FALSE. Use IF to conduct conditional tests on values and formulas.
Syntax:
IF(logical_test,value_if_true,value_if_false)
Example: =IF(10>100,"Excellent","Not Excellent") Ans: Not Excellent
3. NOT: Reverses the value of its
argument. Use NOT when you want to make sure a value is not equal to one
particular value.
Syntax: NOT(logical)
Example: =NOT(5>2) Ans: False
=NOT(5<2) Ans: True
4. OR: Returns TRUE if any
argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax:
OR(logical1,logical2,...)
Example: =OR(5>2,6>3) Ans: True
=OR(5<2,6>3) Ans: True
=OR(5>2,6<3) Ans: True
=OR(5<2,6<3) Ans: False
v Statistical
Functions
1. AVERAGE: Returns the average
(arithmetic mean) of the arguments.
Syntax:
AVERAGE(number1,number2,...)
|
|
A
|
|
1
|
Data
|
|
2
|
10
|
|
3
|
7
|
|
4
|
9
|
|
5
|
27
|
|
6
|
2
|
Example: =average(A2:A6) [from table 1] Ans:
11
|
|
A
|
|
1
|
Data
|
|
2
|
Sales
|
|
3
|
6/9/1990
|
|
4
|
|
|
5
|
19
|
|
6
|
22.24
|
|
7
|
True
|
|
8
|
#DIV/0!
|
2. COUNT: Counts the number of
cells that contain numbers and counts numbers within the list of arguments. Use
COUNT to get the number of entries in a number field that is in a range or
array of numbers.
Syntax:
COUNT(value1,value2,...)
Example: =COUNT(A2:A8) [from table 2]
|
Ans: 3
3. COUNTA: Counts the number of
cells that are not empty and the values within the list of arguments. Use
COUNTA to count the number of cells that contain data in a range or array.
Syntax:
COUNTA(value1,value2,...)
Example: =COUNTA(A2:A8)
[from table 2] Ans: 6
4. COUNTBLANK: Counts empty cells in a
specified range of cells.
Syntax:
COUNTBLANK(range)
Example: =COUNTBLANK(A2:A8) [from
table 2] Ans: 1
5. COUNTIF: Counts the number of
cells within a range that meet the given criteria.
Syntax:
COUNTIF(range,criteria)
Example:
|
|
A
|
B
|
|
1
|
Data
|
Data
|
|
2
|
Apples
|
32
|
|
3
|
Oranges
|
54
|
|
4
|
Peaches
|
75
|
|
5
|
Apples
|
86
|
|
[from table 3]
=COUNTIF(A2:A5,"apples")
Ans: 2
=COUNTIF(A2:A5,A4)
Ans: 1
=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)
Ans: 3
|
|
A
|
|
1
|
Data
|
|
2
|
20
|
|
3
|
 50 |
|
4
|
40
|
|
5
|
30
|
|
6
|
10
|
|
7
|
30
|
6. MAX: Returns the largest value
in a set of values.
Syntax:
MAX(number1,number2,...)
Example: =MAX(A2:A7) [from table 4] Ans:
50
7. MIN: Returns the smallest number in a set of values.
Syntax:
MIN(number1,number2,...)
Example: =MIN(A2:A7) [from table 4] Ans: 20
8. RANK: Returns the rank of a number in a list of numbers. The rank of a
number is its size relative to other values in a list. (If you were to sort the
list, the rank of the number would be its position.)
|
|
A
|
||
|
1
|
Data
|
||
|
2
|
7
|
||
|
3
|
3.5
|
||
|
4
|
3.5
|
||
|
5
|
1
|
||
6
|
2
|
Syntax:
RANK(number,ref,order)
Example:
=RANK(G8,G3:G11,1)
[from table 5]
=RANK(A3,A2:A6,1)
Rank of 3.5 in the list above Ans: 3
=RANK(A2,A2:A6,1)
Rank of 7 in the list above Ans: 5
v Date
& Time Functions
1. DATE: Returns the sequential
serial number that represents a particular date.
Syntax:
DATE(year,month,day )
Example: =DATE
(1990,09,06) Ans: 9/6/1990
2. DATE VALUE: Returns the serial number
of the date represented by date_text. Use DATEVALUE to convert a date
represented by text to a serial number.
Syntax:
DATEVALUE(date_text)
Example:
=DATEVALUE("09/06/1990") Ans: 33122
3. DAY: Returns the day of a
date, represented by a serial number. The day is given as an integer ranging
from 1 to 31.
Syntax:
DAY(serial_number)
Example: =DAY(“6-September-1990”) Ans: 6
4. HOUR: Returns the hour of a
time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23
(11:00 P.M.).
Syntax:
HOUR(serial_number)
Example: =HOUR(“2:30:25”) Ans: 2
5. MINUTE: Returns the minutes of a
time value. The minute is given as an integer, ranging from 0 to 59.
Syntax: MINUTE
(serial_number)
Example: =MINUTE
(“2:30:25”) Ans: 30
6. MONTH: Returns the month of a
date represented by a serial number. The month is given as an integer, ranging
from 1 (January) to 12 (December).
Syntax:
MONTH(serial_number)
Example: =MONTH(“6-September-1990”) Ans: 9
7. SECOND: Returns the seconds of a
time value. The second is given as an integer in the range 0 (zero) to 59.
Syntax:
SECOND(serial_number)
Example:
=SECOND(“2:30:25”) Ans: 25
8. NOW: Returns the serial number
of the current date and time.
Syntax: NOW( )
Example: =NOW( ) Ans: 9/6/1990 14:32
9. TIME: Returns the decimal
number for a particular time.
Syntax:
TIME(hour,minute,second )
Example: =TIME
(2,30,16) Ans: 2:30 AM
10. TODAY: Returns the serial number
of the current date. The serial number is the date-time code used by Microsoft
Excel for date and time calculations. If the cell format was General before the
function was entered, the result is formatted as a date.
Syntax: TODAY( )
Example: TODAY( ) Ans: 09/06/1990
11. WEEKDAY: Returns the day of the
week corresponding to a date. The day is given as an integer, ranging from 1
(Sunday) to 7 (Saturday), by default.
Syntax:
WEEKDAY(serial_number,return_type)
Example: =WEEKDAY("9/6/1990",2) Ans: 4
12. YEAR: Returns the year corresponding to a date. The year is returned as
an integer in the range 1900-9999.
Syntax: YEAR(serial_number)
Example: =YEAR(“9/6/1990”) Ans: 1990
v Text
Functions
1. CHAR: Returns the character
specified by a number. Use CHAR to translate code page numbers you might get
from files on other types of computers into characters.
Syntax: CHAR(number)
Example: =CHAR(65) Ans: A
=CHAR(90) Ans: Z
2. CODE: Returns a numeric code
for the first character in a text string. The returned code corresponds to the
character set used by your computer
Syntax: CODE(text)
Example: =CODE(A) Ans: 65
=CODE(Z)
Ans: 90
3. CONCATENATE: Joins two or more text
strings into one text string.
Syntax: CONCATENATE
(text1, text2...)
Example: =CONCATENATE (“WEL”,”COME”)
Ans: WELCOME
4. DOLLAR: The function described in
this Help topic converts a number to text format and applies a currency symbol.
The name of the function (and the symbol that it applies) depends upon your
language settings.
This function
converts a number to text using currency format, with the decimals rounded to
the specified place. The format used is ($#,##0.00_);($#,##0.00).
Syntax: DOLLAR
(number, decimals)
Example:
=DOLLAR(1234.567,2) Ans: $1234.57
5. EXACT: Compares two text strings
and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is
case-sensitive but ignores
formatting
differences. Use EXACT to
test text being entered into a document.
Syntax: EXACT (text1,
text2, ……..)
Example: =EXACT(“a”,”A”) Ans: False
=EXACT(“A”,”A”) Ans: True
6. FIND: FIND locate one text
string within a second text string, and return the number of the starting
position of the first text string from the first character of the second text
string.
Syntax: FIND
(find_text, within_text,start_num)
Example: =FIND("i","India",1) Ans: 4
7. LEFT: LEFT returns the first
character or characters in a text string, based on the number of characters you
specify.
Syntax: LEFT (text,
num_chars)
Example: =LEFT (“RAMAYAN”,3) Ans:
RAM
8. LEN: LEN returns the number of
characters in a text string.
Syntax: LEN(text)
Text is the text whose length you want to find.
Spaces count as characters.
Example:
=LEN("Computer") Ans: 8
9. LOWER: Converts all uppercase
letters in a text string to lowercase.
Syntax: LOWER(text)
Example:
=LOWER(“COMPUTER”) Ans: computer
10. MID: MID returns a specific
number of characters from a text string, starting at the position you specify,
based on the number of characters you specify.
Syntax:
MID(text,start_num,num_chars)
Example: =MID
(“RAMAYAN”,3,4) Ans: MAYA
11. PROPER: Capitalizes the first
letter in a text string and any other letters in text that follow any character
other than a letter. Converts all other letters to lowercase letters.
Syntax: PROPER(text)
Example: =PROPER(“COMPUTER”) Ans: Computer
12. REPLACE: REPLACE replaces part of
a text string, based on the number of characters you specify, with a different
text string.
Syntax:
REPLACE(old_text,start_num,num_chars,new_text)
Example: =REPLACE
(“Input”,1,2,“Out”) Ans: Output
13. REPT: Repeats text a given
number of times. Use REPT to fill a cell with a number of instances of a text
string.
Syntax: REPT(text,number_time)
Example: =REPT (“*”,10) Ans: **********
14. RIGHT: RIGHT returns the last
character or characters in a text string, based on the number of characters you
specify.
Syntax: RIGHT
(text,num_chars)
Example: =RIGHT (“RAMAYAN”,3) Ans:
YAN
15. TRIM: Removes all spaces from
text except for single spaces between words. Use TRIM on text that you have
received from another application that may have irregular spacing.
Syntax: TRIM(text)
Example: =TRIM(“I LOVE MY
INDIA”) Ans: I LOVE MY INDIA
16. UPPER: Converts all uppercase
letters in a text string to lowercase.
Syntax: UPPER(text)
Example:
=UPPER(“Computer”) Ans:COMPUTER
Ms Excel – 2003 Keybaord Shortcut Key
F2
: Edit the selected cell
F5
: Go to a specific cell
F7
: Spell check selected text and/or
document
F11
: Create chart
Ctrl
+ Shift + ; : Enter the current time
Ctrl
+ ; : Enter the current date
Alt
+ Shift + F1: Insert new
worksheet
Shift
+ F3 : Open the Excel® formula window
Shift
+ F5 : Bring up search box
Ctrl
+ A : Select all contents of worksheet
Ctrl
+ B : Bold highlighted selection
Ctrl
+ I : Italicize highlighted selection
Ctrl
+ C : Copy selected text
Ctrl
+ V : Paste
Ctrl
+ D : Fill
Ctrl
+ K : Insert link
Ctrl
+ F : Open find and replace options
Ctrl
+ G : Open go-to options
Ctrl
+ H : Open find and replace options
Ctrl
+ U : Underline highlighted selection
Ctrl
+ Y : Redo
Ctrl
+ 5 : Strikethrough highlighted selection
Ctrl
+ O : Open options
Ctrl
+ N : Open new document
Ctrl
+ P : Open print dialog box
Ctrl
+ S : Save
Ctrl
+ Z : Undo last action
Ctrl
+ F9 : Minimize current window
Ctrl
+ F10 : Maximize currently selected window
Ctrl
+ F6 : Switch between open workbooks/windows
Ctrl
+ Page up:
&
Page Down : Move between Excel® worksheets in the same document
Ctrl
+ Tab : Move between two or more open Excel® files
Alt
+ = : Create formula to sum all of above
cells
Ctrl
+ ‘ : Insert value of above cell into current
cell
Ctrl
+ Shift + ! : Format number in comma format
Ctrl
+ Shift + $ : Format number in
currency format
Ctrl
+ Shift + # : Format number in
date format
Ctrl
+ Shift + % : Format number in
percentage format
Ctrl
+ Shift + ^ : Format number in
scientific format
Ctrl
+ Shift + @: Format number in
time format
Ctrl
+ g : Move to next section of text
Ctrl
+ Space : Select entire column
Shift
+ Space : Select
entire row
No comments:
Post a Comment