Graphed LogoGraphed
Login

How to Create an HR Dashboard in Excel with AI

Cody Schneider•

Tracking your company's people data is no longer a nice-to-have, it's essential for smart decision-making. An HR dashboard in Excel can transform scattered employee information into clear, actionable insights. In this tutorial, we'll walk through how to build one from scratch and show you how to use AI to make the process easier and faster.

First, Why Build an HR Dashboard?

An HR dashboard gathers your most important people-related metrics in one place, giving you a real-time snapshot of your organization’s health. Instead of digging through endless spreadsheets to answer a simple question, a good dashboard makes it easy to:

  • Make data-driven decisions: Instead of guessing, you can see exactly which departments have high turnover or how hiring trends are evolving.

  • Spot trends early: Notice a spike in absenteeism or a dip in employee satisfaction scores? A dashboard brings these patterns to the surface before they become major problems.

  • Communicate effectively: Present a clear, visual summary of HR performance to leadership, grounding conversations in facts, not feelings.

  • Track goals: Monitor key performance indicators (KPIs) like time-to-hire or diversity metrics to see if your strategies are working.

Step 1: Plan Your HR Dashboard

Before you even open Excel, a little planning goes a long way. The goal isn't to track every single piece of data you have, but to focus on the metrics that matter most to your business.

Ask the Right Questions

Start by identifying the key questions you or your management team are trying to answer. Your dashboard should be built to answer these at a glance. Good starting questions include:

  • What is our current headcount by department, role, or location?

  • What is our employee turnover rate, and is it getting better or worse?

  • How long does it typically take to fill an open position?

  • Are we meeting our diversity and inclusion goals?

  • What is the main reason employees are leaving the company?

Identify Your Key HR Metrics

The questions above will lead you directly to the key performance indicators (KPIs) you need to track. While every company is different, most HR dashboards include a mix of the following:

  • Workforce Composition: Total Headcount, Headcount by Department, Male/Female Ratio, Employee locations, Average Age, and Average Tenure.

  • Hiring and Recruitment: Time to Fill (days), Time to Hire (days), Cost Per Hire, Offer Acceptance Rate, and Applicants Per Opening.

  • Retention and Turnover: Employee Turnover Rate (monthly or quarterly), Voluntary vs. Involuntary Turnover, and Attrition Rate by Manager or Department.

  • Employee Performance & Engagement: Employee Satisfaction Score (from surveys), Absenteeism Rate, and Promotion Rate.

  • Compensation: Average Salary by Department/Role, and Gender Pay Gap.

Gather and Organize Your Data

Now, it's time to find the data. This is often the most time-consuming step because HR data can live in multiple places - your HRIS (Human Resources Information System), payroll software, recruiting platform, exit interview forms, and engagement survey results.

