Excel with AI for HR Teams: Attendance, Payroll, Employee Dashboard & Recruitment

Run everyday HR operations in Excel with Microsoft Copilot and ChatGPT — track attendance, calculate payroll, build a staff dashboard, and manage recruitment pipelines without manual wrangling.

Table of Contents

• 1. Attendance
• 2. Payroll
• 3. Employee Dashboard
• 4. Recruitment

How to Use AI in This Tutorial

Every task below follows the same two-step AI pattern:

1. Microsoft Copilot (inside Excel) — open the Copilot pane from the ribbon (or press Alt + the Copilot key). Type your request in plain English; Copilot builds the table, pivot, or chart for you.
2. ChatGPT (alongside Excel) — when you need an exact formula or logic, paste your column layout into ChatGPT and ask for the precise Excel formula, then paste it back into the cell.

Use Copilot to build and explore, ChatGPT to get the exact formula. Both are shown in every section.

1. Attendance

Attendance tracking records who was present, late, or absent on each day. Excel turns those marks into absence rates and trend lines.

Excel setup: one row per employee per day, with a Status column (Present / Late / Absent).

Count absences for an employee (column C = Status):

=COUNTIF(C:C, "Absent")

Absence rate:

=COUNTIF(C:C, "Absent") / COUNTA(C:C)

Copilot prompt:

"From this attendance sheet, calculate each employee's attendance percentage for the month and list anyone below 90%."

ChatGPT prompt:

"Write an Excel formula that counts how many times an employee was Late but not Absent."

=COUNTIFS(C:C, "Late", C:C, "<>Absent")

Simple explanation: COUNTIFS applies multiple conditions at once — here it counts "Late" entries that are not "Absent", giving a fair view of punctuality.

Common mistake: Using free-text like "was late" instead of a fixed list. Standardize status words first; ask Copilot to clean the Status column.

2. Payroll

Payroll calculates what each employee is paid after hours, tax, and deductions. The core math is gross pay, then net pay.

Gross pay (Hours × Rate):

=Hours * Rate

With Hours in B2 and Rate in C2:

=B2 * C2

Net pay (after a flat tax rate in a cell, e.g. 20%):

=B2*C2 * (1 - 0.2)

Copilot prompt:

"Calculate gross and net pay for all employees using their hours and rate, apply 20% tax, and total the company payroll cost."

ChatGPT prompt:

"Give me an Excel formula for overtime: pay 1.5× the rate for hours above 40."

=MIN(B2,40)*C2 + MAX(0,B2-40)*C2*1.5

Simple explanation: The first part pays up to 40 hours at the normal rate; the second pays only the overflow at 1.5×. MAX(0,...) prevents negative overtime when hours are under 40.

Best practice: Store the tax rate and overtime threshold in named cells so Copilot and you can update policy in one place.

3. Employee Dashboard

An employee dashboard is a single view of headcount, department splits, and key HR metrics, usually built from a pivot table plus a chart.

Headcount by department (column B = Department):

=COUNTIF(B:B, "Sales")

Average tenure (column C = Tenure in months):

=AVERAGE(C:C)

Copilot prompt:

"Create a dashboard from this employee list showing headcount by department as a chart and average tenure as a card."

ChatGPT prompt:

"Suggest three KPI cards I should put on an HR dashboard built from an employee spreadsheet."

Common mistake: Putting raw names on a shared dashboard. Keep dashboards aggregated (counts, averages) and store the source list separately.

4. Recruitment

Recruitment tracking follows candidates through stages — Applied, Interview, Offer, Hired — so you can see pipeline health and time-to-hire.

Count candidates in each stage (column D = Stage):

=COUNTIF(D:D, "Interview")

Conversion rate (Hired ÷ Applied):

=COUNTIF(D:D, "Hired") / COUNTIF(D:D, "Applied")

Copilot prompt:

"From this candidate sheet, show the number of candidates in each recruitment stage as a funnel and calculate the overall hire rate."

ChatGPT prompt:

"Write an Excel formula that calculates average days from Applied to Hired using two date columns."

=AVERAGEIFS(HireDateColumn, StageColumn, "Hired") - AVERAGEIFS(ApplyDateColumn, StageColumn, "Hired")

Simple explanation: AVERAGEIFS averages the hire dates and apply dates only for rows marked "Hired", then subtracts to get the typical time-to-hire.