Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create an HR Dashboard in Excel with ChatGPT

Cody Schneider••8 min read

Building a robust HR dashboard in Excel used to mean spending hours wrestling with complex formulas and wrangling data. Now, you can use ChatGPT as your personal Excel assistant to get the job done in a fraction of the time. This guide will walk you through exactly how to create a dynamic, interactive HR dashboard from scratch using Excel and the power of AI.

GraphedGraphed

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.

Watch Graphed demo video

We'll cover everything from planning your dashboard and structuring your data to using simple prompts in ChatGPT to generate the formulas, PivotTables, and charts you need to visualize your key People metrics.

What Exactly is an HR Dashboard?

An HR dashboard is a one-page report that gives you a clear, at-a-glance view of your most important human resources metrics. Instead of digging through endless spreadsheets or different HR systems, you get a visual summary of workforce analytics in a single location. The goal is to make it easy to spot trends, identify potential issues, and make informed decisions about your people strategy.

Common metrics you might find on an HR dashboard include:

  • Headcount: Total number of employees, often broken down by department, location, or team.
  • Employee Turnover Rate: The rate at which employees are leaving the company, either voluntarily or involuntarily.
  • Recruitment Funnel: Metrics like average time-to-hire, cost-per-hire, and offer acceptance rate.
  • Employee Satisfaction & Engagement: Data often pulled from surveys (e.g., eNPS - employee Net Promoter Score).
  • Diversity, Equity & Inclusion (DEI): Breakdowns of your workforce by gender, ethnicity, or other demographics.
  • Compensation: Average salary by department, pay equity ratios, and total payroll costs.

Having this data visually accessible helps you answer critical questions faster, like "Are we seeing an increase in turnover in the engineering department?" or "How is our hiring pipeline for the second quarter looking?"

Before You Build: Planning Your Dashboard

A little planning upfront saves a lot of headaches later. Before you even open Excel, take a few minutes to think through these three key areas.

1. Define Your Key Performance Indicators (KPIs)

Your dashboard is only as useful as the metrics it tracks. Don't try to visualize everything. Instead, focus on the handful of KPIs that align with your company’s goals. Ask yourself and your stakeholders: What are the most critical questions we need to answer about our workforce?

Start with cornerstone metrics like total headcount and turnover. Then, expand based on your priorities. If hiring is a major focus, track "Time to Fill" and "Cost Per Hire." If retention is the top priority, you might want to visualize "Turnover by Manager" or "Employee Satisfaction Scores."

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.

2. Prepare Your Data Source

Dashboards run on data. For an HR dashboard, this data usually comes from a Human Resources Information System (HRIS) like Workday, BambooHR, or Gusto. The simplest way to start is by exporting a single, comprehensive "employee census" or "headcount report" as a CSV or Excel file.

The key here is organization. Your raw data should be in a single table, with clean, consistent column headers. A typical dataset might include:

  • Employee ID
  • Full Name
  • Department
  • Job Title
  • Hire Date
  • Termination Date (if applicable)
  • Salary
  • Manager
  • Performance Rating
  • Gender/Ethnicity

Format this raw data as an official Excel Table by selecting your data and pressing Ctrl + T. Naming this table (e.g., HR_Data) makes writing formulas much easier, especially when you start asking ChatGPT for help.

3. Sketch a Rough Layout

You don't need to be a designer, but a quick sketch on paper or in a simple tool can provide clarity. Decide where your most important metrics (your KPIs) will go – usually at the top in large, clear "cards." Then, decide where to place your main charts and graphs. Think about adding filters or "slicers" to let users interact with the data, allowing them to drill down by department or time period.

Building Your HR Dashboard with Excel and ChatGPT: A Step-by-Step Guide

With your plan in place and data ready, it’s time to start building. Here's how you can leverage ChatGPT at every step.

Step 1: Get Sample Data with ChatGPT (If You Need It)

If you just want to practice building the dashboard or need to create a template without using real employee data, you can ask ChatGPT to generate realistic dummy data for you. This is a great way to start building the structure before you plug in your sensitive company information.

Example ChatGPT Prompt:

"Act as a data analyst. Please create a sample HR dataset for a tech company with 100 employees. Include the following columns: Employee ID, Full Name, Department (Sales, Marketing, Engineering, HR, Finance), Job Title, Hire Date (between 2020-2024), Termination Date (should be blank for most), Salary (between $50,000 and $200,000), Manager, and Performance Rating (Below Expectations, Meets Expectations, Exceeds Expectations). Format the output as a clean, A1-style table I can copy directly into Excel."

