Excel____________

Sql is imp.
Part1 -
Part2 -
😀
ALL TOPICKS

Topic based learning - topic ka nam pad ke que solve kar lo sare topic ko
aache se samjhao with ex.
●​ Ye sare topics google sheet pe bhi fully applicable he .
[Link] vs worksheet
[Link] , column , cell
[Link] box , formula bar
[Link] and tabs
[Link] the data in 4 directions , by controlling
[Link] - neeche ke cells me
7. flash fill - ctrl+e, (hint - data me ja ke .)
[Link] and center
[Link] text
[Link] darker and color
11.formula_building - 2 types
[Link] 1
[Link] 2
-​ [Link]()
-​ [Link]()
-​ [Link]()
-​ [Link]()
-​ [Link]()
-​ [Link]()
-​ [Link]() -
[Link] vs absolute reference - dono different he . f4, kya ye sirf formulas pe
work karta he ?
[Link]- normal condition , multiple if condition .
[Link]() -
-​ normal and
-​ Multiple and , and ke under 10 condition
-​ If + and se kuch badiya banao
[Link]
[Link]()
[Link]
20. Concat
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]-
[Link]
[Link]
[Link]
[Link](match())- ye dono mujhe yad nhi aaye .
-​ index()
-​ match()
[Link]
[Link]()
36. DAY(), MONTH(), YEAR()
[Link]()
[Link] and filter- process kar ke dikha .
[Link] filter- process kar ke dikha .
[Link] duplicates from excel - process kar ke dikha .
[Link] to column - process kar ke dikha .
[Link] validation - process kar ke dikha .
-​ Drop down - process kar ke dikha .
-​ Limitations - process kar ke dikha .
[Link] - - process kar ke dikha .
[Link] duplicates - process kar ke dikha .

[Link] table , charts - all process , task pure karo sare .

-​ 10 task .
-​ ✔ Pivot Table Basics
-​ ✔ Sorting & Filtering
-​ ✔ Matrix Reports
-​ ✔ Date Grouping
-​ ✔ Top N Analysis
-​ ✔ Segmentation
-​ ✔ Aggregations (Sum/Avg)
-​ ✔ Calculated Fields
-​ ✔ Running Total
-​ ✔ Percentage Analysis
-​ ✔ Pivot Charts
-​ ✔ Slicers (Interactive Reports)
-​
-​ 12 task .
-​ Data Modeling
-​ Pivot Table Analysis
-​ Advanced Pivot Calculations
-​ Drill Down Analysis
-​ Interactive Controls (Slicer/Timeline)
-​ Dashboard Building
-​
[Link] graphs
[Link] formatting
[Link] layout printing
49. Dashboard creation
50.2 projects
[Link] questions practice .

Google sheet - ye small task ke liye he , jese college ka koi work , multiple log live
editing kar sakte he , excel formula same he , row column same he , kabhi kabhi slow
kam karta he , fully web based he , heavy kam ke liye nhi he small task ke liye he
simple task ke liye he ye .

Excel - paied he , web base bhi chalta he but software based me bohot sare features
he , complex kam handel kar sakta he , multiple live editing , google me live editing
easy he multiple log aek time pe aek sheet pe kam kar sakte he aur sab ke kam bhi
dikhe ge but excel me multible log aek time me edit kar sakte he but thoda complex
process he google sheet jesa easy nhi he , excel share by onedrive share point .

Excel power full he bade kamo ke liye , google sheet chote kamo ke liye
book vs Worksheet (Sabse Basic)
●​ Workbook: Ye teri puri Excel file hai. Isko ek "Notebook" samajh le.
●​ Worksheet: Ye us notebook ke andar ke "Pages" hain. Niche dekhna, Sheet1,
Sheet2 likha hota hai. Tu + button daba ke jitne chahe pages (sheets) add kar
sakta hai.

[Link], Columns aur Cells (Ye hi main khel hai)

