Excel Spreadsheets: Formulas, Functions & Data Analysis

Core Concepts of Excel Spreadsheets

1. What is a Spreadsheet?

A spreadsheet is an electronic grid (rows × columns) used to:

• Store structured data (text, numbers, dates).
• Perform calculations automatically.
• Generate reports/charts.
• Analyze trends (e.g., sales, expenses, inventory).

Why Excel?

• Automation: No manual recalculations (e.g., bank loan interest).
• Collaboration: Shared workbooks (e.g., NEPSE stock analysts).
• Visualization: Dashboards for decision-making (e.g., Daraz’s order status).

2. Excel Basics: Cells, Ranges, and References

Cell Addressing

• Columns: A, B, C… Z, AA, AB (26 × 26 = 676 columns).
• Rows: 1, 2, 3… 1,048,576.
• Range: A1:D10 (cells A1 to D10).

Example: In a Ncell employee salary sheet, B2 might store an employee’s salary, and C2 could calculate tax as =B2*0.13.

Relative vs. Absolute References

Worked Example: Calculate Net Salary for Ncell employees (Salary – Tax – Insurance). Assume:

• Salary in column B, Tax Rate in D1 (13%), Insurance in D2 (5%).
• Formula for Net Salary (E2):

  =B2 - (B2*$D$1) - (B2*$D$2)
  

  • Drag this down to apply to all rows.

stateDiagram-v2
    [*] --> CellSelected
    CellSelected --> FormulaEntered : Type "=B2*0.13"
    FormulaEntered --> Calculation : Press Enter
    Calculation --> ResultDisplayed : Shows 1300 (if B2=10000)
    ResultDisplayed --> [*]

3. Excel Formulas and Functions

A. Basic Formulas

Example: Calculate monthly loan EMI for a bank loan.

• Formula:

  =PMT(rate, nper, pv)
  

  • rate = Annual interest rate/12 (e.g., 5%/12).
  • nper = Total payments (e.g., 12*5 for 5 years).
  • pv = Loan amount (e.g., 500000).

B. Logical Functions

Worked Example: For XYZ Company (from past exams), calculate Income Tax based on salary:

• Tax Rules:
  • ≤10,000: 0%
  • 10,001–50,000: 10%

  • 50,000: 20%

• Formula for Tax (D2):

  =IF(B2<=10000, 0, IF(B2<=50000, B2*0.1, B2*0.2))
  

• Total (E2):

  =B2 + C2 + D2
  

• Net Salary (F2):

  =B2 - E2
  

sequenceDiagram
    participant User
    participant Excel
    User->>Excel: Types =IF(B2>50000, B2*0.2, B2*0.1)
    Excel-->>User: Displays 2000 (if B2=100000)
    User->>Excel: Drags formula down
    Excel-->>User: Auto-fills D3:D100

C. Lookup and Reference Functions

Worked Example: For HAT Company (past exam), find the Maximum Salary:

• Formula:

  =MAX(D2:D4)
  

• For Conditional Maximum (e.g., only for "Manager"):

  =MAXIFS(D2:D4, C2:C4, "Manager")
  

D. Text and Date Functions

Example: Calculate days until deadline for a Daraz order:

=B2-TODAY()

(Where B2 is the order date.)

4. Data Analysis Tools

A. Sorting and Filtering

• Sort: Data → Sort A to Z (e.g., sort Ncell employees by salary).
• Filter: Click dropdown arrow in header → select values (e.g., filter "Engineer" roles).

Example: Filter HAT Company data to show only employees with Bonus > 1000.

B. PivotTables

Convert rows/columns of data into a summary report. Steps:

1. Select data → Insert → PivotTable.
2. Drag fields to Rows, Values, or Columns.
3. Apply calculations (Sum, Average, Count).

Example: Summarize Daraz order data by region:

• Rows: Region (e.g., Kathmandu, Pokhara).
• Values: Sum of Orders.

C. Data Validation

Restrict cell input to specific values (e.g., dropdown lists). Example: Limit salary grades to "Low", "Medium", "High":

