HR Analytics and AI: Analyse Staff Turnover From a Spreadsheet Export

Export only the dates and counts needed, remove names and identifying notes, calculate turnover and absence rates in Excel, then ask an approved AI tool to explain the aggregate figures. Keep the original employee file restricted. Check every AI observation against the spreadsheet before changing staffing policy; small groups can reveal people even without names.

Removing a name is not always enough. A row showing one departure in a two-person role, a precise date and a detailed absence reason can identify the worker. The safest first analysis often uses monthly counts by a sufficiently broad team, with the rate calculated in the spreadsheet and only the resulting table shared with AI.

Choose the question before exporting a row

Write down the decision you hope the analysis will inform: whether departures rose after a rota change, whether absence is concentrated in one season, or whether a recruiting plan should allow for replacement hires. A question about the business gives you a reason to exclude sensitive columns. A request to discover which employee is likely to leave is a different, much more intrusive project and should not be disguised as a turnover report.

For an owner-run bakery, the first question might be, “Did monthly departures change after we moved from a five-day to a six-day opening schedule?” The necessary information is the month, opening and closing headcount and number of leavers. Names, addresses, pay, performance notes and medical reasons add nothing to that calculation. For absence, decide whether you need total scheduled days and total days absent, rather than an individual-level list.

Define your period before you count. A rolling twelve months and a calendar year answer different questions. Record whether temporary workers, owners and contractors are included. If the export calls a transfer between teams a “leaver”, decide whether you mean departure from the company or from a team. Write that definition at the top of a private working sheet. Otherwise an AI-generated trend statement may be perfectly worded but built on shifting denominators.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Make a restricted source sheet and a safe analysis sheet

Download the HR or payroll export into a restricted folder that only the owner and authorised HR staff can access. Work on a copy. Delete fields the question does not require before you prepare anything for an AI tool: employee number, name, personal contact details, exact birth date, free-text case notes, diagnosis, disciplinary information and manager comments. Do not upload the source workbook merely because an assistant offers a convenient file button.

In Excel, create a second sheet with one row per month and columns such as Month, Opening headcount, Closing headcount, Leavers, Scheduled working days and Absent days. Produce that sheet from the restricted source while it remains on your machine. Check a sample of source rows against the month totals and retain a note of the export date and any exclusions. If your team is very small, share only the annual total with the assistant and do the finer inspection privately.

Consider re-identification before calling the copy anonymous. If a five-person workshop had one long absence that everyone knows about, even an “anonymous” monthly absence spike points to that person. Combine months, merge tiny teams, or omit the absence breakdown altogether. Pseudonyms such as Employee A are still linked to people if you retain a mapping or other details make a match possible. A summary with counts is easier to defend than a row-level dataset whose identifiers have merely been replaced.

Employment records can be subject to the UK GDPR or EU GDPR where those regimes apply. The regulator's employment records guidance stresses a clear purpose, lawful and fair use, transparency and access limited to those who need the records. Your adviser should check the lawful basis, privacy notice, retention and any special-category absence details before a new analysis process is adopted. The regulator marks this guidance as under review after changes made by the Data (Use and Access) Act, so check for a newer version before relying on it. This practical workflow does not settle those legal questions.

Calculate the rate yourself before requesting an explanation

For a simple period turnover rate, divide leavers during the period by average headcount, then multiply by 100. A practical average for a small firm is opening headcount plus closing headcount, divided by two. Document that choice because some businesses use monthly average headcount or a different definition of voluntary turnover. Absence rate can be absent working days divided by scheduled working days, multiplied by 100. These are descriptive ratios, not a diagnosis of why somebody left or was absent.

Illustrative worked example: a bicycle repair shop starts a twelve-month period with 24 employees, ends with 28 and has 5 leavers. Average headcount is (24 + 28) / 2 = 26. The turnover rate is 5 / 26 × 100 = 19.2% after rounding. Across the same period, staff were scheduled for 5,200 working days and recorded 156 days absent. The absence rate is 156 / 5,200 × 100 = 3.0%. Do not combine the two percentages or call the difference an effect of absence on turnover.

The owner can compare the previous period using exactly the same definitions. If it had 3 leavers and an average headcount of 25, the earlier turnover rate was 12.0%. The change is 7.2 percentage points, not “a 7.2% increase”. A percentage increase would compare 19.2 with 12.0, yielding about 60%, a very different phrase. With only five leavers, one additional departure can move the rate substantially. State the numerator next to the rate so readers understand that sensitivity.

In the workbook, use straightforward formulas and inspect their referenced cells. For example, a row with opening count in B2, closing count in C2 and leavers in D2 can use =D2/AVERAGE(B2,C2), with the result displayed as a percentage. Check that neither headcount cell is blank and that the leaver count is not already included as a percentage. For absence, use =F2/E2 if E2 is scheduled days and F2 is absent days. A zero scheduled-days denominator needs an explicit blank or “not applicable”, never a forced zero rate.

Give AI totals, definitions and a narrow job