●​ Columns: Jo upar A, B, C, D... likhe hote hain (khadi lines), wo Columns hain.
●​ Rows: Jo side mein 1, 2, 3, 4... numbers likhe hote hain (leti hui lines), wo Rows
hain.
●​ Cell: Jahan ek Column aur Row milte hain, wahan ek dabba (box) banta hai.
Use Cell kehte hain.
○​ Example: Agar tu Column B aur Row 5 wale dabbe mein hai, toh us cell
ka naam B5 hoga. Isko "Cell Address" kehte hain.
[Link] Box aur Formula Bar (Top Area)
●​ Name Box: Upar left corner mein hota hai. Ye batata hai ki abhi tu kaunse cell
mein khada hai (Jaise wahan B5 likha hoga).

●​ Formula Bar: Name box ke just right side mein ek lamba sa box hota hai. Cell
mein jo bhi likhega, wo yahan dikhega. Future mein jab hum formulas
lagayenge, toh yahi sabse zyada kaam aayega.

[Link] & Tabs (Tools)

●​ Sabse upar Home, Insert, Page Layout likha hota hai. Inhe Tabs kehte hain.
●​ Har Tab ke andar dher saare options hote hain (jaise Bold karna, Color karna).
Is pure area ko Ribbon kehte hain.

5. Data Entry ke 2 Basic Rules:

●​ Enter Key: Niche wale cell mein jane ke liye.
●​ Tab Key: Right side wale cell mein jane ke liye.
●​ Pro Tip: Agar Shift ke saath Enter ya Tab dabaoge, toh ulta (upar ya left)
jaoge.
[Link] Magic: AutoFill (Drag Handle) Excel ka sabse bada jaadu.
Autofill and flash fill
●​ Scene: Tumhe 1 se 100 tak ginti likhni hai.
●​ Slow Tarika: 1, 2, 3... type karna.
●​ Hero Tarika (AutoFill):
○​ Cell mein 1 likho, niche 2 likho.
○​ Dono ko select karo.
○​ Selection ke bottom-right corner pe jao, wahan ek chhota sa black plus
(+) dikhega.
○​ Use pakad ke niche kheench do (Drag). Excel samajh jayega aur puri
ginti likh dega.
○​ Ye Months (Jan, Feb), Days (Mon, Tue) pe bhi kaam karta hai.
_________________________________________________________________________

[Link] fill ctrl+E

ctrl+E
Flash Fill (Ctrl + E): Ye tab use hota hai jab tumhe Text mein se kuch nikalna ya
jodna ho.
●​ Example: "Rahul Sharma" se "Rahul" nikalna. Ya "rahul" aur "sharma" ko
jodna.
●​ Tumhari mistake: Tum Monday ke bagal mein 12 aur Tuesday ke bagal mein 13
likh rahe ho. Excel soch raha hai "Monday ka 12 se kya rishta hai?". Kyunki
text mein koi connection nahi hai, isliye Flash Fill fail ho raha hai.
AutoFill (Drag Handle): Ye tab use hota hai jab tumhe Ginti (Numbers) ya Pattern (Jan,
Feb...) aage badhana ho.
●​ Solution: Agar tumhe 12, 13, 14... chahiye, toh Ctrl + E mat dabao.
●​ Bas 12 aur 13 ko select karo, aur mouse se corner pakad ke niche kheench do
(Drag karo).
_________________________________________________________________________
Flash Fill (Ctrl + E) - Interview Favourite! Ye feature interviewer ko impress karne ke
liye best hai.
●​ Scene: Ek column mein pura naam hai (e.g., "Rahul Sharma"). Tumhe First
Name alag karna hai.
●​ Trick:
1.​ Side wale cell mein haath se likho "Rahul".
2.​ Uske niche wale cell pe aao aur Ctrl + E daba do.
3.​ Boom! Baaki saare naam (Amit, Priya etc.) apne aap aa jayenge.

@ Basic Formatting (Sajawat) Ganda data koi nahi padhta.

●​ Borders: Data select karke Home Tab > Borders icon pe jake "All Borders" kar
do. Tabhi print mein lines dikhengi.
●​ Wrap Text: Agar cell mein likha hua text lamba hai aur chup raha hai, toh Wrap
Text pe click karo. Text cell ke andar fit ho jayega (niche aa jayega).
 ●​ Merge & Center: Heading ko table ke beech mein lane ke liye cells select karo
