HW-02 | Homework Day 2 — HR Payroll Processor

Build a fully formula-driven payroll sheet using ARRAYFORMULA for every calculation column, a VLOOKUP lookup tool, and conditional formatting.

💼

What You're Building

A fully formula-driven payroll system for 12 employees where HR only updates Basic Salary and Days Present — every other column (HRA, Gross, PF, TDS, Net Pay, Grade, Status) calculates automatically using ARRAYFORMULA and nested IF. Plus a live VLOOKUP lookup tool.

🏗️

Design PrincipleSeparate INPUTS (columns you type into) from CALCULATED columns (columns driven by formulas). HR should only ever change columns D (Basic Salary) and E (Days Present). Everything else should update automatically.

🪜

Step-by-Step Guide

Build the Employee Master Table

Create tab Day2. Add 12 employees in rows 3–14. Input columns (type values): A: Emp ID, B: Name, C: Department, D: Basic Salary, E: Days Present. Leave columns F–L empty for now — these will all be formulas.

ARRAYFORMULA — HRA, Gross, PF (Columns F, G, H)

Each of these columns needs just ONE formula in row 3:

Col F — HRA = 40% of Basic

=ARRAYFORMULA(IF(D3:D="", "", D3:D * 0.4))

Col G — Gross = Basic + HRA

=ARRAYFORMULA(IF(D3:D="", "", D3:D + F3:F))

Col H — PF = 12% of Basic

=ARRAYFORMULA(IF(D3:D="", "", D3:D * 0.12))

💡

Why the IF(D3:D="","",...) wrapper?Without it, ARRAYFORMULA applies to all rows in the column — including empty ones — causing zeros to appear all the way down. The IF check suppresses output on empty rows.

TDS — Conditional Tax Deduction (Column I)

Apply 5% TDS only for employees whose Gross exceeds ₹30,000:

Col I — TDS

=ARRAYFORMULA(
  IF(G3:G="", "",
    IF(G3:G>30000, G3:G * 0.05, 0)))

Net Pay and Salary Grade (Columns J, K)

Col J — Net Pay

=ARRAYFORMULA(IF(G3:G="","",G3:G-H3:H-I3:I))

Col K — Salary Grade (A/B/C)

=ARRAYFORMULA(
  IF(J3:J="", "",
    IF(J3:J>40000, "A",
      IF(J3:J>25000, "B", "C"))))

Build the VLOOKUP Lookup Tool (N1:O3)

In column N, create a mini lookup tool. A manager types any Emp ID into O1 and instantly sees the employee's name and Net Pay:

N1: "Enter Emp ID →"    O1: E001  ← type any ID here
N2: "Employee Name"    O2: =VLOOKUP(O1, A3:J14, 2, FALSE)
N3: "Net Pay (₹)"     O3: =VLOOKUP(O1, A3:J14, 10, FALSE)

=VLOOKUP( O1, A3:J14, 10, FALSE )

O1 — The value to look up (the Emp ID)

A3:J14 — The full table to search (must include Emp ID in leftmost column)

10 — Return the value from the 10th column (Net Pay = column J)

FALSE — Exact match only (never use TRUE for ID lookups)

Payslip Status + Conditional Formatting (Column L)

Col L — Payslip Status

=ARRAYFORMULA(
  IF(E3:E="", "",
    IF(E3:E>=20, "Payslip Ready", "Hold — Attendance Low")))

Apply Conditional Formatting:
1. Select L3:L14 → Format → Conditional formatting
2. Rule 1: Custom formula =$L3="Payslip Ready" → Green fill (#DCF5E8)
3. Add Rule 2: Custom formula =$L3="Hold — Attendance Low" → Red fill (#FDECEA)