How to Create an HR Dashboard in Google Sheets

Creating an HR dashboard doesn't require expensive, specialized software, you can build a powerful and informative one right inside Google Sheets. While it takes some setup, having a central place to track key people metrics can transform how you make decisions about hiring, retention, and company culture. This guide will walk you through the process step-by-step, from organizing your raw data to building interactive charts and reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is an HR Dashboard and Why Use Google Sheets?

An HR dashboard is a visual representation of your most important human resources data. At a glance, it should answer critical questions about your workforce, such as headcount trends, turnover rates, and hiring efficiency. It centralizes key performance indicators (KPIs) to help you spot trends, identify potential issues, and share insights with leadership.

Google Sheets is an excellent starting point for your first HR dashboard for several reasons:

• It's Free and Accessible: Anyone with a Google account can use it. There's no cost, and it's cloud-based, so you can access it from anywhere.
• Collaboration is Built-In: You can easily share your dashboard with stakeholders or team members, controlling who can view or edit the data.
• It's Familiar: If you've used any spreadsheet program before, the interface will feel comfortable, reducing the learning curve.

The main trade-off is that it requires manual data updates. Unlike sophisticated business intelligence tools, Google Sheets won't automatically sync with your HRIS or payroll system. But for many teams, the benefits of having a clear, centralized report outweigh the occasional manual work.

Step 1: Plan Your Dashboard by Identifying Key HR Metrics

Before you open a single spreadsheet, the most important step is to decide what you want to measure. A great dashboard isn't about throwing every piece of data onto a screen, it's about answering specific, important questions about your business. Start by thinking about the goals of your HR department and the company as a whole.

Here are some fundamental HR metrics to consider tracking:

Graphed

Still Building Reports Manually?

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

Watch the Demo

Recruitment Metrics:

• Time to Fill: The number of days between a job requisition being posted and an offer being accepted. It measures the efficiency of your hiring process.
• Cost Per Hire: The total cost of recruiting (ad spend, recruiter fees, etc.) divided by the number of new hires. This helps you understand and manage your recruiting budget.
• Offer Acceptance Rate: The percentage of candidates who accept a formal job offer. A low rate might indicate issues with compensation, benefits, or company culture.

Employee Engagement & Retention Metrics:

• Total Headcount: A simple count of all active employees. You’ll want to track this over time and be able to break it down by department, location, or other categories.
• Turnover Rate: The percentage of employees who leave the company during a specific period (usually monthly, quarterly, or annually). High turnover is a significant red flag.
• Absence Rate: The rate of unplanned employee absences due to sickness or other reasons. A rising rate could signal burnout or low morale.

Diversity & Inclusion Metrics:

• Gender Diversity Ratio: The ratio of male, female, and non-binary employees in the company, often visualized by department or leadership level.
• Ethnicity Breakdown: A look at the diversity of your workforce across different ethnic groups to ensure equitable representation.

Choose 5-7 core metrics to start with. You can always add more later, but focusing on the essentials prevents you from getting overwhelmed.

Step 2: Collect and Structure Your Data

This is where the groundwork is laid. Your visualizations will only be as good as the data powering them. Your goal is to create one central "database" tab in your spreadsheet that is clean, organized, and machine-readable.

Create Two Core Tabs

In a new Google Sheet, create two tabs at the bottom:

1. Raw Data: This is where your master list of employee data will live. You should never build charts directly on this page. Think of it as your single source of truth.
2. Dashboard: This is where your charts, graphs, and KPIs will be displayed. This page will pull all its information from the 'Raw Data' tab.

This two-tab structure keeps your data separate from your presentation, making the entire workbook much easier to manage and update.

Building Your Raw Data Set

On your 'Raw Data' tab, format your data as a clean table. Each row should represent a unique record (e.g., one employee), and each column should represent a piece of information about that employee.

Here’s an example structure:

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

Scrub your data to ensure consistency. For example, make sure "Sales" is always spelled the same way and doesn't also appear as "sales team." This consistency is essential for accurate calculations and filtering.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Calculate Your Metrics and Create Visualizations

Now for the fun part: bringing your data to life. We will build our calculations and charts on the 'Dashboard' tab by referencing the 'Raw Data' tab.

Calculating Key KPIs