aur Merge & Center daba do.
8. Merge & Center (Heading banana)
Concept: Iska kaam hai "Deewar Todna". Ye kayi chhote cells ko tod kar ek bada cell
bana deta hai aur text ko beech mein le aata hai.
Practical Karo:
1.​ Cell A1 par click karo aur mouse se select karte hue E1 tak jao (A1 se E1 select
karo).
2.​ Ab upar Home Tab mein dekho, wahan "Merge & Center" ka button hoga. Uspe
click kar do.
3.​ Dekho, wo 5 cells milkar ek bada cell ban gaye.
4.​ Ab isme type karo: STUDENT RESULT 2025 (Dekho ye apne aap beech mein aa
gaya).

9. Wrap Text (Lambi line ko adjust karna)

Concept: Jab text cell se bahar nikal raha ho, toh ye us text ko fold karke niche le aata
hai (Column chauda nahi hota, Row lambi ho jati hai).
Practical Karo:
1.​ Ab niche Cell A3 mein aao.
2.​ Wahan ye type karo: "Total Marks Obtained in Final Exam".
3.​ Enter dabao. Tum dekhoge ki ye text A3 se bahar nikal kar B3 aur C3 ke upar ja
raha hai.
4.​ Ab wapas A3 cell par click karo.
5.​ Upar Home Tab mein "Wrap Text" button (Merge ke just upar hota hai) pe click
karo.
6.​ Jaadu dekho: Text ab cell ke bahar nahi ja raha, balki cell ki height badh gayi
aur text 2-3 lines mein aa gaya.

LOOK IT IS B2
 NOW I CAN PROPARLY SEE B2.
10. Line darker
.Select Karo: Sabse pehle mouse se A1 se lekar E5 tak saare dabbe select kar lo (Blue
ho jayega).
1.​ Kahan Dekhna Hai:
○​ Upar Home Tab mein dekho (jo already khula hai).

○​ Bas wahi ruk jao!

2.​ Icon Pehchano:
✋
○​ Wahan tumhe B (Bold), I (Italic), aur <u>U</u> (Underline) dikh raha hai?

○​ <u>U</u> (Underline) ke bilkul bagal mein ek Chokor Dabba (Square)

bana hoga.
○​ Ye "Paint Bucket" (Baalti) ke left side mein hota hai.
3.​ Click Karo:
○​ Us Chokor Dabbe ke bagal mein ek chhota sa Teer (Arrow ▼) hai. Uspe
click karo.
○​ Ek list khulegi. Usme "All Borders" dhundo (iska icon ek window jaisa
dikhta hai).
○​ Uspe click kar do.
Short Trick: Agar icon nahi mil raha, toh ye keyboard shortcut dabao (Select karne ke
baad): Alt dabao, phir H dabao, phir B dabao, phir A dabao. (Dhire-dhire, ek ke baad
ek).

Ek Chhoti si Tip (Bonus): Tumhare screenshot mein "YASH VARDHAN" wala naam
bahut patla (lamba) dikh raha hai.
●​ Upar A aur B column ke beech mein jo line hai, usko pakad ke thoda Right side
kheench do. Naam thoda khul jayega aur sundar dikhega.
11.formula_building -
Use by sine “=”
Their is 2 kinds of formula’s

1.

2.
HAM NE IS JAGAH PE SEEDHA FORMULA ETER KAR DIYA HE AND ANSWER SAMNE
HE .
 ●​ Aab ham ise copy kare jese 77 jaha formula likha tha use copy kare

●​

To jab ham past kare to hame bohot sare options mil jaye ge aapko kya past karna he
aapko koi value past karni he ya formula ya kuch aur
Ham seedhe value past kar de to direct ham value ko dekh sakte he .
If ham formula past kare to wo E3+E4 aesa kuch hoga to bhi answer 0 hog
_________________________________________________________________________

