Formula esa vf/kdre 1024 Characters 'kkfey fd;s tk ldrs gSaA

Text Function:-
=(A 1& B 2) ;gka ;fn A1 rFkk B2 cell esa dksbZ Text fy[kk gS vkSj gesa
mu nksuksa Text dks rhljs cell esa tksM+ dj j[kuk gS rks ge rhljs lsy esa
mijksDr Formula dks fy[ksxsaA rks ;g dqN bl izdkj ls fn[kkbZ nsxk%&

Note:- ;fn ge =(A 1& B 2) Formula ls Text dks tksM+rs gSa rks nksuksa
lsyksa ds Text ds tqM+us ij nksuks Text ds e/; (blank space) [kkyh LFkku
ugh jgrk gSA

=(A1&" "&B1) ;gka ;fn A1 rFkk B2 cell esa dksbZ Text fy[kk gS vkSj gesa
mu nksuksa Text dks rhljs cell esa tksM+ dj j[kuk gS rks ge rhljs lsy esa
mijksDr Formula dks fy[ksxsaA rks ;g dqN bl izdkj ls fn[kkbZ nsxk%&

Note:- ;fn ge =(A1&" "&B1) Formula ls Text dks tksM+rs gSa rks nksuksa
lsyksa ds Text ds tqM+us ij nksuks Text ds e/; ( Single blank space) [kkyh
LFkku jgrk gSA ;gka ij /;ku j[kus dh vko’;drk fd tc Formula dks fy[ksa rks
fn;s x;s Quotes (“ ”) ds e/; ,d [kkyh LFkku nssaA uhps fn;s x;s Formula dk
iz;ksx Hkh ge Bhd ,sls gh djrs gSa%& =PROPER(A2&" "&B2)

3 [Link] ckrsa tks vkidks dates and times ds lkFk dke djrs gq;s ;kn
j[kuh gS oks gS Format, Format and Format.
Next date dks ykus ds fy,%&
=A1+1
 A1 cell esa date dks j[ksaA
 blds ckn ftl lsy esa cell A1 esa j[kh rkjh[k dh
vxyh rkjh[k ykuh gks rks ml lsy esa =A1+1 dks fy[ksaA
 blds ckn Enter Key dks press djsaA
 tgka ij vkius ;g QkWewyk yxk;k gS ogka ij cell A1 esa j[kh rkjh[k ls
;gka ij ,d fnu vxyh rkjh[k crkrk gSA
 ;fn gesa cell A1 esa j[kh rkjh[k ls nks fnu dh vxyh rkjh[k ykuh gks
rks blds fy, =A1+2 dks fy[ksaA
 ;gka ij cell A1 ds ckn + number esa tks Hkh vad fy[kxsaA cell A1 ls
mrus gh fnu vkxs dh rkjh[k dks crk;sxkA
 ;fn gesa cell A1 esa j[kh rkjh[k ls one week
vkxs dh rkjh[k pkfg, rks blds fy, =A1+7 dks
fy[ksaA
 ,slk djrs gh ;g cell A1 esa j[kh rkjh[k ls one
week vkxs dh rkjh[k dks fn[kk;sxkA

A
Note:- ;g formula dsoy date ds lkFk dke djsxk bl formula dk mi;ksx ge time
function ds lkFk ugha dj ldrs gSaA ;g function vki Insert Menu ds
Function option eas ugha ik ldrs gSaA

=TODAY()
 ;g QkWewyk fy[kdj Enter Key dks press djus ij
orZeku rkjh[k vk tkrh gSA
=NOW()
 ;g QkWewyk fy[kdj Enter Key dks press djus ij
orZeku rkjh[k ds lkFk orZeku le; Hkh vk tkrk gSA

Note:- ;g QkWewyk current date dks Lor% update djrk gSA tc gekjs
computer dh date cnysxh olS gh tgka ij bl QkWewyk ds lkFk MsV yk;k x;k
gS og Hkh Lor% gh cny tk;sxkA Bhd ;gh ckr Time ds lkFk ykxw gksrh
gSA

fdlh nwljh 'khV dk eSVj nwljh 'khV esa fjQj djus ds fy,
%&

1. tSls vki Sheet 1 esa Sheet 2 dk MkVk pkgrs

gSa vkSj vki pkgrs gSa fd tSls gh Sheet 2
MkVk j[kk tk; oSls gh Sheet 1 esa Hkh
MkVk vk tk;sA rks blds fy, djsa%&
2. loZizFke ftl 'khV esa fdlh nwljh 'khV dk
eSVj j[kuk gks tgka ls vki vkjaHk djuk
pkgrs gSa vFkkZr Sheet 1esa = dk fpUg
yxkdj ftl 'khV dk eSVj pkfg, ml Single
click djsaA
3. blds ckn tgka rd ds jst dk MkVk Sheet
1esa pkgrs gSa ogka rd Drag djsaA
4. blds ckn Enter key dks press djsaA
5. blds ckn Sheet 1esa tgka ij = dk ekdZ
yxk;k Fkk ogka ij formula = Sheet2!A1:A9 vk tkrk gSA
6. vc blds ckn blds ckn Sheet 1esa tgka ij = dk ekdZ yxk;k
Fkk ml lsy dks lfdz; dj jsat cuk,a ]
tgka rd vki Sheet 2 dk eSVj Sheet 1 esa
j[kuk pkgrs gSA
7. blds ckn Enter key dks press djsaA
8. vc Sheet 2 esa fn;s x;s jsat esa tks Hkh
Item j[ksxsa og Lor% gh Sheet 1 esa
fn;s x;s jsat esa vk tk;sxkA

B
Multiply djrs le; fuEu ckrksa dk /;ku j[ksa%&

BODMAS ds fu;e dk ikyu djsaA

B Bracket
O Of
D Devide
M Multiply
A Add
S Substract

1. tSlk fd ;gak fp= esa fn;s x;s MkVk esa

vki ns[k ldrs gSa fd ,d gh bracket ds
vanj j[ks MkVk dks multiply djrk gS blds
ckn Add djrk gSA
2. =(1+2*3) ;gka ij BODMAS fu;e ds vk/kkj
ij igys multiply djrk gS blds ckn Add djrk
gS vkSj Result esa ges 7 fn[kkrk gSA
3. ;gh vxj ge =(1+2)*3 djrs gSa rks ;g igys
bracket esa j[ks MkVk dks Add djrk gS
fQj blds ckn 3 ls multiply djrk gS vkSj Result esa ges 9
fn[kkrk gSA

Mathmetical Function

SUM
=SUM(A1,C2,C3) or =SUM(A1+C2+C3)

 Sum Function dk mi;ksx djds ge fdlh Hkh lsy esa j[ks MkVk
dks tksM+rs gSaA
 bls fuEu izdk ls dj ldrs gSa%&
1. ;gka ij = Sum fy[kus ds ckn ftl lsy dk
MkVk gesa tksM+uk gks ml lsy dk irk

C
 (address) nsrs gSA ;gka ij nks lsy ds address chp esa dkWek
dk iz;ksx djrs gSaA
2. = Sum fy[kus ds ckn ftl lsy dk MkVk gesa tksM+uk gks ml
lsy dk irk (address) nsrs gSA ;gka ij nks lsy ds address chp
esa + dk iz;ksx djds Hkh ge tksM+ ldrs gSaA
3. ftu lsyksa dk tksM+uk gS ;fn os ,d lhfj;y esa gSa rks ge
jsat dk iz;ksx djrs gSaA ;gka ij igys lsy dk irk blds ckn :
fpUg dk iz;ksx dj vafre lsy dk irk nsrs gSaA ;fn gesa ,d ls
vf/kd jst nsus gSa rks nks jsat ds chp dkWek dk iz;ksx
djrs gSaA

SUMIF
 Sum Function dk mi;ksx djds ge ,d jsat esa j[ks MkVk
dks ,d condition yxkdj tksM+rs
gSaA
 ;gka ij Cell A1 : A6 rd jsat esa
ftrus Hkh 5 feysa mu lcdk ;ksx
ftl lsy esa QkWewyk yxk gqvk
gS esa fn[kkrk gSA
 fn;s gq;s jsat esa ftl la[;k
dk ;ksx djuk gksxk mls jsat nsus ds ckn fy[ksaA

Note:- ;gka ij ns[k ldrs gSa fd fn;s x;s jsat

esa ftruh ckj 5 vk;k dsoy mUgh la[;k dks
tksM+dj fn[kk jgk gSA
 ;fn ge sumif ds lkFk fdlh sign dk iz;ksx
djrs gSa rks bls double quotes ds Hkhrj
j[ksaA
 tSlk fd ;gka ij sumif ds lkFk tks daMh’ku yxkbZ gS og ;g
gS fd ;fn A1 ls A7 rd dh jsat esa ftrus Hkh numbers 1 ls
vf/kd gks dsoy mUgh dks tksMs+A

SQRT

 bldk iz;ksx fdlh la[;k dk Squre fudkyus ds fy,

fd;k tkrk gSA
 tSlk fd fp= esa cell A1 esa j[ks MkVk 64 dk
Squre cell B3 esa 8 fn[kk jgk gSA

D
POWER
You can raise a number to a power (multiplying it by itself a certain number of
times with this function) Hence:
=POWER(4,2)
=POWER(A1,2)
 blesa bracket esa igys dksbZ la[;k
fy[ksaA blds ckn dkWek yxkdj nwljh
la[;k fy[ksaA
 ;gka ij tks nwljh la[;k gksxh ;g igyh la[;k dks mruh ckj
[Link] djsxhA
 tSlk fd ;gka ij fp= ls Li"V gS fd power(4,2) esa 4 X4 fn[kkdj
mls result ds :i esa 16 fn[kkrk gSA
 ;gka ij lsy dk irk nsdj Hkh bl QkWewyk dk iz;ksx dj ldrs
gSaaA

Note:- ;gka ij vki POWER function esa second argument esa Trick dk
bLrseky dj Squre root rFkk Cubic root fudky ldrs gSaA

=POWER(16,1/2) Squre Root fudkyus ds fy,

 ;gka ij cell A esa 16 la[;k gS vkSj mldk

Squre root fudkyus ds fy, formula esa 16
la[;k ds ckn dkWek yxkdj 1/2 fy[ksaA
 ;gka ij fp= esa 16 dk underoot 4 fn[kk jgk gSA D;ksafd 4X4=
16 gksrk gSA vr% 16 dk underoot 4 gksxkA

=POWER (A1,1/3) Cubic Root fudkyus ds fy,

 ;gka ij cell A esa 27 la[;k gS vkSj

mldk Cubic root fudkyus ds fy,
formula esa 27 la[;k ds ckn dkWek
yxkdj 1/3 fy[ksaA
 ;gka ij fp= esa 27 dk cubic root 3 fn[kk jgk gSA D;ksafd
3X3X3 = 27 gksrk gSA vr% 27 dk cubic 3 gksxkA

Max

E
  cell A1: A10 rd dh jsat esa tks Hkh MkVk
jgsxk mlesa lcls vf/kd value ftl lsy essa
jgsxh mlh lsy dk vad tgka ij ;g formula
jgsxk ogka ij fn[k;h nsxkA
 tSlk fd ;gka ij fp= ls Li"V fn[k jgk gSA

ROUND
bldk iz;ksx n’keyo ds ckn dh la[;k dks rounding djus esa fd;k
tkrk gSA
 n’keyo ds ckn dh la[;k gVk dj blls igys
okyh la[;k esa ;fn n’kyo ds ckn dh
Remove gksus okyh la[;k 5 or 5 ls vf/kd gS
rks blls igys vkus okyh la[;k esa 1 tqM+ tkrk gSA
 n’kyo ds ckn ftruh la[;k j[kuh gks mls = round( cell address,
No.) nsrs gSaA
 ;gka ij ns[k ldrs gSa fd 4.258 dks n’kyo ds ckn nks vad rd
round gksus ij ;g 4.26 gks tkrk gSA

ABS
 ;g Function dsoy Negative numbers dks
positive esa cny nsrk gSA

MOD

 ;g Function dsoy fdlh la[;k esa Hkkx nsus ij

tks 'ks"kQy cprk gS mldks crkrk gSA
 tSlk fd Cell A1 (50) dks Cell B1 (3) ls Hkkx nsus
ij 'ks"kQy 2 cprk gS] dks Cell C1 esa fn[kk;k x;k gSA

Note:- 50 esa 3 dk Hkkx nsus ij 'ks"kQy 2 cpsxkA

Divide

=(A4/B4)
 lsy A4 esa HkkT; la[;k dks
fy[ksaA vFkkZr ftl la[;k dks
Hkkx nsuk gS mldk irk fy[ksaA

F
  lsy B4 esa HkkT; la[;k dks fy[ksaA vFkkZr ftl la[;k ls Hkkx
nsuk gS mldk irk fy[ksaA
 tSlk fd ;gka ij fn;s x;s fp= ls Li"V gks jgk gSA
 ;gka ij la[;k 100 dks la[;k 2 ls Hkkx nsus ij 50 vk jgk gSA
 ;fn A4 rFkk B4 esa dksbZ vad ugha gksrk gS ;k mlesa 0
gksrk gS rks ftl lsy esa ;g QkWewyk yxk gS ml lsy esa
#DIV/0 fn[kkbZ nsrk gSA
 ;fn vki pkgrs gSa fd A4 rFkk B4 esa 0 ;k cell blank gksus ij
#DIV/0 u fn[kkbZ nsA cfYd ftl lsy esa QkWewyk yxk gS ml
lsy esa dqN u fn[kkbZ nsA rks blds fy, djs%&
 =IF(ISERROR(B1/A1),"",B1/A1) yxk;sA vkSj ;fn pkgrs gSa fd A4
rFkk B4 esa dksbZ oSY;q u jgus ij 0 fn[kk;s rks blds fy,
=IF(ISERROR(B1/A1),0,B1/A1) fy[ksA

IF, ISNUM, LEFT and MID

MID
=MID(A3,3,3)

 ;gka QkWewyk MID(A3,3,3) esa cell A3 ds

ckn tks vad 3 vk;k gS mldk vFkZ lsy A3
esa j[ks vadksa esa ls ck;h vksj ls rhljs
vad ls ftl lsy esa QkWWewyk gS esa
fy[kuk 'kq: djsA blds ckn var esa tks vad
3 vk;k gS mldk vFkZ tgka ls vad dks
fy[kuk 'kq: fd;k gS ogka ls dsoy rhu vad
¼ck;ah vksj ls nk;h vksj½ rd gh ftl lssy
esa QkWewyk gS mlesa fy[ksA
 tSlk fd fp= ls Li"V gSA

=LEFT(A1,2)
 ;gka QkWewyk LEFT(A1,2) esa cell A1
ds ckn tks 2 Diigit vk;k gS mldk vFkZ
lsy A1 esa j[ks vadksa ;k 'kCnksa

G
 esa ls ck;h vksj ls dsoy nks fMthV dks gh ftl lssy esa
QkWewyk gS mlesa fy[ksA

=Right(A1,2)

 ;gka QkWewyk RIGHT(A1,2) esa cell A1

ds ckn tks 2 digit vk;k gS mldk vFkZ
lsy A1 esa j[ks vadksa ;k 'kCnksa esa ls nk;ha vksj ls
dsoy nks fMthV dks gh ftl lssy esa QkWewyk gS mlesa
fy[ksA
=TEXT(B1,"###") bl QkWewys dk iz;ksx fdlh
number format data dks as a text format
esa j[kus ds fy, fd;k tkrk gSA
 ;gka ij column C esa ;g QkWewyk
yxk;k gSA fdlh ,d lsy es QkWewys dks
yxkus ds ckn Drag dj mls nwljs lsy esa Hkh fQy djsaA
 ;gka ij vki ns[k ldrs gSa fd column B esa fy[kh la[;k
number format eas gsS D;ksfd number lsy esa nkfguh
rjQ gSA tcfd column c bls text ds :i esa convert dj
numbers dks cka;h rjQ j[k nsrk gSA
 ;fn fdlh la[;k ds lkFk fdlh text dks j[k nsrs gSa rks ;g
number rFkk text nksuks dks gh text format ij ykxw djds
fn[kkrk gS vFkkZr ;g general format ij ;g Lor% left side
pyk tk;sxkA
 ;gka ij ;fn gesa nksuksa mix (number & text ) dks
Ascending or descending esa Nk¡Vuk gS rks mls iwjk
lysDV dj Ascending or descending Tab ij click djsaA

=DATEDIF (Date1,Date2,Interval)

tgka ij gS%&

Date1 igyh Date gS tks Standard serial date format esa gSA
Date2 nwljh Date gS tks Standard serial date format esa gSA
Interval fn[kkrk gS Unit of Time. vkSj ;gka ij fy[kk x;k tks =dateif fjtYV essa
fn[kk;h iM+rk gSA

H
Date1 dks igys lsy esa gksuk pkfg, rFkk lkFk gh Date2 ls less ¼de½ ;k cjkcj
gksuk pkfg,A ugha rks =dateif bl izdkj fn[kk;h nsxk& #NUM! vkSj ;fn Date1
;k Date2 Date Format esa ugha gS rks =dateif bl izdkj fn[kk;h nsxk& #VALUE!

Intervals fuEufyf[kr dksM (code) ds gks ldrs gSa%&

Code Meaning dk fooj.k%&

“m” dk vFkZ month gksrk gS ;g date1 rFkk date2 ds e/; ftrus Hkh [Link] month
cuxsa mlh dks fn[kk;sxkA
“d” dk vFkZ days gksrk gS ;g date1 rFkk date2 ds e/; ftrus Hkh [Link] days
cuxsa mlh dks fn[kk;sxkA
“y” dk vFkZ year gksrk gS ;g date1 rFkk date2 ds e/; ftrus Hkh [Link] years
cuxsa mlh dks fn[kk;sxkA
;fn date1 rFkk date2 ds ,d gh year esa gSa rks %&
“yd” days years dks ckgj dj nsrk gSA vkSj ;g fnuksa dh la[;k dks fn[kkrk
gSA ;fn date1 rFkk date2 ds ,d gh year esa gSa rHkh ,slk gksxkA
“md” days month dks ckgj dj nsrk gSA vkSj ;g fnuksa dh la[;k dks fn[kkrk
gSA ;fn date1 rFkk date2 ds ,d gh month rFkk ,d gh year esa gSa rHkh ,slk
gksxkA

DATEDIF And Leap Years (DATEDIF vkSj yhi bZ;j)

Excel Leap Year esa Months dks Lor% gh 29 days dk fn[kkrk gSA
For Example :- ;gka ij tks Formula yxk gS og dqN bl izdkj gS%&
=dateif(a1,b1 “d”)
;gka ij a1 cell esa igyh date ( 1-Feb-1996) gS rFkk nwljh lsy esa nwljh date (1-
march-1996) gS ;g nks frfFk;ksa ds chp ds fnuksa dh laa[;k fn[kk jgk
gSA ;gka ij 1996 dh Feb yhi bZ;j gS vr% blesa February ekg esa 29 fnu
fn[kk jgk gSA tks o"kZ yhi ugha gksxk mlesa February ekg esa 28 fnu
gksxsaA Formula dk iz;ksx nks frfFk;ksa ds e/; fnuksa dh la[;k Kkr djus ds
fy, fd;k tkrk gSA

,d gh o"kZ dh nks frfFk;ksa ds e/; fnuksa dh

[Link]%&
For Example :- ;gka ij tks Formula yxk gS og dqN bl izdkj gS%&
=datedif(date1,date2 “d”)
;gka ij date1 cell esa igyh date ( 1-Jan-1996) gS rFkk nwljh lsy esa nwljh date
(1-Feb-1996) gS ;g nks frfFk;ksa ds chp ds fnuksa dh laa[;k 31 days fn[kk
jgk gSA

I
,d o"kZ dh nks frfFk;ksa ds e/; eghuks dh [Link]
%&
=datedif(date1,date2 “m”)
;gka ij date1 cell esa igyh date ( 5-Apr-05) gS rFkk nwljh lsy esa nwljh date (5-
Oct-05) gS ;g nks frfFk;ksa ds chp ds fnuksa dh laa[;k 6 months fn[kk jgk
gSA

Note:-blh izdkj ge o"kZ bR;kfn dh [Link] dj ldrs gSaA

fdlh O;fDr dh vk;q dh [Link] djuk%&

blds fy, loZizFke ge ,d lsy esa O;fDr dh tUefrfFk (Date of birth ) dks date
format esa fy[ksxsaA rFkk nwljs lsy esa ftl rkjh[k rd ml O;fDr dh vk;q dh
[Link] djuh gS ml rkjh[k dks ml lsy esa j[ksxsaA ;gka ij eku yhft, dh fdlh
vk;q 03/¼bldks j[kk gS A7 esa½ gS vkSj gesa 02/01/07¼bldks j[kk gS B7
esa½ rd mldh vk;q dh [Link] djuh gS rks blds fy, ge fuEu izdkj ds
QkewZys dk iz;ksx djsxsa%&

=datedif (A7,B7,”Y”)&”years,”&datedif(A7,B7,”ym”)& “months” &

datedif(A7,B7,”md”)&”days”

rks ;g vk;q dh [Link] bl fn[kk;sxk%&

Conditional Formatting

tc Formula dks develop fd;k tkrk gS vFkkZr QkWewyk dks fy[kk tkrk
gS rks ;g case sensitive ugha gksrk gSA
,d gh Formula esa 7 ls vf/kd If dk iz;ksx ugha fd;k tk ldrk gSA
;fn ge fdlh Text dks Condition yxkus ds rqjar ckn j[krs gSa rks Text
dks double quotes (“”) esa j[krs gSaA

J
If
;g Formula dqN bl izdkj fn[kk;h iM+rk gS%&
=IF(condition,value if condition is True,value if condition is False).

=IF(A1=1,9,8) ;gka ;fn A1 cell esa la[;k 1 gksrh gS rks ftl lsy esa ;g
formula yxk;k tkrk gS ml lsy esa 9 dks fn[kk;sxk vkSj ;fn 1 'ks"k dksbZ
la[;k gksrh gS rks ogka ij 8 fn[kk;h iMs+xkA D;ksafd ;gka ij conditon
yxk;h xbZ gS fd A1 cell esa 1 ds cjkcj dksbzZ la[;k gksrh gS rks la[;k 9
dks fn[kk;sxkA ;fn ;gka ij A1>=1 fy[k nsrs gSa rks ;g A1 cell esa 1 ds
cjkcj ;k blls vf/kd dksbZ la[;k gksus ij la[;k 9 dks fn[kk;sxkA rFkk 1 ls
de gksus dh n’kk esa 8 dks fn[kk;sxkA
=IF(A1<>1,9,8) ;gka ;fn A1 cell esa la[;k 1 ugha gksrh gS gS rks ftl
lsy esa ;g formula yxk;k tkrk gS ml lsy esa 8 dks fn[kk;sxk vkSj ;fn
dksbZ nwljh la[;k gksrh gS rks ogka ij 9 fn[kk;h iMs+xkA
D;ksafd ;gka ij condition yxk;h xbZ gS fd A1 cell esa 1 ds cjkcj dksbzZ
la[;k u gks rks la[;k 9 dks fn[kk;h iM+sA ;fn ;gka ij A1>=1 fy[k nsrs
gSa rks ;g A1 cell esa 1 ds cjkcj ;k blls vf/kd dksbZ la[;k gksus ij la[;k 9
dks fn[kk;sxkA rFkk 1 ls de gksus dh n’kk
=IF(A1>=90,"A",IF(A1>=80,"B","C") A1 cell esa 90 ds cjkcj ;k blls
vf/kd dksbZ la[;k gksus ij la[;k A dks fn[kk;sxkA rFkk 80 ;k blls vf/kd
gksus dh n’kk esa B dks fn[kk;sxkA rFkk 80 ls de gksus dh n’kk esa c
dks fn[kk;sxkA
=IF(A1="ten",TRUE,FALSE) A1 cell esa text (ten) feyrk gS rks ;g true
fn[kk;sxk vkSj vxj A1 cell esa dksbZ nwljk 'kCn feyrk gS rks ;g False
fn[kk;sxkA
=IF(A1="Microsys",9,8) ;fn A1 cell esa microsys fy[kk gqvk gksxk rks
ftl lsy esa ;g formula yxk gqvk gS ml lsy esa laa[;k 9 dks fn[kk;sxk
vkSj ;fn ml lsy esa dksbZ nwljk word fy[kk gS rks laa[;k 8 dks
fn[kk;sxkA ;gka ij ge laa[;k dh txg fdlh text dks Hkh j[k ldrs gSa text
dks fy[krs le; ;g /;ku j[kuk pkfg, fd og text Quotes (“”)ds vanj gh jgsA
=IF(A1=1,"Microsys",8) ;fn A1 cell esa la[;k 1 fy[kk gqvk gksxk rks
ftl lsy esa ;g formula yxk gqvk gS ml lsy esa microsys fy[kk gqvk vk
tk;sxk vkSj ;fn A1 cell esa la[;k 1 ds vykok dksbZ nwljh la[;k gqbZ rks
ml lsy esa la[;k 8 dks fn[kk;sxkA
=IF(A1=1," ",8) ;fn A1 cell esa la[;k 1 fy[kk gqvk gksxk rks ftl lsy esa
;g formula yxk gqvk gS ml lsy esa Blank space fy[kk gqvk vk tk;sxk vkSj
;fn A1 cell esa la[;k 1 ds vykok dksbZ nwljh la[;k gqbZ rks ml lsy esa
la[;k 8 dks fn[kk;sxkA
=IF(A1=" ","Please enter something","Thanks") ;fn A1 cell [kkyh
(Blank) jgrk gS rks ;g ftl lsy esa ;g QkWewZyk yxk gS ml lsy esa
Please enter something fn[kk;sxk vkSj vxj ;fn A1 cell esa dksbZ eSVj
fy[k tkrk gS rks blesa Thanks fn[kkrk gSA ;gka ij ge bu 'kCnksa ds
vykok dksbZ nwljk Hkh 'kCn fy[k ldrs gSA
=IF(A1=100,A1+20,IF(A1>100,A1+30,"Less than 100")) ;fn A1 cell esa
100 jgrk gS rks ;g ftl lsy esa ;g QkWewZyk yxk gS ml lsy esa 100(A1)+20

K
fn[kk;sxk 120 vkSj ;fn 100 ls vf/kd jgrk gS rks tSlk fd QkWewys esa fy[kk
gS 100 ls vf/kd gksus ij 100 +30 = 130 esa 1 tksM+dj fn[kk;sxkA vFkkZr
A1 cell rFkk tgka QkWewyk fy[kk x;k gS nksuks ds chp vUrj 30 dk
fn[kk;sxkA vkSj vxj ;fn 100 ls de gS rks Less than 100 fy[kk gqvk
fn[kk;sxkA

(Calculate the number of days in a month ) fdlh ekg esa fnuksa

dh la[;k dh [Link] djuk%&

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)
For Example:-
 lsy esa Date Format esa date dks fy[ksaA
 blds ckn tgka ij fnuksa dh la[;k fudkyuh gks ml lsy esa ;g Formula
fy[ksaA
 ;gka ij A1 ml lsy dk address gS ftl lsy esa date fy[kh xbZ gSA
 tc ge fdlh lsy esa date dks fy[krs gSa rks bl formula ds ek/;e ls ;g
Kkr ldrs gSa fd fy[kh gqbZ date ds month esa fnuksa dh la[;k fdruh
gksxhA
 Write Formula like this

 bldk Result bl izdkj display djsxk%&

Note:- ;gka ij ;g B1 esa December ekg esa fnuksa dh la[;k 31 fn[kk jgk gSA

Conditional Formatting

tc Formula dks develop fd;k tkrk gS vFkkZr QkWewyk dks fy[kk tkrk
gS rks ;g case sensitive ugha gksrk gSA
,d gh Formula esa 7 ls vf/kd If dk iz;ksx ugha fd;k tk ldrk gSA
;fn ge fdlh Text dks Condition yxkus ds rqjar ckn j[krs gSa rks Text
dks double quotes (“”) esa j[krs gSaA

MS-Excel dh Short keys

Keys Action
Ctrl +Z Undo command ds fy,
Ctrl + C Copy djus ds fy,A
Ctrl + V Copy fd;s eSVj dks dbZ ckj Paste djus ds
fy,A
Enter Paste djus ds fy,A
Ctrl + X Cut djus ds fy,A bldk iz;ksx ykds’ku cnyus
ds fy, fd;k tkrk gSA

L
Ctrl + F Find djus ds fy,A
Ctrl + H Replace djus ds fy,A
Ctrl + P Print djus ds fy,A
Ctrl + S Save djus ds fy,A
Ctrl + F4 Excel worksheet dks close djus ds fy,A
Alt + F4 Excel application dks close djus ds fy,A

Cell ls Cell esa Move djus ds fy,%&

To Press Action
Arrow keys ( &) blds ek/;e ls Active cell ds left, right, up &
down Direction esa tk (Move) ldrs gSaA
Ctrl + Arrow key blds ek/;e ls Ctrl + Arrow key dks ,d ckj press
djus ij data ds ,d region esa tk ldrs gSaA
Ctrl+A All cells dks lysDV djus ds fy,A
Ctrl + Home Active sheet ds igys lsy vFkkZr Cell A1 Active
gks tkrk gSA
Ctrl + End Range ds var esa ys tkrk gS tks jsat dh lcls
fupyh row gksrh gSA
Ctrl + Shift + End Active cell ls Range ds vfrae lsy rd lsysDV
djrk gSA
Ctrl + Shift + Home Active cell ls ysdj Range ds A1 cell rd lsysDV
djrk gSA
Ctrl + Page Down Next sheet (vxyh 'khV )ij tkus ds fy,A
Ctrl + Page UP Previous sheet ij (fiNyh 'khV ) tkus ds fy,A
Ctrl + TAB Next open work book eas tkus ds fy,A
Ctrl + N New work book ds fy,A
Shift + F11 New work sheet ds fy,A
=+Function Name+ Ctrl + A (Display Function) Function Name esa fy[ks x;s
Formula dks Paste Function esa Display djrk gS
ftls Enter key press djds Function dks Paste fd;k
tkrk gSA
Alt + F11 Active file Visual Basic Editor Mode esa pyh
tkrh gSA
Shift + Space Bar Select (active row ) lfdz; lsy dh row dks
lsysDV djrk gSA
Ctrl + Space Bar Select (active column ) lfdz; lsy dh column dks
lsysDV djrk gSA