📊 Microsoft Excel

Complete Study Notes

Formulas • VLOOKUP • HLOOKUP • INDEX-MATCH • Macros • Text Functions • Pivot

Tables

📌 SECTION 1: Basic Formulas

1. Random Number Generator

RandBetween: =RANDBETWEEN(20, 50)
• Shortcut to use RANDBETWEEN: Press F2, then Ctrl + Enter
• Yeh formula 20 se 50 ke beech random number deta hai

2. Copy Data (4 Values Change Karna)

• Copy data karo → Alt + H + V + U (Paste Special – Values)
• Isse formula nahi, sirf value paste hoti hai

3. Basic Formulas
Total / SUM: =SUM(num1, num2)
Average: =AVERAGE(num1, num2)
Maximum: =MAX(num1, num2)
Minimum: =MIN(num1, num2)

4. RANK Function
RANK: =RANK(student_avg, whole_class_avg_range)
💡 Note: Yeh formula ek student ka rank puri class mein batata hai

5. Result (IF Formula)

Result: =IF(Total>33, "Pass", "Fail")
• Agar Total 33 se zyada hai → Pass, warna Fail

6. Grade (Nested IF)

 Grade: =IF(Avg>60,"A", IF(Avg>50,"B","C"))
• Avg > 60 → Grade A
• Avg > 50 → Grade B
• Baaki sab → Grade C

📎 New Sheet Shortcut

⌨ Shift + F11 → Nayi sheet add karo

📌 SECTION 2: VLOOKUP

VLOOKUP kya hai?

VLOOKUP ek powerful formula hai jo ek column mein value dhundh ke, usi row ka koi aur data
nikaalta hai.

VLOOKUP Steps (Data Validation ke saath):

• Step 1: Data select karo aur usse ek naam do (Name Box mein)
• Step 2: 3-4 alag alag sheets banao aur unhe naam do, jaise: 'Years'
• Step 3: Final result sheet banao
• Step 4: Data tab → Data Validation → Allow: List → Source: 'Years' → OK
• Step 5: Dropdown se data select karo

VLOOKUP Formula (with INDIRECT):

Formula: =VLOOKUP(Product_name, INDIRECT(Ref_Cell), 2, 0)
💡 Note: INDIRECT function ek cell mein likhe sheet ke naam ko reference ki tarah use karta hai

VLOOKUP Advanced Formula:

Syntax: =VLOOKUP(lookup_value, table_array, col_index_no, 0)
• lookup_value = woh value jo dhundni hai
• table_array = poora data select karo
• col_index_no = column number jahan se data chahiye
• 0 = Exact Match

📌 SECTION 3: HLOOKUP

HLOOKUP kya hai?

HLOOKUP (Horizontal Lookup) – yeh row mein data dhundta hai, column mein nahi.
Syntax: =HLOOKUP(lookup_value, table_array, row_index_no, 0)
HLOOKUP ke Parts:
• GC = Name of the row (header)
• Table Array = Lookup value (1, 2, 3, 4)
• Row Index = All data selected → 4
• Exact Match = 0
• True → 1

📌 SECTION 4: INDEX & MATCH

INDEX MATCH kab use karte hain?

Jab VLOOKUP kaam na kare – especially jab data right se left side se nikalna ho.

MATCH Function:
MATCH: =MATCH(lookup_value, lookup_array, 0)
• Column number find karne ke liye match ka use karte hain
• (lookup_value) = jo value dhundni hai
• (lookup_array) = jis column/row mein dhundna hai
• Usse poori select karenge
• (0) = Exact Match

INDEX Function:
INDEX: =INDEX(array, match(lookup_value, lookup_array, 0), 0)
• DATA ko right hand side se nikalta hai
• Full table select karo → apply karenge

VLOOKUP + MATCH (Combined):

Formula: =VLOOKUP(lookup_value, table_array, MATCH(lookup_value,
lookup_array, 0), 0)

INDEX + MATCH (Combined):

Formula: =INDEX(column_array, MATCH(lookup_value, lookup_array, 0),
0)

📌 SECTION 5: Date Functions

Date Shortcuts:
⌨ Ctrl + Shift + ; → Current Time insert karo
⌨ Ctrl + ; → Aaj ki date insert karo
DATEDIF Function:
Find Day (Months): =DATEDIF(DOB, TODAY(), "MD")
Find Months: =DATEDIF(DOB, TODAY(), "YM")
Find Years: =DATEDIF(DOB, TODAY(), "Y")
💡 Note: Agar DOB diya hua ho tabhi yeh formulas use karte hain

📌 SECTION 6: Text Functions

1. UPPER
UPPER: =UPPER(text)
• Kisi bhi text ko Capital (UPPERCASE) mein change karne ke liye

2. LOWER
LOWER: =LOWER(text)
• Capital letters ko small (lowercase) mein change karne ke liye

3. PROPER
PROPER: =PROPER(text)
• Jab koi text lower case mein ho ya usme koi capitalization na ho, toh PROPER se sahi
karenge
• Eg: 'harsh yadav' → 'Harsh Yadav'

4. LEFT
LEFT: =LEFT(text, No_of_characters)
• Jab kisi text ke left side se hume left vale text ko lena ho

5. RIGHT
RIGHT: =RIGHT(text, No_of_characters)
• Jab kisi text ke right side se data nikalna ho