12. Type 1
Excel mein har formula = (Equal to) se shuru hota hai.
●​ Jod (Add): =A1 + B1
●​ Ghata (Subtract): =A1 - B1
●​ Guna (Multiply): =A1 * B1
●​ Bhaag (Divide): =A1 / B1
Pro Tip (AutoSum): Agar tumhe ek lambi list ka total karna hai, toh numbers ke niche
jao aur Alt + = daba do. Excel apne aap SUM() formula laga dega.

[Link] 2
1.​ SUM() Kaam: Iska kaam simple hai—list me jitne bhi numbers hain, un sabko
jodna (add karna). Jaise agar aapko pure mahine ka total kharcha nikalna ho,
toh ye function saare amounts ko plus karke total bata dega.
2.​ AVERAGE() Kaam: Ye function data ka "beech ka maan" (mean) nikalta hai.
Matbal, ye sabhi numbers ko jodta hai aur phir count se divide kar deta hai.
Jaise agar aapko dekhna hai ki class me average marks kitne aaye (na sabse
zyada, na sabse kam, bas ek beech ka figure), toh ye use hota hai.
3.​ MIN() Kaam: Iska full form hai Minimum. Ye apke select kiye huye data me se
sabse chhoti value dhoondh kar deta hai. Jaise agar aapko pata karna ho ki
sabse sasta product kaunsa hai, toh ye turant sabse kam price bata dega.
4.​ MAX() Kaam: Ye MIN ka ulta hai (Maximum). Ye data me se sabse badi value
nikalta hai. Jaise agar aapko dekhna ho ki company me sabse high salary kiski
hai, ya sabse zyada score kisne kiya, toh ye function use hoga.
5.​ COUNT() Kaam: Ye ginti karta hai ki kitne cells me data bhara hua hai, lekin iski
shart ye hai ki ye sirf un cells ko ginta hai jinme numbers (ank) likhe hain. Agar
kisi cell me naam (text) likha hai, toh ye use count nahi karega, ignore kar
dega.
6.​ COUNTA() Kaam: Iska matlab hai "Count All". Ye har us cell ko ginta hai jo
khali (empty) nahi hai. Chahe cell me number ho, text ho, date ho, ya koi
symbol—agar cell bhara hua hai, toh ye usko ginti me le lega. Ye tab kaam aata
hai jab attendance ya naam count karne hon.
7.​ ROUND() Kaam: Jab numbers points (decimals) me hote hain (jaise 99.8765),
toh ye unhe simple aur clean banane ke kaam aata hai. Aap isse set kar sakte
hain ki point ke baad kitne digits dikhne chahiye ya bilkul hat jane chahiye,
taaki data padhne me aasan ho jaye.
 alt+= dabane ke bad .

[Link] reference vs absolute reference

Rr- not fix
Ar - fixed
The Most Important Concept: Locking with $ (F4 Key)
Ye interview ka favourite question hai: "Relative vs Absolute Reference kya hota hai?"
Ise ek example se samajhte hain:
Scenario: Tumhare paas 10 Products ke price hain, aur tumhe sab par 18% GST
lagana hai. GST ka rate (18%) ek alag cell (D1) mein likha hai.
●​ Galti: Agar tum formula lagate ho =A2 * D1 aur use niche drag karte ho, toh
Excel agle cell mein =A3 * D2 kar dega.
○​ Lekin D2 toh khaali hai! Isliye answer 0 aayega. Excel dimaag nahi
lagata, wo bas pattern follow karta hai (ek step niche = sab kuch ek step
niche).
●​ Solution (Taala Lagana 🔒 ): Hume Excel ko bolna hai ki "Bhai, Price (A2) toh
change karte rehna, lekin GST Rate (D1) ko hilne mat dena."​
Iske liye hum Dollar ($) use karte hain.​
Sahi Formula: =A2 * $D$1
○​ Kaise karein? Formula type karte waqt D1 select karo aur keyboard pe
F4 key daba do. D1 ban jayega $D$1.
○​ $ ka matlab: Taala (Lock). $D matlab Column lock, $1 matlab Row lock.
To aab hame yaha har price pe 18% gst lagana he and ototal price nikalna he .

