AI + Excel

Excel + AI for HR Teams: Hiring Trackers, Attrition Analysis, and Reporting

By Sagnik Bhattacharya 3 Mar 2026 12 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for Excel and AI for HR teams, with hiring and reporting visuals.

HR spreadsheets sit in an awkward place. They are operational enough to need speed, but sensitive enough that careless automation can hurt real people. The useful version of Excel and AI for HR is not "let the model choose candidates". It is a controlled workflow where Excel keeps the source data structured, formulas produce the numbers, and AI helps with draft summaries, cleanup suggestions, and reporting language that a human still reviews.

I teach Flutter and Excel with AI — explore my courses if you want structured learning.

This tutorial builds that workflow around three common HR jobs: a hiring tracker, an attrition analysis sheet, and a leadership reporting pack. It uses ordinary Excel Tables, COUNTIFS, SUMIFS, XLOOKUP, PivotTables, and prompts that keep judgement and sign-off with the HR team. For the wider spreadsheet learning path, start with the Excel Formulas Guide hub.

If your task is attendance tracking rather than hiring or attrition, read the attendance tracker tutorial first. If your task is sales pipeline reporting, the same pattern appears in Excel + AI for Sales Ops.

Prerequisites

Excel 365, Excel 2021, or Excel for the web. The core formulas also work in Excel 2019 if you use INDEX/MATCH instead of XLOOKUP.
A hiring export or employee list that you are allowed to analyse. If the data contains candidate names, salary, protected characteristics, health data, or disciplinary notes, follow your organisation's HR and data-protection process before using any AI tool.
An AI assistant such as Copilot in Excel, ChatGPT, Claude, or a local model. If you use Copilot in Excel, your workbook needs to be in a supported cloud location and formatted as a Table or supported range.
One rule before starting: use AI for preparation and drafting, not for final employment decisions.

Step 1 — Build a hiring tracker that AI can safely read

Start with structure, not prompts. AI performs badly when the tracker is a loose grid of notes, copied email snippets, and inconsistent stage names. Create an Excel Table named tblHiring with one row per candidate-role application.

Column	Example	Why it matters
Candidate ID	C-1042	Lets you analyse without exposing names in prompts.
Role	Data Analyst	Enables funnel reporting by opening.
Source	Referral	Shows which channels produce interview-ready candidates.
Stage	Interview 1	The core funnel field; should be a dropdown.
Status	Active	Separates live applications from rejected, withdrawn, or hired.
Applied Date	2026-04-01	Feeds age and time-to-offer calculations.
Offer Date	2026-04-18	Feeds time-to-offer and acceptance reporting.
Start Date	2026-05-06	Lets you report accepted hires and joining slippage.
Reviewed By	Priya	Makes human ownership visible.

Use Data Validation for Stage and Status. Put the stage list on a small reference sheet:

Applied
Screen
Interview 1
Interview 2
Offer
Hired
Rejected
Withdrawn

Then select the Stage column, choose Data > Data Validation > List, and point the source to that range. This avoids stage drift such as "Int 1", "Interview One", and "Interview-1" becoming three separate funnel buckets.

Beginner pitfall. Do not paste interview feedback, disability accommodation notes, medical notes, or protected-characteristic fields into a general AI prompt. For most HR analysis, candidate IDs and aggregated counts are enough.

Step 2 — Calculate the hiring funnel with formulas

Once the tracker is a Table, the funnel is straightforward. Create a summary sheet with one row per stage and these formulas.

Candidate count by stage

=COUNTIFS(tblHiring[Stage], A2, tblHiring[Status], "Active")

where A2 contains the stage name, such as Screen or Interview 1.

Count by role and stage

=COUNTIFS(tblHiring[Role], $B$1, tblHiring[Stage], A2)

Use this when a hiring manager asks why one opening feels stuck while another is moving normally.

Time to offer

Add a helper column in tblHiring:

=IF([@[Offer Date]]="", "", [@[Offer Date]]-[@[Applied Date]])

