How to Create an HR Dashboard in Excel

Building an HR dashboard in Excel helps you transform endless rows of employee data into clear, actionable insights about your workforce. Instead of getting lost in spreadsheets, you can get a bird's-eye view of key metrics like headcount, turnover, and hiring costs. This guide will walk you through, step-by-step, how to create a dynamic HR dashboard using the tools you already have in Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, What Is an HR Dashboard?

An HR dashboard is a visual reporting tool that displays important human resources metrics in one place. It helps you track the health of your organization, spot trends, and make better, data-informed decisions about your people strategy. Think of it as a central hub for your most critical workforce data.

While specialized BI tools are powerful, Excel is often the starting point for many organizations. It's accessible, familiar, and surprisingly capable of creating interactive dashboards. It's the perfect way to get started with data visualization without investing in new software.

Planning Your Excel HR Dashboard

Before you start clicking around in Excel, a little planning goes a long way. Taking a few minutes to think through your goals will make the building process much smoother and ensure the final dashboard is actually useful.

1. Identify Your Key HR Metrics (KPIs)

What do you actually want to measure? Your dashboard is only as good as the metrics it tracks. The right KPIs depend on your organization's goals, but here are some of the most common and valuable ones to consider:

• Headcount: The total number of employees, often broken down by department, location, or management level.
• Employee Turnover Rate: The percentage of employees who leave the company over a specific period. This is often tracked quarterly or annually to spot trends.
• Recruitment Metrics:
• Compensation: Average salary by department or role, providing insights into your company's pay structure.
• Absence Rate: The rate of unplanned employee absences, which can indicate issues with employee well-being or engagement.
• Diversity Metrics: Breakdowns of your workforce by gender, ethnicity, or age to track progress on diversity and inclusion goals.

Start with 5-7 core metrics. You can always add more later, but an overcrowded dashboard can be confusing and less impactful.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Gather and Organize Your Data

Your dashboard is powered by raw data. You need to figure out where this data lives and consolidate it into a single, clean source for Excel.

Your HR data is likely scattered across different systems:

• Your HR Information System (HRIS)
• Payroll software
• Recruiting or Applicant Tracking System (ATS)
• Employee engagement survey results

For your Excel dashboard, the goal is to export data from these sources (usually as a CSV or Excel file) and combine it into one master "data" sheet. This data should be in a simple, tabular format, where each row represents a single record (like an employee) and each column represents a field (like Name, Department, Start Date, Salary).

Pro tip: Make sure your data is clean. Check for inconsistencies like "Marketing" vs. "Mktg", extra spaces, or varied date formats. Clean data is the foundation of an accurate dashboard.

Step-by-Step: Building Your HR Dashboard in Excel

With your data prepped, it's time to build. We'll use a combination of Excel Tables, PivotTables, and Slicers to create a dynamic and professional-looking dashboard.

Step 1: Format Your Data as an Excel Table

First, open your spreadsheet containing your clean, raw HR data. Click any cell within your data range and then convert it into an official Excel Table.

• Go to the Insert tab and click Table, or use the shortcut Ctrl + T (or Cmd + T on Mac).
• Excel will automatically detect your data range. Make sure the "My table has headers" box is checked, then click OK.

Your data will now be formatted in a striped style. Using an Excel Table makes your data dynamic, any new rows you add will automatically be included in your analysis, which is a big deal when it's time to refresh your dashboard.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Create PivotTables for Your Analytics

PivotTables are the engine of your dashboard. They do the heavy lifting of summarizing your data so you can create charts. We’ll create each metric on its own PivotTable in a separate sheet, away from the final dashboard view.

Create a new sheet and name it something like "PivotTables". Now, let's create our first PivotTable to calculate headcount by department:

• Click on any cell inside your data Table.
• Go to the Table Design tab that appears, and click Summarize with PivotTable.
• In the pop-up, choose to place the PivotTable in your existing "PivotTables" worksheet, then click OK.

Now, in the PivotTable Fields pane on the right:

• Drag the "Department" field into the Rows area.
• Drag the "Employee ID" (or any unique identifier) field into the Values area. It should default to "Count of Employee ID." If not, click on it and change the Value Field Setting to "Count".

Voilà! You have a summary table showing headcount by department. Repeat this process to create separate PivotTables for each of your key metrics, like turnover rate over time or average salary by job level.