1. Select cell → Data → Data Validation.
2. Choose List → enter values: Low,Medium,High.

5. Charts and Graphs

Visualize data for trends/patterns. Common Chart Types:

Example: Create a bar chart for XYZ Company salaries:

1. Select data (A1:F3).
2. Insert → Bar Chart.
3. Customize titles/axes (e.g., "Salary Distribution").

6. Advanced Functions (Exam Focus)

Worked Example: For Places Production Achievement (past exam):

1. Achievement (%):

   =C2/B2
   

2. Grade:

   =IF(D2>=80, "A", IF(D2>=60, "B", "C"))
   

3. Average Achievement:

   =AVERAGE(D2:D4)
   

In the Real World

Excel is everywhere in Nepal and globally. Here’s how businesses and apps use it:

1. eSewa / Khalti (Digital Payments)

   • Idea Used: SUMIF and VLOOKUP.
   • How?
     • Transaction Logs: Sum daily payments per user (=SUMIF(UserID, "Ram", Amount)).
     • Fraud Detection: Lookup suspicious transactions (=VLOOKUP(TransactionID, Blacklist, 2)).

2. Daraz / Pathao (Order Management)

   • Idea Used: IF + COUNTIF + PivotTables.
   • How?
     • Order Status: =IF(DeliveryTime<TODAY(), "Late", "On Time").
     • Driver Efficiency: Count completed orders per driver (=COUNTIF(DriverID, "Raju")).
     • Sales Dashboard: PivotTable to show top-selling products by region.

3. NEPSE (Stock Market)

   • Idea Used: AVERAGEIF, DATEDIF, and Line Charts.
   • How?
     • Moving Averages: =AVERAGEIF(ClosePrice, "Last 30 Days") to smooth trends.
     • Volatility: =STDEV(PriceRange) to measure risk.
     • Trend Analysis: Line chart of closing prices over 1 year.

4. Ncell / NTC (Customer Data)

   • Idea Used: INDEX+MATCH and Data Validation.
   • How?
     • Billing: Calculate monthly charges (=IF(Usage>GB, "Overage Fee", "Standard")).
     • Customer Segmentation: Dropdown lists for "New/Retained" status.

5. Banks (Loan Processing)

   • Idea Used: PMT, DATEDIF, and IF for eligibility.
   • How?
     • EMI Calculation: =PMT(5%/12, 60, 500000) for a 5-year loan.
     • Early Repayment Penalty: =IF(DATEDIF(StartDate, Today, "M")<60, "Penalty", "None").

Exam Tip: How to Score Full Marks

1. Formula Accuracy:

   • Always use absolute references ($) for fixed values (e.g., tax rates).
   • Example: =B2*$D$1 (not =B2*D1).

2. Step-by-Step Logic:

   • Break complex problems into parts (e.g., first calculate tax, then total, then net salary).
   • Use comments (Ctrl+1) to explain steps in the exam.

3. Real-World Tie-Ins:

   • Relate formulas to scenarios like salary sheets (Ncell), order tracking (Daraz), or loan calculations (banks).
   • Example: For IF questions, say:

     "This is like how eSewa applies different transaction fees for domestic vs. international payments."

4. Chart Questions:

   • Describe the type of chart (e.g., "bar chart for comparing monthly sales").
   • Mention axes labels (e.g., "X-axis: Months, Y-axis: Revenue").

5. Common Pitfalls:

   • #DIV/0!: Check for empty cells or division by zero.
   • #NAME?: Ensure function names are spelled correctly (e.g., SUMIFS vs. SUMIF).
   • Relative vs. Absolute: Drag formulas carefully to avoid copying errors.

Pro Tip:

• Practice with past exam sheets (like the salary or production achievement questions).
• Use F4 to toggle between relative/absolute references quickly.
• Memorize shortcuts:
  • Ctrl+C / Ctrl+V: Copy/Paste.
  • Ctrl+Shift+L: Toggle filters.
  • Alt+E+S+V: Paste as Values (to remove formulas).