First, let's calculate a few of those high-level numbers that executives will want to see.

Total Headcount

This is the simplest one. We just need to count the number of employees marked as "Active."

1. In a cell on your 'Dashboard' tab, type the label "Total Headcount."
2. In the cell next to it, use the COUNTIF function. This formula will count how many times the word "Active" appears in the 'Status' column (Column C) on your Raw Data sheet.

=COUNTIF('Raw Data'!C:C, "Active")

Calculating Turnover Rate (Simplified)

A true turnover rate calculation is complex, but we can create a simple version. For example, let's find the number of people who left in 2023. This requires counting rows where the 'End Date' falls within that year.

1. In a cell, type a label like "Departures in 2023."
2. Next to it, use the COUNTIFS function, which lets you count based on multiple criteria.

=COUNTIFS('Raw Data'!E:E, ">=1/1/2023", 'Raw Data'!E:E, "<=12/31/2023")

You can then divide this number by your average headcount for the year to get a percentage rate.

Creating Charts with Pivot Tables

Pivot tables are the easiest way to summarize your data for charting. Let’s create a chart showing headcount by department.

1. Click anywhere inside your table on the 'Raw Data' sheet.
2. Go to Insert > Pivot Table.
3. For 'Insert to,' choose 'Existing sheet' and then select a blank cell on your 'Dashboard' tab.
4. The Pivot table editor will open. In the 'Rows' section, add 'Department'.
5. In the 'Values' section, add 'Employee ID' and make sure it is summarized by COUNTA.

This will generate a simple summary table showing the number of employees in each department. Now, select that table, go to Insert > Chart, and choose a Pie Chart or Bar Chart. Google Sheets is smart enough to create a nice-looking chart from your pivot table data.

You can repeat this process for other charts:

• Gender Diversity: Use 'Gender' in the Rows and COUNTA of 'Employee ID' in the Values. A pie chart works well here.
• Hires by Month: Use 'Start Date' in the Rows. Right-click on one of the dates in the pivot table and choose "Create pivot date group" to group by Year-Month. Use COUNTA of 'Employee ID' in the Values and visualize with a Line Chart.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Design Your Dashboard Layout

Once you have a few KPIs and charts, organize them on your 'Dashboard' tab. The goal is a clean, scannable layout.

• Top Left is Prime Real Estate: Put your most important summary KPIs, like Total Headcount and Turnover Rate, at the top. People naturally read from top to bottom, left to right.
• Use Consistent Sizing: Resize your charts to be uniform. A grid-like layout looks professional and is easy to follow.
• Label Everything Clearly: Give every chart a descriptive title, like "Total Headcount by Department" instead of just "Department." Add text boxes with 'KPI' callouts if needed to explain key trends and summarize insights in natural language text.
• Keep It Simple: Avoid clutter, 3D effects, or distracting colors. The simpler the design, the easier it is to read and absorb the information.

Step 5: Add Interactivity with Slicers

The real power of a dashboard is filtering data on the fly. Google Sheets' Slicers make this incredibly easy.

1. Click on one of your pivot tables.
2. Go to Data > Add a Slicer.
3. In the slicer settings on the right, choose the column you want to filter by - 'Department' is a great one to start with.
4. The slicer will now appear on your dashboard. However, by default, it only controls the pivot table you created it from. To fix this, click on the slicer, then click the three dots and select "Apply to all Pivot Tables on sheet."

Now, when you use the slicer to filter by "Sales," all of your pivot tables and the charts based on them will update automatically to show data for just the Sales department. You can add slicers for 'Location,' 'Job Title,' or any other category you want to analyze.

Final Thoughts

Building an HR dashboard in Google Sheets puts critical people-data at your fingertips, helping you move from reactive problem-solving to proactive strategy. By organizing your data correctly and using pivot tables and slicers, you can create a surprisingly powerful and interactive report without spending a dime on fancy software.

At Graphed, we see countless teams get bogged down by the manual process of exporting CSVs and fighting with spreadsheets just to get answers. We built our product specifically to eliminate that wasted time. By connecting directly to your data sources - from HR systems to sales and marketing platforms - we allow you to simply ask for what you need in plain English. You can create always-on, real-time dashboards in seconds, so you can spend your time acting on incredible insights, not just chasing them down.