VLOOKUP & XLOOKUP — Data Dhundna

VLOOKUP Excel ki sabse important formula hai. Jab aapke paas do alag sheets mein data ho aur aap unhe match karna chahte ho — VLOOKUP use hoti hai. For example, ek sheet mein employee names hain aur doosri mein salaries — VLOOKUP dono ko connect karti hai.

Example: Employee ID se Employee Name dhundna:

Pivot Tables — Data Analysis Ka Shortcut

Pivot Tables Excel ki most powerful feature hai. Lakhs of rows ka data 2 minutes mein summarize ho jaata hai. Finance mein monthly reports, HR mein attendance analysis, Sales mein region-wise performance — sab Pivot Tables se hota hai.

Kaise banate hain:

Data select karo → Insert → PivotTable → New Worksheet → OK

IF Formula aur Nested IF — Conditions Lagana

IF formula logic check karta hai. “Agar yeh condition true hai toh yeh karo, warna yeh karo” — yahi IF karta hai. Performance rating, grade calculation, tax slab — sab mein IF use hota hai.

SUMIF & COUNTIF — Conditional Sum/Count

Specific condition ke basis pe sum ya count karna ho toh SUMIF aur COUNTIF kaam aata hai. For example: “Delhi ke saare sales ka total karo” ya “Kitne employees ne 80% se zyada score kiya”.

Data Validation — Galat Entry Rokna

Jab aap kisi aur ko Excel file fill karwate ho, toh Data Validation se aap restrict kar sakte ho ki kya enter ho sakta hai. For example: sirf dates allowed, sirf numbers between 1-100, ya ek dropdown list.

Kaise use karein: Data tab → Data Validation → Settings → Allow → List/Number/Date

Conditional Formatting — Data Visually Highlight Karna

Conditional Formatting automatically cells ka color change karta hai based on values. Due dates red ho jaati hain, high performers green, targets miss ho toh yellow — sab automatically. MIS dashboards mein yeh bahut use hota hai.

Kaise use karein: Cells select karo → Home → Conditional Formatting → New Rule

Charts & Graphs — Data Ko Visual Banana

Numbers se zyada powerful Charts hote hain. Management presentations mein raw data ki jagah clear charts chahiye. Bar chart, Line chart, Pie chart — Excel mein sab available hain aur customize bhi ho sakte hain.

Best Practices:

✅ Bar chart — comparisons ke liye | ✅ Line chart — trends ke liye | ✅ Pie chart — percentage ke liye

TEXT Functions — Data Clean Karna

Real world data aksar messy hota hai — extra spaces, mixed case, joined text. TEXT functions se yeh sab clean hota hai.

Keyboard Shortcuts — Speed Badhao

Excel mein kaam karne ki speed shortcuts se 3x badh jaati hai. Yeh shortcuts roz kaam aate hain:

Basic Macros & VBA — Automation

Yeh advanced skill hai lekin bahut valuable hai. Jo kaam aap manually 30 minutes mein karte ho, Macro woh 30 seconds mein kar deta hai. Monthly reports generate karna, data format karna, emails send karna — sab automate ho sakta hai.

Shuru kaise karein: View tab → Macros → Record Macro → Apna kaam karo → Stop Recording