Step 3: Visualize Your Data with PivotCharts

With your PivotTables ready, it’s time to create visualizations. A PivotChart is a chart that is linked directly to a PivotTable. When the PivotTable data changes, the chart updates automatically.

Let's create a bar chart for our headcount PivotTable:

• Click inside your 'Headcount by Department' PivotTable.
• Go to the PivotTable Analyze tab and click on PivotChart.
• Choose a chart style. A Clustered Bar or Column chart is great for comparing categories. Click OK.

This will insert a chart onto your "PivotTables" sheet. It will look a bit messy with field buttons. Right-click on one of the grey field buttons (like "Count of Employee ID") and select "Hide All Field Buttons on Chart" to clean it up. Don't worry about styling it just yet.

Repeat this step for each of your PivotTables, choosing the best chart type for the data:

• Bar/Column Charts: Excellent for comparing categories (e.g., headcount by department).
• Line Charts: Perfect for showing trends over time (e.g., turnover rate by month).
• Pie/Doughnut Charts: Good for showing parts of a whole (e.g., workforce gender distribution). Use sparingly for categories fewer than 5.
• KPI Cards: For single, important numbers like Total Headcount, create a PivotTable with just one summary number. Then, on your dashboard sheet, create a text box, click on the formula bar, type "=", and click on the cell in the PivotTable with your number. This links the text box to the data.

Step 4: Design Your Dashboard Layout

Now for the fun part! Create a new sheet and name it "HR Dashboard". This will be the clean, presentation-ready view.

Cut and paste (Ctrl + X, Ctrl + V) each of your charts from the "PivotTables" sheet onto your new "HR Dashboard" sheet. Arrange them in a logical and visually appealing way. Here are some design tips:

• Grid Layout: Align your charts neatly in a grid. This gives the dashboard a professional and organized look.
• Spacing: Leave some white space between charts so it doesn't feel cluttered.
• Color Scheme: Use a simple and consistent color palette. Stick to your company's brand colors for a cohesive feel. You can change chart colors on the Chart Design tab.
• Headers: Give your dashboard a clear title at the top (e.g., "Quarterly HR Performance Dashboard").

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Add Interactivity with Slicers

Slicers are user-friendly filters that make your dashboard interactive. Instead of manually filtering data, users can simply click buttons to drill down into the insights they need.

• Click on any of your PivotCharts to select it.
• Go to the PivotChart Analyze tab and click Insert Slicer.
• A pop-up box will appear showing all your data fields. Check the boxes for the fields you want to filter by — "Department" and "Job Level" are great choices. Click OK.

Two slicer boxes will now appear on your dashboard. By default, they only control the chart you had selected. To make them control everything, you need to connect them to all your other PivotTables:

• Right-click on the "Department" slicer and select Report Connections.
• In the pop-up, check the box for every PivotTable you created. Click OK.
• Repeat this process for the "Job Level" slicer.

Now, when you click on "Sales" in the Department slicer, every single chart on your dashboard will instantly filter to show data for only the Sales department. You’ve just built a fully interactive dashboard!

Tips for Maintaining Your Dashboard

• Focus on Clarity: Your dashboard should tell a story at a glance. Use clear chart titles, label your axes properly, and avoid clutter. If a chart doesn't provide a clear insight, reconsider if it's needed.
• Scheduled Updates: To update your dashboard with new data, simply add the new raw data to the bottom of your Excel Table. Then, go to the Data tab and click Refresh All. All your PivotTables and charts will update automatically.
• Protect Your Work: To prevent accidental changes, protect your dashboard sheet. Go to the Review tab and click Protect Sheet.

Final Thoughts

Creating an HR dashboard in Excel is a fantastic way to take control of your people analytics. By transforming your raw data into an interactive, visual report, you empower yourself and your team to make more strategic, data-backed decisions for your organization.

While Excel is a great starting place, the process of manually exporting CSVs from different systems, cleaning them, and updating the dashboard can become a time-consuming weekly routine. As a next step, we built Graphed to automate all that busy work. We connect directly to your data sources — like your HRIS, payroll, and recruiting software — and stream the data in real-time. Instead of building PivotTables and charts, you can just ask questions in plain English like, "show me employee turnover rate by department this year," and it instantly builds a live, shareable dashboard for you.