= laga ke formula likh rahe he

F4 dabaye to

But dono me hi answer aaye ga 90 .

Tala lagane ki vaja se hamne neeche drag kiya to hamare pass aayi different value
depend on prize , yaha sab [pe laga he 18% gst .
[Link] - practical approach if laga ke pass ya fail ki marksheet banana .

Formula lagaya C3 ke liye

Bas ise drag kare to ye sab ke liye lagta jaye ga .

Drag karo .

Multiple conditions .

jo 1
condition insari condition me sab se pehele true hui usko print kar diya jaye ga , aage
ki condition true ho ya false usase matlab nhi hoga fir .
16) AND()
Sari condition true ho to TRUE , aek bhi condition false hui to pura false .​
=AND(A1>10, B1<20)
Iska [Link] he .
17) OR()
Kam: Ek bhi condition true ho to pura TRUE​

18) NOT()
Kam: True ko false, false ko true, aek dam sahi .​
=NOT(A1="Paid")

19) IFERROR()
Kam: Error aane par custom message dena​
=IFERROR(VLOOKUP(...), "Not Found")

1/1=1 , yaha error nhi aaya to seedha 1 ko print kar diya .

Error aa gaya .

:D

[Link] - - 2 cells ki cheeso ko aapas me

jodne wala .
-​ CONCATENATE(), concat ka old version he , same use .

21. Left - kisi bhi character ko left se nikalte he jitne chahe utne characters .

B4
[Link] - same as left but right se .

[Link]- matlab mid automatic nhi nikle ga , aapko starting point deta he kis jagah se
data uthaye and kitna data uthaaye us jagah se lekar kitna number of data .

[Link]- aapke cell me under kitne number of charecter he ye count karta he .

[Link] - extra space ko remove karta he .

[Link] - sare letters ko upper shift kar deta he.

[Link] - sare letters ko lower shift kar deta he .
[Link] - word ke starting ke letter ko upper kar diya and baki ke sare small .
imp
[Link] - vertical lookup, iska use kisi bhi table mese value dhundane ke liye hota
he .
=VLOOKUP(id( kiske samne ki chees chahiye aapko jese neeche table me B2=yash he
uske samne ki chees mile gi aap ko ), puri table ki range = jitni chaho utni table sellect
kar sakte ho aap yaha pe , table ki kon si jagah (man lo table me 8 column he to jo
value aap ko chahiye wo kon se column me he neeche sellect kiya gaya he column 4
(matlab yash ke samne column 4 ki value dedo ), exect value chahiye ya round off
value (ture likha to round off value mile gi false likha to exect value mile gi 0 or 1 bhi
likh sakte he ))))).
True = 1 = aprox value , upar neeche se ye kuch bhi print kar ke de sakta he .

print kiya he isne aprox value ke liye kam aaye ga , 77 ko ye 70 man ke chal raha he .

False = 0 = exact fix value , koi upar neeche nhi .

B2 jis row me he uski 4th value ko print karo .

Table ki range ko fix kar ke f4 se drag n drop kare to dusri values bhi show hogi .

If table me duplicate values he to vlookup me aapne id dali he a4 wo dekhe ga

a4=1001 to wo jesehi 1001 ko peheli bar dekhe ga uske samne wale ko print kar dega
wo address nhi dekhe ga a4 wo sirf id ka nam dekhe ga 1001
Yaha pe use a4 matlab 18 print karna tha , par usne a4-1001 dekha aur 1001 a1 bhi he
to usne a1 ke samne wale ko print kar diya 8.
To jab bhi vlookup use karo table me duplicate nhi ho warna ye dhang se kam nhi kare
ga .

Multiple sheets ko connect kar ke kisi dusri sheet ka data kahi aur show kar sakte he .

Jese mene s4 me s1,s2,s3 ko lagaya he unka data yaha show kiya he , cell number se
pehele sheet n! Likhna he bas .

[Link] - horizontal look up -

