Aaj ki video mein hum Excel ke ek bahut important concept ke baare mein samjhenge, jiska naam hai Cell References in Excel.
Agar aap Excel properly seekhna chahte hain, to aapko Relative aur Absolute Cell References ko samajhna bahut zaroori hai.
Is video mein hum practically samjhenge:
- Cell Reference kya hota hai?
- Relative Cell Reference kya hota hai?
- Absolute Cell Reference kya hota hai?
- Dono mein kya difference hai?
- Dollar $ sign ka use kaise hota hai?
- F4 shortcut kaise use karte hain?
- Cell References ka practical use kahan hota hai?
What is Cell Reference?
Excel worksheet mein har cell ka ek unique address hota hai.
यदि आप Data Analyst Course सीखना चाहते हैं तो हमारी Technical Team से बात कर सकते हैं!
(HR: 9034756711 | MD: 9017940459)
For example:
- A1
- B2
- C5
- D10
Yahan:
- A, B, C, D columns ko represent karte hain.
- 1, 2, 3, 4, 5… rows ko represent karte hain.
Isliye B2 ka matlab hai:
- B = Column
- 2 = Row
Yaani B2 ek cell ka unique address hai.
Jab hum kisi formula mein kisi cell ko refer karte hain, to us cell ke address ko Cell Reference kaha jata hai.
Example:
Agar humein A1 aur B1 ki values ko add karna hai, to formula hoga:
=A1+B1
Is formula mein A1 aur B1 Cell References hain.
Mein Types for Excel Cell References
Excel mein different types ke cell references hote hain.
Complete Lesson in This Video; So Watch Completely!! 👇 👇 👇
Is topic mein hum mainly do important types ko samjhenge:
- Relative Cell Reference
- Absolute Cell Reference
In dono ka behavior formula copy karte waqt different hota hai.
What is Relative Cell Reference?
Relative Reference Excel ka default cell reference hota hai. Ismein formula ko kisi doosre cell mein copy karne par row aur column references automatically change ho sakte hain.
Yaani reference apni new position ke according move karta hai.
Relative Reference Ka Simple Example
Maan lijiye:
- Column A mein Quantity hai.
- Column B mein Price hai.
- Column C mein humein calculation karni hai.
Agar formula hai:
=A1*B1
Aur is formula ko next row mein copy karte hain, to Excel automatically reference ko change kar dega:
=A2*B2
Agar ise next row mein copy karenge, to:
=A3*B3
Yaani Excel automatically row number ko change karta rahega.
Isi behavior ki wajah se ise Relative Cell Reference kaha jata hai.
Relative Cell Reference Ka Practical Use
Maan lijiye aapke paas multiple products hain aur har product ki quantity aur price alag hai.
Aap pehli row mein formula create karte hain:
=B2+C2
Agar isi formula ko neeche copy karenge, to Excel automatically ise next row ke according change karega:
=B3+C3
Phir:
=B4+C4
Is tarah aapko har row ke liye manually formula type karne ki zaroorat nahi padti.
Relative Reference Ka Main Benefit
Formula ko copy karne par references automatically adjust ho jate hain.
Isse large data mein manual calculation ka kaam kam ho jata hai.
What is Absolute Cell Reference?
Ab baat karte hain Absolute Cell Reference ki. Absolute Reference mein kisi particular cell ko lock kar diya jata hai.
Digital Thinker Help – Best IT Company in Yamunanagar
Ismein:
- Row fixed rehti hai.
- Column fixed rehta hai.
- Formula copy karne par locked reference change nahi hota.
Absolute Reference banane ke liye Dollar Sign $ ka use kiya jata hai.
Example:
=$A$1
Yahan:
- $A ka matlab Column A locked hai.
- $1 ka matlab Row 1 locked hai.
Isliye formula ko kahin bhi copy karein, A1 reference change nahi hoga.
Absolute Reference Ka Practical Example
Maan lijiye kisi Excel sheet mein ek fixed GST rate 18% diya hua hai.
Aapke products ke prices alag-alag hain, lekin GST rate sabhi products ke liye same hai.
Example:
- A1 = 18% GST
- A2 = Product Price
- A3 = Product Price
- A4 = Product Price
- A5 = Product Price
Agar GST calculate karni hai, to GST rate wale cell ko lock karna useful hoga.
For example:
=A2*$A$1
Yahan:
- A2 product price hai aur ye row ke according change ho sakta hai.
- $A$1 GST rate hai aur ye fixed rahega.
Formula ko neeche copy karne par:
=A3*$A$1
Phir:
=A4*$A$1
Lekin $A$1 same rahega.
Yaani GST rate har calculation mein fixed rahega.
Dollar $ Sign Kaise Kaam Karta Hai?
Dollar sign ka use cell reference ko lock karne ke liye hota hai.
Iske different combinations ho sakte hain.
1. $A$1
Column aur Row dono locked hain.
- Column A = Fixed
- Row 1 = Fixed
2. $A1
Ismein:
- Column A = Fixed
- Row 1 = Change ho sakti hai
Yaani formula neeche copy karne par row change ho sakti hai, lekin column A fixed rahega.
3. A$1
Ismein:
- Column A = Change ho sakta hai
- Row 1 = Fixed
Yaani row 1 lock rahegi.
4. A1
Ismein kuch bhi locked nahi hai.
- Column change ho sakta hai.
- Row change ho sakti hai.
Ye normal Relative Reference hai.
Relative or Absolute Reference Mein Difference
| Relative Reference | Absolute Reference |
| Default reference hota hai | Cell ko lock karta hai |
| Row/Column change ho sakte hain | Row/Column fixed reh sakte hain |
| Formula copy karne par reference adjust hota hai | Locked reference change nahi hota |
| Example: A1 | Example: $A$1 |
| Repeated calculations mein useful | Fixed values ke saath useful |
Excel Mein F4 Shortcut Ka Use
Dollar signs ko manually type karne ki zaroorat nahi hoti.
Excel mein F4 shortcut ka use karke cell reference ko different reference modes mein change kiya ja sakta hai.
Basic Process:
- Formula type karein.
- Formula mein cell reference select karein.
- F4 press karein.
- Reference ka format change ho jayega.
For example:
A1
F4 press karne par:
$A$1
Aur F4 ko again press karne par reference ke different locking combinations mil sakte hain.
Is shortcut se formula banate waqt kaam faster ho jata hai.
F4 Shortcut Se Reference Lock Karna
Maan lijiye formula mein aapne A1 select kiya.
F4 press karne par:
$A$1
Again F4:
A$1
Again F4:
$A1
Again F4:
A1
Is tarah F4 shortcut se aap decide kar sakte hain ki:
- Row lock karni hai
- Column lock karna hai
- Dono lock karne hain
- Ya kuch bhi lock nahi karna
Beginners Ki Common Mistakes
Cell References seekhte waqt beginners kuch common mistakes karte hain.
1. Dollar Sign Ko Galat Use Karna
Beginners ko aksar confusion hoti hai ki dollar sign kis jagah lagana hai.
Yaad rakhein:
$A$1 aur $A1 same nahi hain.
Similarly:
A$1 aur A1 bhi same nahi hain.
Jis part ke pehle $ laga hota hai, woh part lock ho jata hai.
Dollar Sign Ka Simple Rule
Is rule ko yaad rakhein:
$A$1
Column + Row = Dono Locked
$A1
Column = Locked
Row = Change
A$1
Column = Change
Row = Locked
A1
Column + Row = Dono Change
Practice karne se ye concept easily clear ho jayega.
Cell References Ka Practical Use Kahan Hota Hai?
Cell References ka use Excel mein bahut jagah hota hai. Especially jab aap ek formula ko multiple cells mein copy-paste karte hain.
Isse manual calculation ka workload reduce hota hai.
Cell References Ka Use:
- Large Data Sets
- Excel Reports
- Salary Calculations
- Tax Calculations
- GST Calculations
- Discount Calculations
- Financial Models
- Product Calculations
- Data Analysis
jaise kaam mein kiya ja sakta hai.
Example for GST Calculation Mein Cell Reference
Maan lijiye:
A1 = 18% GST
Aur A2 se A5 tak different product prices hain.
Aap B2 mein GST calculate karna chahte hain.
Formula ho sakta hai:
=A2*$A$1
Ab is formula ko neeche copy karne par product price ka reference change hoga:
- A2 → A3
- A3 → A4
- A4 → A5
Lekin $A$1 fixed rahega.
Yaani har product ke liye same 18% GST rate use hoga.
Yahi Absolute Cell Reference ka practical benefit hai.
Cell References Ka Main Benefit
Cell References ka sabse important benefit ye hai ki aap ek formula ko multiple cells mein use kar sakte hain.
Isse:
- Manual calculations kam hoti hain.
- Time save hota hai.
- Large data handle karna easy hota hai.
- Formula copying fast ho jati hai.
- Repeated calculations easily perform ho jati hain.
Financial Models Mein Cell References
Financial models mein bhi different types ke cell references ka use kiya ja sakta hai.
Source content ke according, Mixed References bhi financial models mein commonly use hote hain.
Mixed References mein row ya column mein se ek part fixed hota hai aur doosra change ho sakta hai.
Is topic ko future mein separately detail mein samjha ja sakta hai.
How Can Easily Remember Relative and Absolute References?
Ek simple way se samjhein:
Relative Reference
“Formula ke saath reference move karega.”
Example:
A1 → A2 → A3
Absolute Reference
“Reference lock rahega.”
Example:
$A$1 → $A$1 → $A$1
Chahe formula ko neeche kitni bhi baar copy karein, locked reference change nahi hoga.
Final Words
Aaj humne Excel ke Cell References ko detail mein samjha.
Sabse important baat ye hai ki Relative Reference mein cell reference automatically change ho sakta hai, jabki Absolute Reference mein locked cell reference fixed rehta hai.
Agar aap Excel ko basic se advanced level tak seekhna chahte hain, to Cell References ka concept properly samajhna bahut important hai.
Practice karte rahenge to $A$1, $A1, A$1 aur A1 ke beech ka difference easily clear ho jayega.
If this content is valuable for you, then please share it along with your friends, family members, pet lovers or relatives over social media platforms like as Facebook, Instagram, LinkedIn, Twitter, and more.
Do you have any experience, tips, tricks, or query regarding on this? You can drop a comment!