How to Create an HR Dashboard in Google Sheets with ChatGPT

Creating an HR dashboard might sound like a job for a data analyst, but it’s entirely possible to build a powerful and insightful one using just Google Sheets and a little help from ChatGPT. Instead of getting tangled in complex formulas or expensive software, you can leverage these familiar tools to get a clear picture of your people data. This article will walk you through the entire process, step-by-step, from organizing your data to building a visual dashboard that helps you make smarter decisions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Build an HR Dashboard at All?

In human resources, you're sitting on a goldmine of data scattered across spreadsheets, payroll systems, and HR platforms. An effective dashboard brings this information together in one place, instantly highlighting key trends and metrics. It swaps guesswork for data-driven insights, helping you answer critical questions like:

• Are we retaining our top talent?
• Which department has the highest turnover rate?
• Is our gender diversity improving over time?
• How much are we really spending to hire new employees?

A well-built dashboard turns raw data into a clear story, allowing you to spot problems early, celebrate wins, and communicate your team's impact to leadership with confidence.

Step 1: Get Your HR Data in Order

Before you can build anything, you need a clean, reliable source of data. The "garbage in, garbage out" rule applies here more than anywhere. This is easily the most important step.

Choosing Your Key Metrics

Don't try to track everything at once. Start with a few key performance indicators (KPIs) that are most important to your organization right now. Here are some fundamental HR metrics to consider:

• Headcount: The total number of employees, often broken down by department, role, or location.
• Turnover Rate: The rate at which employees leave the company, voluntarily or involuntarily.
• Cost Per Hire: The total cost of recruiting divided by the number of new hires.
• Average Salary: Often calculated by department or role to track compensation trends.
• Diversity & Inclusion Metrics: Ratios of employees based on gender, ethnicity, or other demographic data.
• Employee Tenure: The average length of time an employee stays with the company.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Structuring Your Data in Google Sheets

The best way to structure your data for analysis is in a simple, flat table format. Create a Google Sheet and name your first tab something like "Raw HR Data." Set it up so that each row represents a single employee record and each column represents a piece of information about that employee.

A simple structure might look like this:

• Column A: Employee ID
• Column B: Full Name
• Column C: Status (Active, Terminated)
• Column D: Department (e.g., Sales, Marketing, Engineering)
• Column E: Job Title
• Column F: Start Date
• Column G: End Date (leave blank for active employees)
• Column H: Annual Salary
• Column I: Gender

Keep your data clean and consistent. Make sure everyone calls the "Marketing" department the same thing and that all dates are in the same format. This upfront work will save you hours of headaches later.

Step 2: Leveraging ChatGPT for Formulas and Calculations

Here's where the magic happens. Instead of googling obscure spreadsheet functions, you can simply ask ChatGPT to write the formulas for you. Treat it like your personal data analyst who happens to be an expert in Google Sheets.

Create a second tab in your Google Sheet called "Dashboard Calcs." This is where you'll summarize the results from your raw data.

Calculating Total Headcount by Department

Let's say you want a simple breakdown of how many active employees are in each department. Your prompt to ChatGPT could be:

“I have a Google Sheet named 'Raw HR Data'. In column D, I have the 'Department', and in column C, I have the employee 'Status'. Give me a formula to count the number of 'Active' employees in the 'Engineering' department.”

ChatGPT will likely give you a formula like this:

=COUNTIFS('Raw HR Data'!D:D, "Engineering", 'Raw HR Data'!C:C, "Active")

You can then repeat this for each department (Sales, Marketing, etc.) in your "Dashboard Calcs" sheet to create a summary table.

Calculating Average Salary by Department

Next, let's find the average salary. Your prompt could be:

“Using the same Google Sheet, I need a formula to calculate the average salary for active employees in the 'Sales' department. Salary is in column H, Department is in column D, and Status is in column C.”

The AI will likely return a similar-looking formula using AVERAGEIFS:

=AVERAGEIFS('Raw HR Data'!H:H, 'Raw HR Data'!D:D, "Sales", 'Raw HR Data'!C:C, "Active")

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Advanced Example: Calculating Quarterly Employee Turnover Rate

