Why Every Full Stack Developer Should Learn Excel in 2026

# pip install openpyxl
from datetime import date
from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Order ID", "Date", "City", "PIN", "Amount (₹)"])
for cell in ws[1]:
    cell.font = Font(bold=True)
ws.append([1001, date(2026, 11, 5), "Noida", "201301", 1249.50])
ws["B2"].number_format = "DD-MMM-YYYY"
ws["E2"].number_format = "#,##0.00"
ws.freeze_panes = "A2"
wb.save("orders_export.xlsx")

import csv

rows = [["Order ID", "City", "Amount"], [1001, "नोएडा", 1249.5]]
# utf-8-sig adds a BOM so Excel detects UTF-8 and shows Hindi correctly
with open("orders.csv", "w", newline="", encoding="utf-8-sig") as f:
    csv.writer(f).writerows(rows)

Duplicate check:      =COUNTIF(A:A, A2) > 1
Match with other export: =XLOOKUP(A2, Other!A:A, Other!E:E, "MISSING")
Difference:           =IF(ISNUMBER(F2), E2 - F2, "n/a")
Date only from timestamp: =INT(B2)   (format as date)

Pivot to debug a wrong total
Rows:    City
Columns: Status
Values:  Sum of Amount, Count of Order ID
Filter:  Date = the disputed month
Compare: pivot total vs the app's report total
If different -> check status filter and date boundaries

Discount rule prototype (agree with business)
A: Order value   B: Discount %                          C: Final
499             =IF(A2>=2000,10,IF(A2>=1000,5,0))        =A2*(1-B2/100)
1000            (same formula)                           (same formula)
2500            (same formula)                           (same formula)
Expected: 499 -> 0%, 1000 -> 5%, 2500 -> 10%

def discount_percent(order_value: float) -> int:
    if order_value >= 2000:
        return 10
    if order_value >= 1000:
        return 5
    return 0

def test_discount_matches_agreed_sheet():
    assert discount_percent(499) == 0
    assert discount_percent(1000) == 5
    assert discount_percent(2500) == 10

Export feature checklist
[ ] UTF-8 handled (utf-8-sig for CSV)
[ ] Numbers written as numbers
[ ] IDs and PIN codes written as text
[ ] Dates as date cells with a clear format
[ ] Header row bold and frozen
[ ] Large exports streamed or paginated
[ ] Tested by opening in Excel, not just a text editor

Import template rules (Excel Data Validation + code)
Column A Order ID   whole number, required
Column B Date       date between 01-01-2025 and 31-12-2026
Column C City       list: Noida, Delhi, Ghaziabad
Column D Amount     decimal >= 0
Apply the same rules in the upload API and return row-level errors.

Debugging a "wrong total" ticket
1. Export the data behind the report
2. Pivot by key dimensions with the same filters
3. Compare with the app total
4. Check status filters, duplicates and date boundaries
5. Write a failing test, then fix the code