6. CONCAT
CONCAT: =CONCAT(text1, text2)
• Do alag column mein likhe text ko ek sath dikhne ke liye
• Eg: Tarun + Tiwari → TarunTiwari
💡 Note: Space ke liye: =CONCAT(text1, " ", text2)
7. TEXTJOIN
TEXTJOIN: =TEXTJOIN(" ", 0, Text1, Text2, Text3)
• Jab kisi details ko alag alag column mein dikhana ho
• Eg: Tarun | Sharma | Delhi → Tarun Sharma Delhi

8. SUBSTITUTE
SUBSTITUTE: =SUBSTITUTE(text, "2013", "2026")
• Kisi sentence mein ek word ya sentence ko replace karne ke liye
• Excel 2013 se use karke replace karenge → use hum Excel 2026 likhenge

9. LEN
LEN: =LEN(text)
• Sentence mein kitne characters ya space hain, unhe count karne ke liye
• Alphabet aur usme space ko count karta hai

10. REPLACE
REPLACE: =REPLACE(OldText, StartNum, No_of_Names, NewText)
• Jab hume kisi text ko kisi aur text se replace karna ho → Mum replace ka use karenge
• Eg: =Replace(Harish, 1, 5, Replaced text) → [Replaced text]

11. DOLLAR
DOLLAR: =DOLLAR(text)
• Kisi bhi number ko dollar format mein change karne ke liye
• Number ke uske liye hum = DOLLAR(text) enter karenge
• And (decimal) and Enter karenge

12. REPEAT
REPEAT / REPT: =REPT(text, No_of_times)
• Jab hume kisi bhi name ko without space repeat karna ho → use karenge
• Eg: REPT(text, No) → repeated times

13. TRIM
TRIM: =TRIM(text)
• TRIM ka use hum Space ko kam karne ke liye karte hain

14. UNIQUE
UNIQUE: =UNIQUE(text)
• Duplicate value mein se sabse unique values ko find out karte hain
15. SEQUENCE
SEQUENCE: =SEQUENCE(No_of_rows, No_of_column, Start_No, Step_No)
• Jab kabhi hume series banani ho ya table banani ho
💡 Note: Sequence mein: Row No, Column No, Start No, Step No daalna hota hai

📌 SECTION 7: Flash Fill

Flash Fill kya hai?

Jab bahut saara data ek saath ek column mein ho aur unhe separately dikhne ke liye alag alag
karke manually likhna pade...

Steps:
• Pehle ek example manually type karo (jaise: alag column mein alag dikhna chahte ho)
• Uske baad → Ctrl + E dabao
• Data automatically separate ho jayega!
Shortcut: Ctrl + E (Flash Fill)

📌 SECTION 8: Conditional Formatting

Alternate Row Color Highlight karna:

• Click on Home tab → Click 'New Rule'
• 'Use formula to determine which cell' select karo
• Fill formula in dialogue box: =MOD(ROW(),2)=0
• Format → Select colour → OK → Again OK

📌 SECTION 9: AutoFit Column (Entire Sheet)

Method: VBA Code

• Sheet par right click → View Code
• General → Worksheet
VBA Command: [Link]
💡 Note: ### means the column is too narrow – AutoFit will fix it

📌 SECTION 10: Total & Grand Total

Shortcut:
⌨ Alt + H + U + S → Total select karo
⌨ Alt + H + U + A → Average ke saath Grand Total

📌 SECTION 11: Merge Cells

Merge karna:
⌨ Alt + H + M + M → Merge & Center
⌨ Alt + H + M + C → Merge & Centre
⌨ Alt + H + M + U → Unmerge Cell

Navigation in Sheets (Move Karna):

⌨ Ctrl + Page Up → Previous sheet mein move karo
⌨ Ctrl + Page Down → Next sheet mein move karo
⌨ Alt + H + B + A → All Borders lagao

Merge Steps (Table ke liye):

• Merge Karne K Baad Ek Saame Sheets mein (Sales 2024, Sales 2025) same cells ko
merge karenge
• Assign karenge → Iske Baad har sheet k left corner mein ek dialogue box hoga
• Use bhi hum Name assign karenge – No Name Sheet mein pehle use nahi honga

📌 SECTION 12: Macro Creation

Macro kya hai?

Macro ek recorded set of actions hai jo aap baar baar perform karte ho – Excel automatically
repeat kar deta hai.

Macro Banane ke Steps:

• Step 1: Select all data → Copy (Ctrl + C / Alt + H + V + U)
• Step 2: Next sheet → Paste de (Ctrl + V / Alt + H + V + U → click on)
• Step 3: Headers OK → Fill data → Click on Developer
• Step 4: Record Macro → Macro name dena → Select → Copy data
• Step 5: Developer → Click outside → Again same area mein click down → copy
• Step 6: Next Sheet → Click outside from data → Give macro name, Select headers
• Step 7: Ctrl + V → Ctrl + T → Again use relative references
• Step 8: Paste first and stop recording
Shape (Button) ko Macro Assign Karna:
• Insert tab → Shapes → any shape select karo
• Shape ka naam dena → Button name dena
• Then right click → Assign macro → Select it → OK
• Click outside → click macro → click outside the button

📌 SECTION 13: Pivot Table

Shortcut to Open Pivot Table:

⌨ Alt + N + V + T → Insert Pivot Table

Text Function ke saath Pivot:

• Upper: Kisi bhi text ko capital se change karne ke liye
• Lower: Capital letters ko small mein change
• Proper: Text ko sentence case mein karo
• Right: Right side se text nikalna

─────────────────────────────────────────────────────

Excel Notes – Prepared with ❤ | Practice karo aur master bano!