Manually calculating turnover is a classic HR headache involving multiple steps and dates. This is a perfect task for an AI assistant. A more detailed prompt can handle the complexity:

“Help me calculate the employee turnover rate for Q1 2024 (Jan 1 to Mar 31). In my 'Raw HR Data' sheet, column G is 'End Date' and column F is 'Start Date'. The turnover rate formula is (Number of employees who left in the quarter / Average number of employees in the quarter) * 100.”

ChatGPT will break this down for you, providing separate formulas for each part:

1. To count employees who left in Q1:

=COUNTIFS('Raw HR Data'!G:G, ">="&DATE(2024,1,1), 'Raw HR Data'!G:G, "<="&DATE(2024,3,31))

1. And it can even help you find the average employee count for that period. The conversation with the AI helps you think through the logic without getting stuck on syntax.

Tips for Effective ChatGPT Prompts

To get the best formulas from ChatGPT, follow these simple rules:

• Be Specific: Mention your sheet name ('Raw HR Data') and the exact column letters (Column A, Column B).
• State Your Goal Clearly: Start with "I want to calculate..." or "Give me a formula that shows..."
• Provide Context: Explain what each column represents (e.g., "column G is the employee's end date").
• Ask for an Explanation: End your prompt with "and explain how the formula works." This helps you learn and troubleshoot later.

Step 3: Building the Dashboard - From Numbers to Insights

With your key metrics calculated on the "Dashboard Calcs" sheet, it's time to build the visual dashboard. This should be clean, easy to read, and highlight the most important information at a glance. Create a new tab and call it "HR Dashboard."

Creating Your Summary Table

First, create a clean summary area at the top. This section should display your most important top-line numbers. You can simply reference the cells from your "Dashboard Calcs" sheet.

For example, in cell A1 of your dashboard, you might have the text "Total Active Headcount," and in cell B1, you'd put the formula 'Dashboard Calcs'!B1 (or whichever cell holds your total headcount calculation).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Adding Charts and Graphs

Visuals are what turn a list of numbers into an actual dashboard. Google Sheets makes creating charts easy.

Headcount by Department (Pie Chart)

1. On your "Dashboard Calcs" sheet, select the data for Department and Headcount.
2. Go to Insert > Chart.
3. Google Sheets will often suggest a pie chart, which is perfect for showing parts of a whole.
4. Customize the colors and titles, then copy and paste this chart onto your "HR Dashboard" tab.

Average Salary by Department (Bar Chart)

1. Select the data for Department and Average Salary.
2. Go to Insert > Chart. Choose a column or bar chart for easy comparison.
3. Customize and move it to your main dashboard tab.

Turnover Rate Over Time (Line Chart)

1. If you calculate the turnover rate for each quarter, you can create a table with Quarter in one column and Turnover Rate in the next.
2. Select this data and insert a Line Chart to visualize the trend over time.

Arrange your charts and summary numbers on the "HR Dashboard" tab in a logical way. The goal is to make it readable and useful for anyone who looks at it.

Step 4: Making Your Dashboard Interactive and Keeping It Fresh

A static dashboard is useful, but an interactive one is far better. A great feature for this in Google Sheets is Slicers (Data > Add a Slicer). You can add a slicer for 'Department', for example, allowing any viewer to filter the entire dashboard to see data for just the Sales team or the Engineering team.

Remember, this dashboard is only as good as the data powering it. You will need to periodically update your "Raw HR Data" sheet by exporting fresh data from your HR systems and pasting it into the sheet. Since all your formulas and charts are connected, the dashboard will update automatically as soon as the new data is in place.

Final Thoughts

By connecting the simplicity of Google Sheets with the formula-writing horsepower of ChatGPT, you can build a highly effective HR dashboard without needing a degree in data science. This approach helps you organize your employee data, calculate key metrics, and create powerful visualizations that tell a clear story about your workforce.

While this Google Sheets method is a huge step up from manual analysis, we know the constant exporting and updating of raw data files can still be a drag. We originally solved this problem for ourselves by building Graphed, which eliminates that drudgery entirely. You can connect your HR, sales, and marketing platforms directly, then simply ask - in plain English - for the exact live-updating dashboards and reports you need, getting you from question to real-time insight in seconds, not hours.