login_tests.xlsx → sheet 'cases'

case_id | username        | password      | expected | run
TC01    | standard_user   | secret_sauce  | success  | Y
TC02    | locked_out_user | secret_sauce  | locked   | Y
TC03    | standard_user   | wrong_pass    | error    | Y
TC04    | (blank)         | secret_sauce  | error    | N

'run' lets anyone switch a case on or off without touching code.

Useful formulas in test sheets
Count runnable cases:   =COUNTIF(E:E,"Y")
Find duplicates:        =COUNTIF(A:A,A2)>1
Flag missing data:      =IF(OR(B2="",D2=""),"CHECK","OK")
Look up expected value: =XLOOKUP(A2,Expected!A:A,Expected!B:B,"not found")
Add Data Validation (Data > Data Validation > List) for 'expected' and 'run'.

# pip install openpyxl pytest
import pytest
from openpyxl import load_workbook

def load_cases(path="login_tests.xlsx", sheet="cases"):
    ws = load_workbook(path, read_only=True)[sheet]
    rows = ws.iter_rows(values_only=True)
    headers = next(rows)
    cases = [dict(zip(headers, r)) for r in rows]
    return [c for c in cases if c["run"] == "Y"]

@pytest.mark.parametrize("case", load_cases(), ids=lambda c: c["case_id"])
def test_login(case):
    result = fake_login(case["username"], case["password"])  # replace with real UI/API call
    assert result == case["expected"]

from openpyxl import load_workbook
from datetime import datetime

wb = load_workbook("login_tests.xlsx")
ws = wb["cases"]
ws.cell(row=1, column=6, value="result")
ws.cell(row=1, column=7, value="run_at")
results = {"TC01": "PASS", "TC02": "PASS", "TC03": "FAIL"}
for row in range(2, ws.max_row + 1):
    cid = ws.cell(row=row, column=1).value
    if cid in results:
        ws.cell(row=row, column=6, value=results[cid])
        ws.cell(row=row, column=7, value=datetime.now().strftime("%Y-%m-%d %H:%M"))
wb.save("login_tests_results.xlsx")

import pandas as pd

df = pd.read_excel("regression_results.xlsx")   # columns: case_id, module, status
summary = pd.crosstab(df["module"], df["status"])
summary["pass_rate_%"] = (summary.get("PASS", 0) / summary.sum(axis=1) * 100).round(1)
print(summary)
summary.to_excel("regression_summary.xlsx")

Sprint Test Status Report (one sheet)
Top:    Total cases | Executed | Passed | Failed | Blocked | Pass rate
Middle: Pivot — module × status
Chart:  Failures per run (last 5 runs)
Bottom: Open P1/P2 defects with owner and due date
Notes:  Risks, environment issues, next steps

Columns for a test case sheet
case_id | module | title | preconditions | steps | test_data | expected | priority | automated (Y/N) | owner
Rules:
- One case per row
- Steps numbered inside one cell
- priority from a list: P1, P2, P3
- automated from a list: Y, N

Columns for a defect log
defect_id | found_in_case | module | summary | steps_to_reproduce | expected | actual | severity | priority | status | owner | found_on | fixed_on
Status list: New, Open, In Progress, Fixed, Retest, Closed, Reopened

Excel Skills for Testers
☐ Sort, filter and freeze panes
☐ Data Validation lists
☐ COUNTIF / COUNTIFS / SUMIFS
☐ XLOOKUP or INDEX-MATCH
☐ Conditional formatting
☐ Pivot tables and pivot charts
☐ Removing duplicates
☐ Reading/writing Excel with openpyxl or pandas