Lahat ng kailangan mo, in one free platform.

🎯 Goal Seek (Reverse Math)

Excel finds the input value needed to get the output you want.

Example: What grade do I need on final
to get an overall 1.50?

1. Set up: Subjects with grades + final cell
2. Data → What-If Analysis → Goal Seek
3. Set cell: B10 (overall GPA)
   To value: 1.50
   By changing: B9 (final exam grade)
4. Excel solves it!

🔬 Solver (Optimization)

For optimization problems — find max/min subject to constraints.

Use for: budget allocation,
schedule optimization, etc.

Enable: File → Options → Add-ins
→ Solver → Go → check Solver → OK

Then: Data tab → Solver
- Set objective (maximize profit)
- By changing: variable cells
- Subject to constraints (≤, ≥, =)
- Solve

📈 Statistical Functions

=STDEV.S(range)    standard deviation (sample)
=VAR.S(range)      variance (sample)
=MEDIAN(range)     middle value
=MODE.SNGL(range)  most common value
=PERCENTILE(r,k)   k-th percentile
=QUARTILE(r,q)     quartile (q=1,2,3,4)
=CORREL(x,y)       correlation
=PEARSON(x,y)      Pearson r
=LINEST(y,x)       linear regression
=TTEST(x,y,t,t)    t-test
=CHISQ.TEST(a,e)   chi-square test

📉 Data Tables (What-If)

See multiple outcomes side-by-side without re-running formulas.

1. Build formula with one or two variable cells
2. Create row/column of test values
3. Data → What-If Analysis → Data Table
4. Set Row Input Cell + Column Input Cell
5. Excel fills in all scenarios

🐍 Excel + Python (New!)

Excel 365 now supports Python directly. Or use Pandas externally.

In a cell: =PY(
  df.groupby('category').sum()
)

Or externally with Pandas:
import pandas as pd
df = pd.read_excel('thesis-data.xlsx')
df.describe()
df.to_excel('summary.xlsx')

📡 Live External Data

Pull live data from web into Excel.

Data → From Web → paste URL

Example sources:
- Stock prices (Yahoo Finance)
- Currency rates
- PSA statistics tables
- ph-public-api.vercel.app endpoints
  (e.g. /scholarships, /weather/cebu-city)

Right-click table → Refresh
to update anytime.

🎨 Conditional Formatting Recipes

⭐ Heatmap for grades:
Home → Conditional → Color Scales
Red-Yellow-Green based on value

⭐ Highlight duplicates:
Home → Conditional → Highlight
→ Duplicate Values

⭐ Top/Bottom 10:
Home → Conditional → Top/Bottom Rules

⭐ Formula-based:
Use a formula → =$C2>3
(highlights failing rows)

👋 Hello World

Sub HelloWorld()
    MsgBox "Hello, Filipino students!"
End Sub

Press F5 to run, or assign to a button.

🔁 Loop Through Rows

Sub ProcessGrades()
    Dim i As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row

    For i = 2 To lastRow
        If Cells(i, "B").Value < 75 Then
            Cells(i, "C").Value = "FAIL"
            Cells(i, "C").Interior.Color = RGB(255, 100, 100)
        Else
            Cells(i, "C").Value = "PASS"
            Cells(i, "C").Interior.Color = RGB(100, 255, 100)
        End If
    Next i
End Sub

🔢 Convert Grades to Letter

Function LetterGrade(score As Double) As String
    Select Case score
        Case Is >= 90: LetterGrade = "A"
        Case Is >= 80: LetterGrade = "B"
        Case Is >= 75: LetterGrade = "C"
        Case Else: LetterGrade = "F"
    End Select
End Function

' Use in any cell: =LetterGrade(B2)

🗑️ Remove Duplicates

Sub RemoveDups()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Range("A1").CurrentRegion.RemoveDuplicates _
        Columns:=Array(1), Header:=xlYes
    MsgBox "Done!"
End Sub

📧 Send Email from Excel

Sub SendThesisToAdviser()
    Dim Outlook As Object
    Dim Mail As Object
    Set Outlook = CreateObject("Outlook.Application")
    Set Mail = Outlook.CreateItem(0)

    With Mail
        .To = "adviser@school.edu.ph"
        .Subject = "Thesis Draft - Chapter 3"
        .Body = "Hi Sir/Ma'am, attached is my latest draft."
        .Attachments.Add ActiveWorkbook.FullName
        .Display  ' or .Send to send right away
    End With
End Sub

📋 Copy Data to New Sheet

Sub CopyToSummary()
    Dim src As Worksheet, dst As Worksheet
    Set src = Sheets("Raw Data")
    Set dst = Sheets("Summary")

    dst.Cells.Clear
    src.Range("A1:F100").Copy Destination:=dst.Range("A1")

    dst.Activate
    MsgBox "Copied 100 rows"
End Sub

🎨 Auto-Format Headers