Then calculate average time to offer:

=AVERAGEIFS(tblHiring[Time to Offer], tblHiring[Role], "Data Analyst", tblHiring[Time to Offer], ">0")

Offer acceptance rate

=COUNTIFS(tblHiring[Status],"Hired") / COUNTIFS(tblHiring[Stage],"Offer")

Format as a percentage. If your team keeps "Offer Accepted" as a separate status, use that instead of Hired.

AI assist. Paste only the aggregated funnel table into your assistant, not raw candidate data:

We are reviewing a hiring funnel. The table below has Stage, Active Count,
Average Days in Stage, and Conversion Rate. Draft a neutral summary for HR
review. Do not recommend rejecting or advancing candidates. Only describe
process bottlenecks and questions the recruiting team should investigate.

This gives you a first-pass narrative without letting the model make candidate-level judgements.

Step 3 — Build an attrition analysis sheet

Attrition analysis works best from a separate employee table. Create tblEmployees with one row per employee and these fields: Employee ID, Department, Role Family, Start Date, Exit Date, Exit Reason, Employment Type, Location, and Manager Group. Use IDs or pseudonyms when possible.

Active headcount at month end

On a monthly summary sheet, put the first day of each month in column A. For month in A2, count active headcount at month end:

=COUNTIFS(tblEmployees[Start Date],"<="&EOMONTH(A2,0),tblEmployees[Exit Date],"")
+COUNTIFS(tblEmployees[Start Date],"<="&EOMONTH(A2,0),tblEmployees[Exit Date],">"&EOMONTH(A2,0))

Leavers during the month

=COUNTIFS(tblEmployees[Exit Date],">="&A2,tblEmployees[Exit Date],"<="&EOMONTH(A2,0))

Monthly attrition rate

If column B contains current month-end headcount and column C contains leavers:

=IFERROR(C2/AVERAGE(B1:B2),0)

For the first month in the summary, use the opening headcount manually or compare against the previous period from your HRIS export.

Tenure at exit

Add a helper column:

=IF([@[Exit Date]]="", "", DATEDIF([@[Start Date]],[@[Exit Date]],"m"))

Now you can review early attrition, such as exits within the first 90 days:

=COUNTIFS(tblEmployees[Exit Date],">="&A2,tblEmployees[Exit Date],"<="&EOMONTH(A2,0),tblEmployees[Tenure Months],"<=3")

AI assist. Give the model a monthly summary, not individual exit notes:

Summarise this attrition table for HR leadership. Use cautious language.
Separate facts from possible hypotheses. Do not infer causes from protected
characteristics or individual records. End with 3 questions HR should validate
before taking action.

Step 4 — Use AI for safe HR reporting drafts

The safest AI use in HR reporting is narrative support over already-reviewed numbers. The model can help you find wording, compare periods, and draft slide notes. It should not decide who gets interviewed, who is "high risk", or who should be managed out.

Workflow	Good AI use	Avoid
Hiring funnel	Summarise stage bottlenecks from aggregated counts.	Ranking candidates or recommending rejection.
Attrition analysis	Draft cautious commentary from monthly trends.	Inferring why named employees left from thin notes.
Engagement survey	Cluster anonymised comment themes.	Identifying individual employees from comments.
Headcount pack	Explain changes by department and month.	Using private salary or performance data in an unapproved tool.

Use a prompt pattern like this:

You are assisting an HR analyst. Use only the table below.
Write a draft summary for internal review.
Do not make employment recommendations.
Do not infer protected characteristics.
Separate observed facts, possible causes, and checks needed.
Keep the tone neutral and evidence-based.

That wording sounds strict because HR is not a casual analytics domain. Good prompts make the boundary clear before the model starts writing.

Step 5 — Create the dashboard view

Build the dashboard after the source Tables are stable. A simple HR dashboard usually needs four blocks:

Hiring funnel: active candidates by stage, role, and source.
Speed: average days from application to offer, and offer to start.
Attrition: monthly leavers, attrition rate, and early attrition.
Open questions: AI-drafted notes that still need HR validation.