Aapko table ke sab se upar wale colums me se aek ko sellect karna hoga ,fir aap table
ki range dege , fir aap row num dege, fir exect value chahiye ya round off chahiye ye
dekhe ge )
v look up me jab data vertical ho tab vlookup use hota HE , jab data horizontal ho tab
hlookup use hoga .
HLOOKUP KA PURA formula v look up ki tarh hota he and kam bhi vlookup jesa he

N1 jis column me he us column ke 7th element ko print karo .

S2 ke end ki value show ho rahi he 0.885 % solve kar ke .

[Link] - vlookup vertical search karta tha and hlookup horizontal but xlookup
horizontal and vertical dono dishao me search karta he .

1003 ke samne jis row ya column

ka element fix he usko print kar do , vivek print hua

Ham fir se vivek ko printkare ge but new tareeke se

L3 ke samne vali row ya column ka element jo fix

he use print kar do ,vivek print hua , bas isbar kuch new use kiya ham ne .
[Link] - khazana khozne ka naksha

m1 se 2 kadam neeche then 2 kadam right me vivek mil jaye

ga .

vivek printed.

[Link]()+match()
index()- cell ke coordinates ke bases pe value return karta he -

Array = puri table

4=row, 5=col
match() -ye result me coordinates deta he, aapne aek value diya ise ex- food , 47 - ki
iseke coordinates dhundo , aur wo

Array matlab table = ise puri table nhi dena sirf single line dena .

jagah di ki kaha dhundana he row ya column , ise table nhi dena , and exect value - ye
aapko cordinate print kar ke dede ga - ex- 1,2,3,4…
●​ Index = index(puri table , table ka row number , table ka column ) = row and
column se pata lag jaye ga kon sa cell print karna he .
●​ Match = index ko aek number dene ke kam aa sakta he , kyu ki match ka final
out put aek number hota he , ye horizontally and vertically dono kam karta he .
●​ match (kya dhundana he , kaha dhundana he )

MATCH() -puri table ko sellect mat karna koi answer nhi dega ye fir , firs aek column ki
line ya row ki line ko select karo usme ye us value ko dhund ke de sakta he jo tum
dhund rahe ho
index(puri table , table ka row num , tab ka column num ) = cell print.
match(kya dhundana he , kaha dundana he , exect value )

34. TODAY()

35. NOW()

36. DAY(), MONTH(), YEAR()

Particular chees ko print kar ke dega
Day bolo ge to ye date se only date ko print kar ke dega , 31

Month me only month ko print kar ke deta he ye puri date se , 12

Year me date se sirf year , 2025

37. DATEDIF()- ye hota hi nhi he .

Kam: 2 dates ke beech ka difference (days/months/years)​

=DATEDIF(A1, B1, "M")

[Link]() and filter()

Table ko select karte he , and uspe ascending or descending sorting ko apply
karte he ,
Filter ko apply karte he and aapne data table ko filter kar ke dekhte he jese
chahe wese, number ke bases pe filter kar sakte he , city ke bases jese chahe
uske bases pe filter ko apply kar ke filter kar sakte he .
Sort - data ko order me lagana.
* Sort by color

[Link] data
[Link] filter
Is data se patna city ke sabhi employes ko sellect karna he and dusri sheet pe
pohochana he , kese kare.

Us hi sheet pe patana nam se cell banaye ge .

Dusri sheet pe click kare ge data ke under yaha advance pe click kare ge .

Af se ye dilog box open hoga and copy to

another location ko select karna he ,
[Link] wala list range he , waha jake purani wali puri sheet ko sellect kar lege .
[Link] range - me patana wala aalag se cell jo nikala tha use select kare ge .
[Link] wala copy to he , waha pe jaha aapna patana wale data ko lejana he wo
cell sellect karo , sara data us jagah aa jaye ga jo tum chahte ho patna wala
data .

[Link] duplicates from excel

[Link] duplicate by unique keyword.

2. in data

to hamne aaj such me duplicate value ko remove

kiya and advance filter ka bhi use kiya precticaly kafi sahi tha ye .
just select this for removing duplicates
3.=UNIQUE()

[Link] to column (split text to column )

1 column me itane sare elements he , =coma se separate