Your goal is to get all of this data into a single, clean table in Excel. Create a new sheet named "RawData" and structure it with clear column headers like:

  • Employee ID

  • Full Name

  • Gender

  • Department

  • Job Title

  • Location

  • Start Date

  • End Date (leave blank if they're a current employee)

  • Salary

  • Reason for Leaving

A clean, well-organized data table is the foundation of a great dashboard. Make sure your dates are formatted as dates, there are no blank rows, and your categorical data (like "Department") is consistent (e.g., "Engineering" not "Eng" or "engineering").

Step 2: Build the Dashboard in Excel (The Manual Way)

Once your data is organized, you can start building the components of your dashboard using Pivot Tables and Charts. We'll design it on a new sheet called "Dashboard."

Create Your First Metric with a Pivot Table

Pivot Tables are a powerful tool in Excel for summarizing large amounts of data. Let's use one to calculate headcount by department.

  1. Click anywhere inside your table on the "RawData" sheet.

  2. Go to the Insert tab and click PivotTable. Excel will automatically select your data range. Choose to place it in a 'New Worksheet' and click OK.

  3. A new sheet will open with the PivotTable Fields pane on the right.

  4. Drag the "Department" field into the Rows area.

  5. Drag "Employee ID" into the Values area. Excel will default to "Sum of Employee ID." Click on it, select "Value Field Settings," and change it to Count.

Just like that, you have a summary table showing the number of employees in each department. Rename this sheet "PivotTables" to keep everything organized.

Visualize Your Data with Charts

Numbers are great, but visuals are better for at-a-glance understanding. Let's turn our Pivot Table into a chart.

  1. Click on your new Pivot Table.

  2. Go to the PivotTable Analyze tab and click PivotChart.

  3. Choose a chart style that fits your data. A Column or Bar chart works perfectly for displaying headcount by department. Click OK.

  4. Cut the chart (Ctrl+X or Cmd+X) from the "PivotTables" sheet and paste it (Ctrl+V or Cmd+V) onto your "Dashboard" sheet.

Clean up your chart by right-clicking the grey field buttons (like "Total" and "Department") and selecting "Hide all field buttons on chart." Give it a clear title like "Headcount by Department."

Repeat this process - creating a new Pivot Table and then a PivotChart - for each metric you decided to track in your planning phase.

Add Slicers for Interactive Filtering

Slicers are filters that make your dashboard interactive. With one click, you can filter all your charts to show data for a specific department, location, or timeframe.

  1. Click on any of your PivotCharts on the "Dashboard" sheet.

  2. Go to the PivotChart Analyze tab and click Insert Slicer.

  3. A dialog box will appear with all your data columns. Check the boxes for the fields you want to filter by, such as "Department," "Gender," and "Location." Click OK.

  4. Arrange the slicers on your dashboard. Now, when you click an option in a slicer (e.g., "Engineering"), your charts will instantly update.

Step 3: Supercharge Your Excel Reporting with AI

Building the dashboard manually is a great skill, but AI can handle much of the heavy lifting, saving you time and helping you find deeper insights. Here are a few ways AI can accelerate your workflow, either directly in Excel or with the help of external tools.

1. Automated Data Cleaning and Prep

Messy data is a huge pain. One department is "Sales," another is "sales," and a third is "Sales Team." AI tools are fantastic at spotting and fixing these inconsistencies. Even Excel's built-in Flash Fill feature is a form of AI that learns patterns and automates data entry.

You can also use tools like ChatGPT by pasting a small sample of your messy data and asking for instructions on how to clean it. For example, "I have a column with job titles that are inconsistent. Give me steps in Excel to standardize them."

2. Generating Complex Formulas

Struggling to figure out the formula for calculating employee turnover? Just ask an AI assistant.

Simply describe what you need in plain English. For instance, you could prompt it with:

"Write an Excel formula to calculate the turnover rate for Q2 2024. My data is on a sheet named 'RawData'. The termination date is in column H, and I want to divide the number of people who left in Q2 by the average number of employees during that quarter."

The AI will process your request and give you a sophisticated formula you can copy and paste directly into Excel. This turns hours of formula-building frustration into seconds of work.

3. Asking Questions to Your Data for Instant Summaries

Modern versions of Excel include features like "Analyze Data" (on the Home tab), which uses AI to analyze your dataset and automatically suggest relevant Pivot Tables, charts, and insights. It's like having a data analyst built right into the program.

You can even type natural language questions into its query box, such as "What is the average salary by job title?" and it will generate a Pivot Table that answers your question on the spot.

4. Creating Executive Summaries

Once your dashboard is complete, you still need to communicate what the data means. AI can be a great assistant for that, too.

Take your key findings (e.g., "Total headcount is 250," "Employee turnover for Q2 was 4.5%," "Engineering was the fastest-growing department with 15 new hires") and paste them into a tool like ChatGPT. Then, ask it to write a brief summary of HR performance for a leadership email. It will weave your data points into a clear and concise narrative, saving you time and helping you communicate your insights more effectively.

Final Thoughts

Building an HR dashboard in Excel is a powerful way to turn your people data into a strategic business asset. By following the steps to organize your data, build Pivot Tables, and visualize your metrics, you can create a valuable tool for tracking performance and guiding decisions. Incorporating AI streamlines the most difficult parts of this process, from data cleaning to insight generation.

While Excel gets the job done, the manual work of connecting data sources, building reports, and keeping everything updated can quickly become a full-time job. At Graphed, we created our platform to automate this entire process. You simply connect your HR systems and other data sources, then build real-time, interactive dashboards by asking questions in plain English - no Pivot Tables or formulas required. The result is instant, always-updated insights without the spreadsheet wrangling.