Use PivotTables for stage counts and department-level attrition. Add slicers for Role, Department, Month, and Location. Keep the AI narrative in a separate text box or notes section labelled Draft AI-assisted commentary — reviewed by HR before sharing.

Worked example — quarterly HR review pack

A 180-person SaaS company keeps hiring data in an ATS and employee data in an HRIS, but the quarterly leadership pack is still built in Excel. The HR analyst exports two CSVs and runs this workflow.

Hiring tracker: 126 applications across 5 open roles. Data Validation catches 11 inconsistent stage names. The funnel shows the Data Analyst role has 42 candidates in Screen but only 4 in Interview 1.
Time-to-offer: Average application-to-offer time is 18 days overall, but 31 days for Engineering. AI drafts a neutral note: "Engineering roles are moving more slowly after initial screen; validate interviewer availability and technical assessment turnaround."
Attrition: Monthly attrition is stable at 1.1% to 1.4%, but early attrition rises to 5 exits in the first 90 days. The model suggests possible onboarding questions, not a conclusion.
Leadership summary: HR reviews every AI-drafted paragraph, removes two overconfident claims, and adds context from recruiter notes before sharing the pack.

The AI saved drafting time, but the workbook stayed formula-led and reviewable. That is the correct balance for HR work.

Common mistakes

Pasting raw candidate names, salary, health notes, or protected-characteristic fields into a general AI tool without approval.
Letting AI write confident causes for attrition when the data only shows correlation or timing.
Using free-text stage names instead of a dropdown list, which breaks funnel counts.
Mixing active employees and exited employees in one unlabelled status column.
Sending AI-drafted HR commentary to leadership without an accountable human reviewer.

Troubleshooting

Copilot cannot analyse the workbook. Save the file to OneDrive or SharePoint with AutoSave on, then format the data as an Excel Table or supported range. Local unsaved files and messy merged-cell grids often fail.
COUNTIFS returns 0 for a stage that exists. The stage text does not match exactly. Use a Data Validation list, or add =TRIM(PROPER([@Stage])) as a cleanup helper and count the helper column instead.
Attrition rate looks too high in a small department. Small denominators magnify single exits. Show both count and percentage, and avoid writing a strong narrative until HR checks whether transfers, fixed-term contracts, or data-entry timing explain the spike.
AI invents reasons people left. The prompt allowed speculation. Rewrite it to say "Use observed data only. Separate hypotheses from facts. Do not infer reasons unless Exit Reason is present in the table."
Dashboard numbers change after refresh. The new rows were pasted below, not inside the Excel Table. Select the Table, use Table Design > Resize Table, and confirm the PivotTable source points to the Table name, not a fixed range.

Frequently asked questions

What should HR teams use AI for in Excel?

Use AI for draft summaries, tracker cleanup suggestions, funnel commentary, attrition pattern review, and reporting narratives. Do not use a general AI chat as the final decision-maker for hiring, promotion, termination, performance, pay, or disciplinary decisions.

How should I structure a hiring tracker before using AI?

Use one Excel Table with stable candidate IDs, canonical stages, role, source, status, key dates, and review notes. Keep names and sensitive fields out of prompts unless your organisation has approved the tool and data handling route.

How do I calculate attrition rate in Excel?

Count leavers during the period, divide by average headcount for the same period, and format the result as a percentage. Use a separate employee table with start date, exit date, department, and exit reason so the formula can be reviewed.

Can AI screen candidates from my Excel tracker?

Treat candidate screening as a high-risk workflow. AI may help summarise role requirements or draft interview questions, but ranking, rejecting, or shortlisting candidates should follow your organisation's approved HR, legal, bias-review, and data-protection process.

Can Copilot in Excel analyse HR data directly?

Yes, if the workbook meets Copilot's requirements: the file must be saved in a supported cloud location, the data must be in a table or supported range, and your Microsoft 365 tenant must allow Copilot. Still review outputs before using them in HR reporting.