Sub FormatHeaders()
    With Rows(1)
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(0, 56, 168) ' PH Blue
        .HorizontalAlignment = xlCenter
        .RowHeight = 24
    End With

    Columns.AutoFit
End Sub

📊 Generate Random Survey Data

Sub GenSurvey()
    Dim i As Long, j As Long
    Cells(1, 1).Value = "Respondent"

    For j = 1 To 10
        Cells(1, j + 1).Value = "Q" & j
    Next j

    For i = 2 To 101
        Cells(i, 1).Value = i - 1
        For j = 1 To 10
            Cells(i, j + 1).Value = Int(Rnd() * 5) + 1
        Next j
    Next i
End Sub

⭐ Use for testing your analysis before real data comes in.

🔍 Find & Highlight

Sub HighlightFailing()
    Dim rng As Range, cell As Range
    Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

    For Each cell In rng
        If IsNumeric(cell.Value) And cell.Value > 3 Then
            cell.Interior.Color = RGB(255, 200, 200)
            cell.Font.Bold = True
        End If
    Next cell
End Sub

💾 Save with Timestamp

Sub SaveBackup()
    Dim fname As String
    fname = ThisWorkbook.Path & "\Backup_" & _
            Format(Now, "yyyymmdd_hhmmss") & ".xlsx"

    ActiveWorkbook.SaveCopyAs fname
    MsgBox "Backup saved: " & fname
End Sub

⭐ Run before risky changes — instant restore point.

🧮 Build Custom Function

Function PHTAX(salary As Double) As Double
    ' PH 2026 simplified tax brackets
    Select Case salary
        Case 0 To 250000: PHTAX = 0
        Case 250001 To 400000
            PHTAX = (salary - 250000) * 0.15
        Case 400001 To 800000
            PHTAX = 22500 + (salary - 400000) * 0.2
        Case Else
            PHTAX = 102500 + (salary - 800000) * 0.25
    End Select
End Function

' Use: =PHTAX(450000)

📥 Import CSV at Click

Sub ImportCSV()
    Dim filePath As Variant
    filePath = Application.GetOpenFilename( _
        "CSV Files (*.csv), *.csv")

    If filePath = False Then Exit Sub

    Workbooks.OpenText Filename:=filePath, _
        DataType:=xlDelimited, Comma:=True
End Sub

🔢 Sort Data

1. Select your data range (or click any cell inside)
2. Data tab → Sort
3. Choose column + Ascending/Descending
4. Check "My data has headers" if first row is title

⚡ Quick: Alt+A+S+S

🔎 Filter Data

1. Click any cell in your table
2. Ctrl+Shift+L to turn on filters
3. Click dropdown arrow on a column header
4. Pick values to show / hide

💡 To remove duplicates: Data → Remove Duplicates

📊 Create a Pivot Table

1. Click inside your data
2. Insert tab → PivotTable
3. Drag fields: Rows (categories), Values (numbers to sum/count)
4. Right-click numbers → Summarize Values By → choose Sum/Count/Average

⭐ Pivot tables are the #1 most powerful Excel feature.

📈 Make a Chart

1. Select data including headers
2. Alt+F1 for default chart, or Insert → Charts
3. Right-click chart → Chart Type to change
4. Bar = comparison · Line = trends · Pie = proportions (≤5 categories)

🎨 Conditional Formatting

1. Select cells
2. Home tab → Conditional Formatting
3. Pick: Color Scales, Data Bars, Highlight Cells, etc.

💡 Use to flag failing grades (red), high values (green), etc.

🔗 Freeze Panes

1. Click cell BELOW + RIGHT of what you want frozen
2. View tab → Freeze Panes → Freeze Panes
3. Now headers stay visible while you scroll

⚡ For just row 1: View → Freeze Top Row

🛡️ Data Validation

1. Select cells
2. Data tab → Data Validation
3. Allow: List → enter values (e.g. "Excellent,Good,Fair,Poor") → creates dropdown

💡 Use for survey-style sheets, grading rubrics.

📤 Export to PDF / CSV

1. File → Save As → PDF (for printing/sharing)
2. File → Save As → CSV (for importing to other systems)
3. Before PDF: Page Layout → Print Area → set the range

🎯 Exercises (free practice files)

• ExcelJet Exercises (50+ free)
• Excel Easy (with practice files)
• Chandoo Excel School (free lessons)
• GCFGlobal Excel Formulas tutorial
• Kaggle Excel (data analysis)

📜 Certifications (Free / Cheap)

• Microsoft Office Specialist (MOS) — Excel
• Macquarie Excel Specialization (audit free)
• LinkedIn Learning (1-month trial)
• Udemy Excel (often $9 sale)

🇵🇭 Practice Datasets (PH context)

• PSA Poverty Statistics
• PSA Labor Force Survey
• Data.gov.ph (open PH datasets)
• BSP Economic Statistics
• PH StudentKit API (cities, schools)