hue he , aab
Data pe jao and split and column select karo

click on opply
[Link] validation -
Data entry ke under rule lagana ki isme only is type ka hi data aaye ga ,

web version me data validation bhi he .kkkkk

[Link] down manue creat karna .

[Link] se pehele aek column sellect karna he jaha pe data validation lagana he , drop
down jaha lagana he .
[Link] down manue ke liye , list sellect karna he
[Link] column ko select kiya he , us pure column me sare options show hoge jo aapne
add kiye he

[Link] lagana he , is type ka data dalna he , is type ka data nhi dalna .

[Link] select kiya
[Link] velidation pe gaye
[Link] ki jagah data ko sellect nhi kiya whole num ko sellect kiya .

4.
[Link] aapne 18 se kam dala to error aaye ga 18 ya usase jada to sahi he .
[Link] ko bhi set up kar sakte he

[Link] ko bhi aapne hisab se set kar sakte he

[Link] number ke liye bhi set kar sakte ho

[Link] -
Aek single cell ko select karo and uske upar and bagal aek dam kune se jo joint hoti
he wo feez ho jaye gi jat tak aap usko unfreez dobara se nhi karte .

jese mene isme gwalior ko sellect kiya .

gwalior jaga box ko cut kar raha he us jagah

pe freez at section apply hua , aab aap upar neeche bagal me scroll karo ye free hi
rahe ga jab tak aap use un freez nhi karte .
[Link] duplicates
Home tab me aao and
[Link] table , charts.
Aek power full excel tool he .
Kisi bhi bade complex data ki summary banata , use aache se analys karna aasani se ,
pattern dhundana , uske under ke data se hi use compare karna .
chatgpt
[Link] table hota kya he .
-​ Kafi power full Tool hota he excel ke under .
[Link] kam ke liye use hota he .
-​ Data ko calculate, summarize , analyse karne me use hota he ,
-​ Large number of data ke liye use hota he ,
-​ Patter , trend find kar sakte ho
-​ Data ko aapas me compare bhi kar sakte ho .

[Link] sheet ye he pura data .

[Link] table ko select karo insart ke under .

[Link] pop up aaye ga table ki range pop up ke under select nhi ho to khud sellect kar
lena , existing worksheet pe kam nhi karna uljhan ho jaye gi , new worksheet bana lo
badiya vahi kam karna , ok karte hi sheet 2 open ho jaye ga waha kam karo badiya .

[Link] sheet open hogi .

Level -1
Level -2
Level -1

To mene chat gpt se task liye pivot table pe perform karne ke liye .

Region and sum of sales ko dala row and values me

Higher to lower sort karna tha is chees ne bohot pareshan kiya

Sales ke kisi bhi number pe right click karo and sort karo large to small simple .
Task 2 - keheta he ki 1 insan ne different areas me kitne ki sales ki he .

ham ye dekhe ki aek insan ne

kitne ki sales ki he to ye dikhe ga .
 david ne sab se jada sales ki he .
Aab dekhe in logo ne different category me kitni sales ki he .
To devid ne har area me sab se jada sales ki he .
 aabhi
isme autometicly year dikh raha he isko group kar ke month me convert karna hoga .
 year ki value me right click
kiya and group ko select kiya .

grouping me sirf month ko sellect kiya , ok

kiya.
and years month me convert ho gaye .
Region wise sales dekhi , har region ko aur detail me dekha unki top sale dikhe .
 sare region ki top six sales dikh rahi he
isko top 3 me convert karna he .
 sales repo wale top 6 ke
kisi bhi value pe click kiya right click then filter pe click kiya

filter
se top 10 pe click kiya .
 top 3 sales har region ki show hone lagi
mission complete +
Premium = returning

castumar ke under sirf itni cheese he

aur answer sirf aek single value hona chahiye .
Customer ko filter me dal ke sirf returning value dekhni he aasan he .
 to
humne filter pe sirf returning sellect kiya and aa gaya uska out put .
Yaha pe 2 cheeso ka kam he bas unit price and product cat

by default ye sub pe set he

