MS Excel

What is MS Excel?

Microsoft Excel is a spreadsheet program developed by Microsoft. It is used to organize data in a table format (rows and columns), perform calculations, and analyze data. In simple terms, Excel functions as a digital ledger where you can input numbers and text, and use built-in formulas to complete complex calculations in seconds.

Basic Excel Terminology

Before working in Excel, it is important to understand these core terms:

• Workbook: When you open Excel, the entire new file that is created is called a Workbook.
• Worksheet (Sheet): A Workbook contains multiple Worksheets, much like a single notebook contains many individual pages.
• Row: The horizontal lines across the Excel sheet are called Rows. They are identified by numbers (1, 2, 3…).
• Column: The vertical lines running down the Excel sheet are called Columns. They are identified by letters (A, B, C…).
• Cell: The individual rectangular box where a Row and a Column intersect is called a Cell. Every cell has a unique reference name based on its column letter and row number (for example, A1 or B5).

Basic Excel Formulas

In Excel, every formula must always start with an Equal to (=) sign.

• Addition (SUM): Used to add a range of numbers together.
  • Example: =SUM(A1:A5) adds all the numbers located in cells A1 through A5.
• Average (AVERAGE): Used to calculate the average value of a selected group of numbers.
  • Example: =AVERAGE(B1:B10)
• Counting (COUNT): Used to count the total number of cells in a range that contain numbers.
  • Example: =COUNT(C1:C10)

Uses of MS Excel

• Managing data entry and maintaining large database records.
• Generating business bills, client invoices, and employee salary slips.
• Creating student results, mark sheets, and attendance trackers for educational institutes.
• Tracking daily expenses and managing corporate or personal budgets.
• Presenting complex data visually using charts, graphs, and pivot tables
• Advanced Excel Formulas aur Nested Functions
• IF + AND Function (Multiple Conditions Check)
  • Syntax: =IF(AND(condition1, condition2), value_if_true, value_if_false)
  • Description: Jab aapko ek sath multiple conditions check karni ho aur dono true hone par hi result chahiye ho.
  • Example: =IF(AND(A2>50, B2="Yes"), "Eligible", "Not Eligible")
• XLOOKUP with IFERROR (Safe Advanced Lookup)
  • Syntax: =IFERROR(XLOOKUP(lookup_value, lookup_array, return_array), "Not Found")
  • Description: Agar XLOOKUP ko value nahi milti, toh error dikhane ki jagah custom text return karta hai.
  • Example: =IFERROR(XLOOKUP(A2, A:A, C:C), "Data Missing")
• INDEX + MATCH + LARGE (Top Nth Value Lookup)
  • Syntax: =INDEX(return_range, MATCH(LARGE(score_range, n), score_range, 0))
  • Description: List mein se 1st, 2nd, ya 3rd sabse badi value ka corresponding naam ya detail nikalne ke liye.
  • Example: =INDEX(A:A, MATCH(LARGE(B:B, 1), B:B, 0))
• 2. Advanced Aggregation & Math
• SUMPRODUCT (Multi-Criteria Weighted Sum / Multiplication)
  • Syntax: =SUMPRODUCT(range1, range2)
  • Description: Multiple columns ko aapas mein multiply karke unka sum nikalta hai bina kisi Array formula ke (Jaise: Quantity × Price ka total).
  • Example: =SUMPRODUCT(B2:B10, C2:C10)
• AGGREGATE (Calculations ignoring Errors & Hidden Rows)