ChatGPT will generate a table you can copy and paste directly into a new Excel sheet. Remember to format it as an Excel Table (Ctrl + T).

GraphedGraphed

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.

Watch Graphed demo video

Step 2: Calculate Your Core KPIs with ChatGPT Formulas

Now, let's create those high-level metric "cards" for the top of our dashboard. This is where ChatGPT shines, saving you from having to look up or manually write formulas.

Let's assume your data table is named HR_Data.

Total Headcount

ChatGPT Prompt:

"I have an Excel table named HR_Data. Write me an Excel formula to count the total number of active employees. An employee is active if their 'Termination Date' column is blank."

Likely Formula from ChatGPT:

=COUNTIF(HR_Data[Termination Date], "")

In a cell on your dashboard sheet, you can combine this with text: ="Total Headcount: "&COUNTIF(HR_Data[Termination Date], "")

Annual Turnover Rate

Calculating turnover is a bit more complex, making it a perfect task for ChatGPT.

ChatGPT Prompt:

"I have an Excel table named HR_Data. Write a formula to calculate the annualized employee turnover rate for the current year. I'll need to count terminations this year (the 'Termination Date' column is not blank and falls within the current year) and divide that by the average number of active employees this year."

ChatGPT will likely return a multi-part formula using functions like COUNTIFS, YEAR, TODAY, and AVERAGE to calculate this complex metric. Simply copy and paste it into your Excel sheet.

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.

Step 3: Create Visualizations with PivotTables and Charts

PivotTables are the engine for most Excel dashboards. They summarize your raw data, making it easy to create charts.

From your raw data sheet, go to Insert > PivotTable. Make sure it selects your entire HR_Data table and places the PivotTable in a new worksheet.

Now, let’s create a few key charts.

Bar Chart: Headcount by Department

  1. In your new PivotTable, drag the "Department" field into the Rows area.
  2. Drag the "Employee ID" field into the Values area. Ensure it shows as "Count of Employee ID."
  3. Select your PivotTable, then go to PivotTable Analyze > PivotChart. Choose a Bar Chart.

If you're unsure, ask ChatGPT: "In an Excel PivotTable, how do I set up a bar chart to show the number of employees in each department?" It will give you the exact steps above.

Line Chart: Hires Over Time

  1. Create a new PivotTable from your HR_Data.
  2. Drag "Hire Date" to the Rows area. Excel will automatically group the dates by years and quarters. You can right-click the dates to change the grouping (e.g., to just Years and Months).
  3. Drag "Employee ID" to the Values area to get a count.
  4. Go to PivotTable Analyze > PivotChart and select a Line Chart to see the hiring trend over time.

Step 4: Assemble Your Dashboard

Now it's time to bring everything together on a single 'Dashboard' sheet.

  1. Select each of your PivotCharts, then copy (Ctrl + C) and paste (Ctrl + V) them onto your dedicated dashboard sheet.
  2. Copy and paste your KPI cards (from Step 2) to the top of the dashboard.
  3. Arrange the elements in a logical way that’s easy to read. Turn off gridlines (View > uncheck Gridlines) for a cleaner, more professional look.

Step 5: Add Interactivity with Slicers

Slicers are user-friendly filters that make your dashboard dynamic. Instead of manually filtering each chart, users can simply click a button.

  1. Click on any of your PivotCharts on the dashboard sheet.
  2. Go to PivotTable Analyze > Insert Slicer. A dialog box will appear.
  3. Check the boxes for the fields you want to filter by, such as "Department," "Job Title," and "Manager." Click OK.

Now you have slicer buttons on your sheet. The problem is they only control one chart. To fix this, right-click on a slicer and select Report Connections. In the new pop-up, check the boxes for all the PivotTables you want this slicer to control. Do this for each slicer. Now, when a user clicks "Marketing," all connected charts will update to show only data for the marketing department.

Final Thoughts

Combining the organizational power of Excel with the "on-demand" formula and logic generation of ChatGPT dramatically lowers the barrier to creating meaningful HR reports. You can move from a stagnant spreadsheet of employee data to a fully interactive dashboard that provides real business insight without needing to be an Excel wizard.

While this method is powerful, managing data syncs, refreshing PivotTables, and ensuring calculations are always up-to-date still requires manual effort. At Graphed, we automate all of that for you. We built Graphed to eliminate this exact kind of data drudgery. You securely connect your HRIS, sales, and marketing data sources once, and then simply describe the dashboard you need in plain English - like you do with ChatGPT. We build the full, live dashboard for you in seconds, with real-time data that updates automatically.

Related Articles