ChatGPT or Claude can help explain a small table, draft questions for the owner, and identify missing definitions. Neither needs the original export to do that work. An approved business workspace may have different default training treatment from a personal workspace. OpenAI states that it does not use ChatGPT Business workspace content to train its models by default. Anthropic says the same for its commercial products, including Claude for Work, with one exception: a conversation someone submits as thumbs-up or thumbs-down feedback may be kept and used for training, so tell reviewers not to rate staff-data chats, or ask a Team or Enterprise owner to switch off the Rate chats setting. Those statements do not replace the need to check your own contract, access permissions, sharing and retention.

Paste an aggregate table and tell the assistant precisely what it must not infer. The prompt should request arithmetic checks and possible follow-up questions, not predictions about individuals. It should also ask the model to distinguish a calculation from a hypothesis. That matters because a fluent explanation can conceal a mistaken comparison or an unsupported story about staff morale.

Use only this illustrative aggregate table. Do not infer any individual's identity, health or reason for leaving. Check the turnover and absence arithmetic, describe the change in plain English, and list three questions to investigate with a person. State which conclusions the numbers do not support.
Period A: opening staff 25, closing staff 25, leavers 3, scheduled days 5,000, absent days 125.
Period B: opening staff 24, closing staff 28, leavers 5, scheduled days 5,200, absent days 156.
Definitions: leavers means company departures; absence counts scheduled working days missed; both periods cover twelve months.

An illustrative output might say, “Turnover rose from 12.0% to 19.2%; absence rose from 2.5% to 3.0%. Investigate whether the rota, recruitment mix or reporting changed.” That is a useful draft. If it says “absence caused the extra departures”, remove the claim: these totals cannot establish cause, and they do not show whether absent workers were among the leavers. You may ask the assistant to cite the exact cells behind each number, then confirm them yourself in Excel.

Six small tests before trusting the trend

First, check the dates. In an illustrative café export, one departure dated on the last day of the previous period was included in both years because two filters used different end conditions. Re-run the count with one inclusive start and one exclusive end. Compare the total leavers in the source system with the sheet, then record the filter rule. AI cannot discover a hidden duplicated row from an aggregate table.

Second, distinguish people from events. A seasonal employee who left, returned and left again may appear as two departure events. If the question is “how many departures occurred?”, that may be right. If it is “how many distinct people left?”, it is wrong. The repair shop could count 5 events but only 4 distinct individuals. Label the numerator and make the same choice in every comparison period.

Third, separate voluntary from all departures. If the five illustrative leavers include two redundancies, a “voluntary attrition” interpretation is misleading. Keep the published aggregate broad unless category counts are sufficiently large to avoid identifying anyone. In a tiny team, describing the split in prose may disclose a case; ask an authorised person to review the detail privately instead.

Fourth, check changed schedules. Suppose the bakery's scheduled working days rise from 4,000 to 4,800 after extra opening hours, while absent days rise from 120 to 132. Raw absence days rose by 12, but the rate fell from 3.0% to 2.75%. An assistant looking only at the raw count could recommend an unnecessary intervention. Give it the denominator and require both values in its explanation.

Fifth, inspect missing or coded values. An export may use a blank, zero, “unpaid leave” and “sickness” for different things. Decide which belong in your absence definition before summing. For example, 18 days of approved holiday should not quietly be added to 156 days of unplanned absence simply because both are tagged “not at work”. Keep a small private mapping of source codes to inclusion rules.

Sixth, test the effect of one person. In a ten-person HVAC contractor, one leaver represents roughly ten percentage points if average headcount is ten. Instead of announcing a major trend from one month, show quarterly or annual counts and explain the small base. An owner can still investigate a departure, but should do so through a proper conversation and records, not an AI guess about an identifiable employee.

Use the explanation to frame questions, not people scores

After arithmetic checks, ask what evidence would confirm or weaken a suspected pattern. If departures cluster after a shift change, review exit feedback where it can be used fairly, compare the rota with actual hours and ask managers whether the schedule changed at the same time as pay or role mix. If absence rises during a peak trading month, examine workload and reporting consistency. The aggregate table is a starting signal; the evidence needed for an employment decision sits elsewhere.

A practical monthly note might read: “Five departures over twelve months; 19.2% of average headcount 26, compared with three and 12.0% previously. Three months account for much of the movement. The owner will verify the leaving dates, review staffing changes and ask an adviser whether any policy change is appropriate.” This is more useful than “AI detects a retention crisis”. It states what was calculated, what remains uncertain and what happens next.

Keep a versioned private workbook and a short decision log. Record who prepared the summary, the export date, formulas, excluded fields, the AI tool and plan used, who reviewed the output, and any correction. Delete temporary uploads and pasted extracts according to your retention policy; check the tool's controls rather than assuming that closing a chat deletes its contents. If the result will be shared with managers, share the aggregate note, not the restricted source file.

