Excel For Data Analysis

1.  sum = Add numbers, it is basically sumission 
   =SUM(number1, [number2], ...)
   Example: =SUM(A1:A7) 
   

   ⚡ Tips:

   • You can use AutoSum for quick addition:

     • Select a cell → click Home → AutoSum (Σ) → press Enter.

   • Shortcut key: Alt + = (automatically inserts =SUM()).

   
   
   
2. Min = TO FIND MINIMUM VALUE 
   =MIN(A1:A12) 
   
   
3. max = TO FIND MAXIMUM VALUE
   =MAX(A1:A13) 
4. Right 
   ExamPle: =RIGHT(E2,10) 
   basically mere pas number hai 91 country code ke sath, main chahta hu 91 code ke bad right se 10 numbers mujhe nikal ke aa jaye yaha E2 mera column number hai and 10 jo mujhe chahiye right se
   

   Cases:

• 
  किसी product code ke last digits निकालने के लिए 
  

• किसी date ya year ke last 2 digits निकालने के लिए

• किसी naam ke last letters निकालने के लिए
  Note: if I want to paste horizontally number then will use 'paste ke option me transpose

5. left  == left se digits extract krne ke liye will use this 
   =LEFT(E2,2)  
   E2 IS CELL AND 2 IS DIGITS THAT I WANT TO EXTRACT
6. Average
   =AVERAGE(A1:A5) starting cell to ending cell
7. If else
   =IF(G2 >$Q$3, "PASS", "FAIL")
8. Countif
   =COUNTIF(D:D,J5)
   Example: we have a list of students grade A b c d e and I want to count how many students has A marks and other marks so we can calculate by using this 
   
   
   
   
   
9. GPT
10. Vlookup
    =VLOOKUP(A2,Sheet3!A:B,2,0)
    VLOOKUP ka matlab hai —
    Vertical Lookup = kisi value ko vertically (upar se neeche) dhoondhna,
    aur uske corresponding column se data lana.
    

    2️⃣ Formula ke parts samjho:

    Part	Matlab	Example Explanation
    A2	Lookup value	Ye value hum Sheet4 ke student name se le rahe hain (e.g. “Divyanshu Khare”)
    Sheet3!A:B	Table range	Ye wo jagah hai jahan se data lena hai — Sheet3 ke column A (Name) aur B (City)
    2	Column number	Table ke 2nd column (City) se data laana hai
    0	Match type	0 ka matlab Exact Match — matlab name bilkul same ho tabhi result do

11. Xlookup
12. SUMIF
    =SUMIF(D:D,J5,C:C)
    EXAMPLE: EK SHEET ME STUDENTS KE GRADE HAI MAIN CHAHTA HU JITNE STUDENTS KO A GRADE MILA HAI YA B GRADE MILA HAI UN SABHI KA MARKS KA SUM NIKALNA HAI TO YAHA MAIN ISE USE KARUNGA. 
    
    
    
13. Coditional Formating
    जब कोई specific condition पूरी होती है (जैसे marks > 80), तो cell automatically highlight हो जाता है।
    
    
    
    
14. Sorting
    

    Sorting का मतलब होता है data को किसी क्रम (Order) में लगाना।
    जैसे –

• A → Z (Ascending Order)

• Z → A (Descending Order)
  

  🔹 2. Sorting के Types

  Type	Example
  Alphabetical Sorting	Names A → Z (e.g., Aarav, Divyanshu, Riya...)
  Numerical Sorting	Marks 50 → 100
  Date Sorting	Oldest → Newest
  Custom Sorting	City के बाद Marks (multi-level sorting)

🔹 3. Sorting लगाने का तरीका

Step-by-step:

1. Data select करो (heading सहित)

2. Go to Home → Sort & Filter → Sort A to Z / Sort Z to A

3. या फिर → Data Tab → Sort

4. “Add Level” से multiple columns पर sort कर सकते हो (जैसे पहले City फिर Marks)

1. Filter
   Filter से हम specific data दिखा सकते हैं और बाकी temporarily hide हो जाता है।
   जैसे: सिर्फ "Raipur" वाले students या Marks > 80।
   

   🔹 5. Filter लगाने का तरीका

   Steps:

   1. Data के header row को select करो

   2. Press Ctrl + Shift + L (Shortcut for Filter)

   3. या → Home → Sort & Filter → Filter

   4. अब हर header पर dropdown दिखेगा

   5. वहां से checkbox या number filter से condition लगाओ

2. Alignment 
3. Text Rotation
4. Wrapping 
   

   Text Wrapping लगाने के Steps:
   Step-by-step:

   1. उस cell या column को select करो

   2. Go to Home Tab → Alignment Group

   3. Click on Wrap Text

5. Charts
6. Borders
   

   Borders Apply Karne ke Steps:

   1. Cell select karo jisme border lagana hai.

   2. Go to Home tab → Font group → Border icon (▢).

   3. Wahan se desired option choose karo:

   • Bottom Border

   • Top Border

   • Left Border

   • Right Border

   • All Borders

   • Outside Borders

   • Thick Box Border

   • No Border

   4. 🪄 Keyboard Shortcut:

      👉 Alt + H + B
      Then choose:

      • A → All Borders

      • O → Outside Borders

      • T → Top Border

      • B → Bottom Border

      • L → Left Border

      • R → Right Border

      • N → No Border

7. Data Cleaning 

1. Data Cleaning ke Main Steps:

   1. Remove Duplicates (डुप्लीकेट हटाना)

• Select data → Data tab → Remove Duplicates

• Duplicate rows automatically delete ho jati hain.
  🧠 Shortcut: Alt + A + M

2. Trim Extra Spaces (फालतू स्पेस हटाना)

   • Extra space clean karne ke liye formula:
     

     =TRIM(A2)

   • Ye function starting, ending aur extra spaces ko hata deta hai.

3. Text to Columns (एक सेल में अलग-अलग डेटा को अलग करना)

   • Agar ek cell me multiple data (jaise Name, Age, City) likha hai,
     jaise → Rahul, 22, Delhi
     Use alag columns me baantne ke liye:

   • Data tab → Text to Columns → Delimited → Comma

   
   

4. Find and Replace (गलत शब्द बदलना)

   • Ctrl + H → Find what: “Mumbay”, Replace with: “Mumbai”

   • Misspellings ko correct karne ke liye best hai.

   
   

5. Change Text Case (Upper/Lower/Proper)

   • =UPPER(A2) → सब अक्षर बड़े

   • =LOWER(A2) → सब अक्षर छोटे

   • =PROPER(A2) → हर शब्द का पहला अक्षर बड़ा

   
   

6. Convert Text to Number/Date

   • Kabhi kabhi numbers text format me hote hain.
     Convert karne ke liye:

   • Warning icon → Convert to Number

   • Ya formula: =VALUE(A2)

7. Check for Inconsistency (असंगत डेटा ढूँढना)

   • Example: State name kahin “MP” likha, kahin “M.P.”

   • Use standard format me convert karo.

8. Find & Replace
   Already done
9. Spelling Check 
10. Importrange