ise average karna hoga .
 ise click kiya and value field me gaye .

yaha pe avg sellect kiya and ok kiya .

aa gaya relust avg .
Isko karne ke liye sab se pehele ham pivot analyze me jaye ge .

Iske bad ham fields items and sets pe click kare ge

yaha ham calculated field me

jaye ge .
aesa kuch khule ga
Yaha name and formula put karna he

Ye kar ke ok kar dege .

pivottable me aek new folder add ho gaya .

 values me sum of profit dikhe ga profit aapne
banaya he new value hogi jo formula se nikali he .

ho gaya .
 aabhi hamne ye dala
isme

ye dikh raha he
running total dikh gaya .
Running total in select karna he bas usme % nahi hona chahiye .
Running total me pichle wale ko add kar ke dikhate he aur last wale me sare mahino
ka amount add hota he matlab last wala total hota he .
 region and sales ko
select kar ke dala .
show value as grand total .
result .
 pivottable me hamne isko
dala .in dono ko .
insert pe aa ke hamne pivotchart pe click kiya .

region and sales ka graph khul gaya samne .

Aab hame sliser add karna he .

chart analyzer pe gaye and insert slicer

pe click kiya .
 region pe ok kiya .

aab aap aapne hisab se chart ko set kar ke dekh sakte ho .

Mujhe theek se excel file save
karni nhi aati thi is waja se data
lost ho jata tha but aab aa gayi
he save as workbook karna he .
Level-2
Solution

Task -1
Ham chahte kya he -

Aabhi hamare pass 3 file ban gayi he and aab hame unhe aapas me connect karna he .

Yaha gaye

from other sources me

aaye .
Excel file ko select kiya .
Then us file ko close kiya jise hame yaha add karna he
Usme 3 files he
Uske under ki teeno sheets ko sellect kari

Teeno sheets ko import kar diya he

Aab teeno me rilation banana he

Yaha tik karna boh0t jaruri he , pichli bar rilation ship banane me dikat aa gayi thi .

Aab rilation ship me sahi se nam dikh rahe he .

Rilation ship bana li teeno me

Ye structure perfect he .

Pivot table banai

Task-2

Ye galat he isase galat values dikh rahi the.

To hamne data file insart kar ke aro ko ulta lagaya he okk ki taraf ye log ja rahe he .
ye sahi rilation he .

pivot table pe gaye to yaha model

ka option aa raha he kyu ki hamne aabhi model banaya he .
Ham yaha tak aa gaye he pichle task ka kam hi tha ye
masure me gaye and aab new measure bana rahehe

Hamne first measure banaya he .

Frofit tha nhi is liye toda aur edit kiya ise .
Sab kuch karne ke bad hamare pass aata he ye .
So task is completed

TASK -3

Completed
TSAK-4

Me duble click kar raha tha , topics pe , hading pe , characters pe , wo kehene laga is
jagah pe drill nhi hota he , phir profit margen hataya laga iski waja se nhi ho raha he ,
phir real value dali , value pe click karo tab hi hota he sirf, wo to new sheet khuli thi
uska nam rakha drill report and ok and ho gaya .
TASK- 5

month me group karna he .

Ye karna tha bas.

TASK - 6

Jun ki jagah privious sellect karna he .

TASK- 7

Ok ho gaya rank dekhni thi

Region ke under ki rank dikhne lagi

TASK -8

Filter me gaye and change kar diya

Ho gaya
TASK -9

Copy past ki madat se hamne 2 pivot table aek hi sheet pe bana li he .

1 pivot table to thi hi , copy kar ke aab 2 pivot table he

Fir slicer ka use kar ke region select kara and slicer bana diya

Aab slicer ko 2 pivot table se connect karna he

slicer me gaye dono

table ko add kar diya , report connection ki waja se .
Ok to dono table ko connect karne ke bad kisi aek ko bhi change karo to dono me aek
sath fark dikhta he .

Task - 10
Are step follow kar ke sab kar dala .

TASK - 11

sab se pehele equal lagaya fir

grand total ki value pe click kiya he .
TASK -12
ONE_TO_MANNY