For a cleaner starting workbook, clean inconsistent spreadsheet fields before counting. If you need wider spreadsheet workflows, the ChatGPT and Excel guide covers general analysis; the task here is the more restrictive staff-data hand-off. A consultant reviewing the wider people process can use the HR audit checklist, while an owner deciding whether recurring questions deserve automation can read the staff HR helpdesk tutorial.

Keep a reproducible monthly review

Set one owner for the calculation and one reviewer if the business can spare them. Each month, confirm opening and closing counts against payroll or the HR system, reconcile leavers, check absence-code mapping, and calculate the ratios with locked formulas. Mark any definition change in the sheet rather than silently overwriting an old number. Then share only a coarse summary with the approved AI workspace, read its arithmetic aloud against the cells and remove claims that suggest an individual cause.

When a manager asks for a team breakdown, calculate the likely disclosure risk before opening another sheet. Imagine a bakery with 18 staff in production and two in delivery. A table reporting one delivery departure and its month adds little decision value, because colleagues may know exactly who left. A combined company figure protects the person better. If the manager needs to understand the delivery role, hold a private discussion about workload and rota design; do not route the two workers' histories through a general assistant.

Likewise, a free-text “reason for leaving” column is not ready for a simple word cloud. One comment might refer to illness, a grievance or a named colleague. If qualitative themes matter, have an authorised HR professional remove personal details, group comments into broad categories and suppress categories with very few responses. A count such as “three comments mention scheduling” should still be checked against the original wording: one comment may say the schedule improved while another says it became worse. AI can suggest a coding scheme, but a person should approve each code and any quotation before publication.

Ask the assistant to produce a compact exception list as well as its narrative: missing denominator, inconsistent period, unexpected headcount jump, unmatched exit code and small-group warning. A model may return “no exceptions” because the aggregate data looks tidy. Test it with an intentionally flawed copy: change one month to a zero closing count or set absent days above scheduled days. If it does not flag that impossible condition, do not use its prose as a quality gate. Keep formula checks in the workbook because deterministic checks are easier to audit.

Illustratively, the bicycle repair shop might hire eight people during the year and lose five. Opening staff 24 plus eight hires minus five leavers equals 27, yet the closing export says 28. The discrepancy of one must be resolved before the 19.2% rate is used in a board note. It could be a returner, a transfer, a missing hire or a wrong closing date. AI can list those possibilities, but only the restricted HR record can establish which one occurred. Add a reconciliation column rather than hiding the difference in a rounded percentage.

A quarter with no leavers also needs careful wording. “Zero turnover” describes that period's count; it does not prove retention improved or guarantee the next quarter. If the owner wants a forecast, ask what planning decision it would change and what history is available. A single year in a small firm rarely supports a reliable predictive model. Budgeting one replacement hire may still be sensible as an operational scenario, explicitly labelled as a planning assumption rather than an AI prediction about staff.

The final report should carry a privacy boundary in plain language: “Prepared from monthly aggregates; no identifiable employee records were provided to the assistant; small-team detail withheld.” Check that statement against what was actually pasted or uploaded. If the assistant was given a workbook containing hidden tabs, comments or named file metadata, the statement would be false. Create a fresh values-only file for the shareable totals, inspect every tab and the filename, then have the reviewer confirm it contains no personal records.

Success is a repeatable report that a second person can reproduce from the source without needing the chat history. It should show counts beside rates, explain at least one possible data-quality issue, avoid identifying anyone, and leave any action involving a worker to a person with proper context. If the team is too small for a safe breakdown, a company-wide number plus a confidential human review is a better result than a detailed AI chart.

Further reads

• AI Survey Analysis for HR Consultants: Coding Employee Comments Safely — Analyse employee comments without exposing individuals.
• Free AI for HR Professionals: Are Free Plans Safe With Staff Data? — Check the risks of free plans with staff data.
• AI for HR in Small Businesses: 12 Tasks You Can Hand Over Safely — Place this calculation among other practical HR tasks.
• HR Automation AI Tools: What a Small Business Can Automate Safely — Decide which follow-up work can be automated.
• ChatGPT for HR Professionals: Job Ads, Reviews and Warning Letters — Keep people-facing drafts under human review.
• AI Salary Benchmarking for HR Consultants: Sources, Bands and Checks — A repeatable method for turning survey and job-advert data into pay bands with AI, plus the checks that catch invented figures.
• AI Staffing Tools for Employee Engagement: Keep Temps on Assignment — A check-in cadence for temp assignments: opt-in texts, AI reply sorting into fine, call and urgent, and a same-day route to a consultant.
• HR Consultant Proposal Template: Scope, Fees and Exclusions With AI — Copyable scope and fee clauses, plus a filled proposal for a 25-person client and a scope-change check.
• AI Tools for Dairy Farming: Analyse Milk and DHI Records in ChatGPT — Load DHI test-day and bulk tank records into ChatGPT, get a ranked list of cows and trends to check, and learn what your vet must confirm.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Information Commissioner's Office, Collecting and keeping employment records; OpenAI Help Center, Data controls in ChatGPT (managed workspaces); Anthropic Privacy Center, Is my data used for model training? (commercial products).