HR reporting is the silent killer of productivity. Every month, teams across the globe drown in spreadsheets, manual data pulling, and the dreaded 'can you just export this?' requests. A recent survey by Deloitte found that HR professionals spend up to 40% of their time on administrative tasks, with reporting being a significant chunk. But what if you could cut that time by 75%? This isn't a fantasy—it's what happens when you combine the raw power of Google Sheets and SQL with the precision of AI-generated prompts.
In this guide, I'll share 10 battle-tested prompts that have helped me and my clients automate everything from headcount tracking to complex attrition analysis. These aren't generic 'write me a formula' prompts—they're engineered to produce production-ready solutions. Whether you're an HR analyst drowning in data or a manager who just wants Friday afternoons back, these prompts will transform your workflow.
1. The Headcount Snapshot (Google Sheets)
The Problem: You need a real-time headcount dashboard that updates as you add rows to a master sheet.
The Prompt:
"Act as an expert Google Sheets formula engineer. I have a sheet named 'Employees' with columns: A (Date), B (Name), C (Department), D (Status - 'Active' or 'Terminated'). Create a single array formula in cell F1 that calculates the current headcount (Active employees) for each department, with columns: Department, Headcount. Use QUERY and ARRAYFORMULA. Also, create a second formula that shows the monthly headcount trend for the last 6 months (columns: Month, Headcount). Assume today is August 22, 2026. Provide the formulas and a brief explanation."
Example Result:
=QUERY(Employees!A:D, "select C, count(A) where D = 'Active' group by C label count(A) 'Headcount'", 1)
=QUERY(Employees!A:D, "select year(A), month(A), count(A) where D = 'Active' group by year(A), month(A) order by year(A), month(A) desc limit 6 label count(A) 'Headcount'", 1)
This gives you a live snapshot and a trend line, all without manual filtering.
2. Automating Turnover Rate (SQL)
The Problem: Calculating quarterly turnover rate requires joining employee and termination data.
The Prompt:
"You are a data analyst. Given two tables: 'employees' (id, name, department, hire_date, termination_date) and 'departments' (id, name), write a PostgreSQL query that calculates the turnover rate for each department for Q2 2026 (April 1 – June 30). Turnover rate = (terminations during period) / (average headcount during period) * 100. Average headcount = (headcount at start + headcount at end) / 2. Include department name, terminations, avg_headcount, turnover_rate. Use CTEs and window functions for clarity."
Example Result:
WITH terminations AS (
SELECT d.name AS dept, COUNT(*) AS term_count
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.termination_date BETWEEN '2026-04-01' AND '2026-06-30'
GROUP BY d.name
),
headcount_start AS (
SELECT d.name AS dept, COUNT(*) AS count
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.hire_date < '2026-04-01' AND (e.termination_date IS NULL OR e.termination_date >= '2026-04-01')
GROUP BY d.name
),
headcount_end AS (
SELECT d.name AS dept, COUNT(*) AS count
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.hire_date < '2026-07-01' AND (e.termination_date IS NULL OR e.termination_date >= '2026-07-01')
GROUP BY d.name
)
SELECT t.dept, t.term_count, (hs.count + he.count)/2.0 AS avg_headcount,
ROUND((t.term_count / ((hs.count + he.count)/2.0)) * 100, 2) AS turnover_rate
FROM terminations t
JOIN headcount_start hs ON t.dept = hs.dept
JOIN headcount_end he ON t.dept = he.dept
ORDER BY turnover_rate DESC;
This query is production-ready and accounts for edge cases like employees hired and terminated within the period.
3. Clean Data Imports (Google Sheets)
The Problem: You have a messy CSV export from your ATS that needs cleaning before analysis.
The Prompt:
"You are a Google Sheets data cleaning expert. I have a column A with names in the format 'Last, First' and column B with email addresses that may have leading/trailing spaces and inconsistent capitalization. Write a formula to: 1) Split names into two columns (First, Last) using SPLIT and TRIM. 2) Normalize emails to lowercase using LOWER and TRIM. 3) Highlight duplicate emails using conditional formatting. Provide the formulas and the conditional formatting rule."
Example Result:
=SPLIT(A1, ", ")
=LOWER(TRIM(B1))
Conditional formatting: Apply to range B:B, custom formula =COUNTIF($B:$B,$B1)>1, set fill color to yellow.
4. Pivot Table Magic (Google Sheets)
The Problem: You need a pivot table to analyze salary distribution by department and gender, but the built-in pivot editor is clunky.
The Prompt:
"You are a Google Sheets expert. Given a sheet 'Salaries' with columns: Department, Gender, Salary. Write a QUERY formula that replicates a pivot table showing average salary by department and gender, with departments as rows and genders as columns. The result should have a header row with 'Department', 'Female', 'Male', 'Other'. Assume genders are 'F', 'M', 'O'. Use PIVOT in QUERY."
Example Result:
=QUERY(Salaries!A:C, "select A, avg(C) where C is not null group by A pivot B", 1)
This gives you a clean matrix, ready for charting.
5. Sentiment Analysis on Exit Interviews (SQL)
The Problem: You have exit interview comments in a SQL database and want to categorize sentiment to spot trends.
The Prompt:
"You are an SQL expert. Table 'exit_interviews' has columns: id, employee_id, comment (TEXT). Using only vanilla PostgreSQL (no external extensions), write a query that categorizes each comment as 'Positive', 'Negative', or 'Neutral' based on the presence of keywords. Include a column 'sentiment_score' (1, -1, 0). Use CASE and a list of keywords: positive (great, good, excellent, supportive), negative (bad, poor, toxic, stressful). Also, show the count of each sentiment per department (joining with employees table)."
Example Result:
WITH sentiment AS (
SELECT e.department_id,
CASE
WHEN comment ~* 'great
|good|excellent|supportive' THEN 'Positive'
WHEN comment ~* 'bad
|poor|toxic|stressful' THEN 'Negative'
ELSE 'Neutral'
END AS sentiment
FROM exit_interviews ei
JOIN employees e ON ei.employee_id = e.id
)
SELECT d.name, sentiment, COUNT(*) AS count
FROM sentiment s
JOIN departments d ON s.department_id = d.id
GROUP BY d.name, sentiment
ORDER BY d.name, count DESC;
This is a simple but effective way to gauge employee sentiment without complex NLP.
6. Dynamic Dashboard with Sparklines (Google Sheets)
The Problem: You want a compact dashboard showing hiring trends, but you don't want to litter your sheet with charts.
The Prompt:
"You are a Google Sheets dashboard designer. I have a sheet 'Hires' with columns: Date, Department, Recruiter. Create a formula in a single cell that generates a sparkline chart showing the monthly hiring count for the last 12 months. The formula should use QUERY to get the monthly counts, then SPARKLINE to render. Also, create a second sparkline for a specific department (e.g., 'Engineering'). Provide the formulas."
Example Result:
=SPARKLINE(QUERY(Hires!A:B, "select month(A), count(A) where A >= date '2025-08-01' group by month(A) order by month(A) label count(A) ''", 0), {"charttype","line"})
This creates a mini line chart that updates automatically as you add data.
7. Recruiter Performance Metrics (SQL)
The Problem: You need to calculate time-to-fill and time-to-start for each recruiter to identify bottlenecks.
The Prompt:
"You are a data analyst. Tables: 'jobs' (id, title, posted_date), 'candidates' (id, job_id, applied_date, offer_date, hire_date). Write a SQL query to calculate for each recruiter (assume a 'recruiter_id' column in jobs): average time-to-fill (days from posted to offer) and average time-to-start (days from offer to hire). Group by recruiter and order by average time-to-fill descending. Use DATE_PART for date difference."
Example Result:
SELECT j.recruiter_id,
AVG(DATE_PART('day', c.offer_date - j.posted_date)) AS avg_time_to_fill,
AVG(DATE_PART('day', c.hire_date - c.offer_date)) AS avg_time_to_start
FROM jobs j
JOIN candidates c ON j.id = c.job_id
WHERE c.offer_date IS NOT NULL
GROUP BY j.recruiter_id
ORDER BY avg_time_to_fill DESC;
This pinpoints which recruiters are fast and which need support.
8. Budget Variance Analysis (Google Sheets)
The Problem: You need to compare actual spending vs budget across departments and highlight variances.
The Prompt:
"You are a Google Sheets formula expert. Sheet 'Actual' has columns: Department, Amount. Sheet 'Budget' has columns: Department, Amount. Write a formula to produce a report showing Department, Budget, Actual, Variance (Actual - Budget), and Variance % (Variance/Budget). Use VLOOKUP to combine data. Then create a conditional formatting rule to highlight negative variances in red. Provide formulas and formatting steps."
Example Result:
=QUERY({Budget!A:B, VLOOKUP(Budget!A:A, Actual!A:B, 2, FALSE)}, "select Col1, Col2, Col3, Col3-Col2, (Col3-Col2)/Col2 where Col1 is not null")
But a more readable approach:
=ARRAYFORMULA({Budget!A:A, Budget!B:B, VLOOKUP(Budget!A:A, Actual!A:B, 2, FALSE)})
Then add variance columns.
9. Automating Weekly Reports (Google Sheets)
The Problem: You have to send a weekly summary to executives, and you're tired of manually copying numbers.
The Prompt:
"You are a Google Sheets automation expert. I have a sheet 'Activity' with columns: Date, Employee, Task Type, Hours. Write a formula that calculates the total hours per task type for the current week (Monday to Sunday). Assume today is August 22, 2026 (a Saturday). Use WEEKNUM and FILTER. Also, create a formula to generate a summary table with columns: Task Type, Hours, % of Total. Finally, explain how to use Google Apps Script to email this summary automatically every Friday at 4 PM."
Example Result:
=FILTER(Activity!D:D, Activity!A:A >= TODAY()-WEEKDAY(TODAY(),2)+1, Activity!A:A <= TODAY()-WEEKDAY(TODAY(),2)+7)
Then use QUERY to group by task type.
10. Predictive Attrition Risk (SQL)
The Problem: You want to identify employees at risk of leaving based on tenure, performance score, and recent complaints.
The Prompt:
"You are a data scientist with SQL expertise. Tables: 'employees' (id, hire_date, performance_score 1-5), 'complaints' (id, employee_id, date). Write a PostgreSQL query that flags employees as 'High Risk' if they have a performance score < 3 and have filed more than 2 complaints in the last 6 months, or if they have a tenure > 5 years and performance score < 4. Output employee name, tenure_years, performance_score, complaint_count, risk_flag. Use CTEs and date arithmetic."
Example Result:
WITH complaint_counts AS (
SELECT employee_id, COUNT(*) AS cnt
FROM complaints
WHERE date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY employee_id
)
SELECT e.name,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.hire_date)) AS tenure_years,
e.performance_score,
COALESCE(c.cnt, 0) AS complaint_count,
CASE
WHEN e.performance_score < 3 AND COALESCE(c.cnt, 0) > 2 THEN 'High Risk'
WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.hire_date)) > 5 AND e.performance_score < 4 THEN 'High Risk'
ELSE 'Low Risk'
END AS risk_flag
FROM employees e
LEFT JOIN complaint_counts c ON e.id = c.employee_id
ORDER BY risk_flag DESC;
This allows HR to proactively engage with at-risk employees.
Putting It All Together
These prompts are more than just snippets—they're a framework for thinking about HR data. By mastering prompts for Google Sheets and SQL, you're not just saving time; you're building a scalable reporting system. The key is to start small: pick one painful report and automate it with these prompts. You'll see immediate wins, and soon you'll wonder how you ever survived manual reporting.
I encourage you to test these prompts in your own environment. Modify them to fit your data structure. The AI is your assistant, but you're the expert who understands your business context. Happy automating!
Comments