Skip to main content

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:

    PartMatlabExample Explanation
    A2Lookup valueYe value hum Sheet4 ke student name se le rahe hain (e.g. “Divyanshu Khare”)
    Sheet3!A:BTable rangeYe wo jagah hai jahan se data lena hai — Sheet3 ke column A (Name) aur B (City)
    2Column numberTable ke 2nd column (City) se data laana hai
    0Match type0 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

      TypeExample
      Alphabetical SortingNames A → Z (e.g., Aarav, Divyanshu, Riya...)
      Numerical SortingMarks 50 → 100
      Date SortingOldest → Newest
      Custom SortingCity के बाद 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 
  5. Charts
  6. Borders
  7. Data Cleaning 
  8. Find & Replace
  9. Spelling Check 
  10. Importrange 

Labels:

Comments

Post a Comment

Popular posts from this blog

Add CSS using external CSS

>>> U just need to create a another page and save it with the name style.css >>> and then go to link that style page with your html docs how to link your css with html page ? >>> You can find code below , it will help you to link your external page with your html docs <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Divyanshu Khare || MERN Developer</title> <meta description = "description" content="Divyanshu Khare's website"> <link rel="stylesheet" type="text/css" href="style.css">   <!----------link external css page ---------> </head> <body> </body> </html>
Post a Comment
Read more

Python

Indexing--  it will help u to fetch single character  string= "Python" string[2] # slicing process of fetching a sub-string from the given string  #sequence of charater we can fetch means more than 1 character string="Divyanshu" string[2:4+1]   #basically here number is index value #string[start_index:end+index+1] string = "Hunny"   #indexing agr positive hai to left se count hoga #right se negative me string[:]  #it will give u entire string #now If i want to any characterwise index like string is Hunny and I want un only #string[start_index:end_index+1:step] string[1:4:2] #reverse your string #string[:: -1] string="Baba hunny" string[:: -1] # to convert into lowecase string="New Divyanshu" new_string=string.lower()  #new_string becase we've to create another string print(new_string) s1={1,2,3,4,5}    s2={3,2,8,67,85} s3=s1.union(s2) s3   #isme add hota hai whole value lekin common value update nhi hongi #intersection - ...
Post a Comment
Read more

Python Final Lectures

 Q- how to Print Hello World print("Hello World") Variables in python ------- age = 30   #variable should be intutive so that we can learn any time print(age) Note: Shift+Enter is shortcut to run command 2) ' #' this is for writing the comment in python Rules for Variables--- Variable can not be start with any number like - 1age  Number can use in between and end with variable like - age1 age2 Special characters are not allowed expect _ (underscore) like - age_my Space not allowed in variable  Python is case sensitive  Way to define Variable --- age1,age2 = 30,25  age1 = 30 age2 = 25 age1=age2=30   #if 30 age for both variable   >> Data type the type of data is basically data type Integer = age1 to age3 is basically integer   , Integer is basically full number lets check = type(age1)  #it will give u print int float=  basically decimal values Interest =  30.24 type(Interest) #answer is float Message = ...
Post a Comment
Read more