How to Create a Headcount Report in Google Sheets

A headcount report is more than just a list of employees, it’s a vital tool for planning your budget, managing departmental growth, and understanding your company’s overall health. Building one shouldn't be complicated. This guide walks you through creating a dynamic headcount report from scratch using Google Sheets, covering everything from organizing your employee data to calculating key metrics and visualizing trends.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First Things First: Prepare Your HR Data

Before you can build any report, you need a solid foundation of clean, well-organized data. All your key calculations and charts will pull from a single master employee list, so getting this part right is the most important step. A common practice is to have one Google Sheet document, with your raw data in one tab (e.g., 'Employee Data') and your report or dashboard in another tab.

What to Include in Your Master Employee List

Create a tab in your Google Sheet named "Employee Data" and set it up with columns for the core information you’ll need. Each row will represent a single employee.

Here are the essential columns to include:

• Employee ID: A unique identifier for each employee.
• Full Name: The employee’s first and last name.
• Department: The department they work in (e.g., Marketing, Sales, Engineering).
• Job Title: Their specific role.
• Start Date: The day their employment began.
• End Date: The day their employment ended (leave it blank for active employees).
• Employment Status: A simple status like "Active" or "Terminated."
• Location: The office or city where they are based (helpful for distributed teams).
• Salary (Optional): Including salary figures allows you to calculate department costs and budget forecasts.

Quick Tips for Keeping Your Data Clean

Mismatched department names or inconsistent date formats can break your formulas. Consistency is your best friend here. Here are a few ways to keep your data organized from day one:

• Use Data Validation: To avoid typos like "Marketing" vs. "Marekting," use data validation to create dropdown menus for columns like Department, Status, and Location. Select the column, go to Data > Data validation, and add the dropdown items.
• Standardize Dates: Settle on a single date format across your sheet, such as YYYY-MM-DD. This makes date-based calculations much more reliable.
• Avoid Blank Rows: Make sure your data is in a continuous table with no empty rows between employees, as this can interrupt formulas.

Calculating Key Headcount Metrics with Formulas

With your data set up, open a new tab and name it something like “Dashboard” or “Report.” This is where you’ll perform your calculations and showcase the results. The following formulas reference your "Employee Data" tab to pull the numbers you need.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Calculating Total Active Headcount

This is the most fundamental metric. It tells you exactly how many active employees you have right now. Instead of manually counting rows, you can use the COUNTIF function to count only the employees marked as "Active."

Assuming your employment status is in Column G of your "Employee Data" tab, the formula is:

=COUNTIF('Employee Data'!G2:G, "Active")

Tracking New Hires Over Time

Understanding your hiring velocity is crucial for measuring recruiting efforts and growth. You can track new hires per month, quarter, or year using the COUNTIFS function, which lets you count based on multiple criteria - in this case, a date range.

For example, to count the number of hires in January 2024 (based on the 'Start Date' in Column E), you would use:

=COUNTIFS('Employee Data'!E2:E, ">=2024-01-01", 'Employee Data'!E2:E, "<=2024-01-31")

To make this dynamic and automatically count hires for the current month, you can use the TODAY() and EOMONTH() functions. This formula might look complex, but it simply finds the first and last day of the current month and counts the hires between those dates:

=COUNTIFS('Employee Data'!E2:E, ">="&EOMONTH(TODAY(),-1)+1, 'Employee Data'!E2:E, "<="&EOMONTH(TODAY(),0))

Keeping Tabs on Employee Turnover

Turnover, or employee attrition, is the rate at which employees leave your company. Tracking this helps you spot potential issues with retention. You can calculate the number of terminations each month using a similar COUNTIFS formula, but this time referencing the 'End Date' column (let's say it's Column F) and the 'Status' column (G).

Here’s how to count employees who were terminated this month:

=COUNTIFS('Employee Data'!F2:F, ">="&EOMONTH(TODAY(),-1)+1, 'Employee Data'!F2:F, "<="&EOMONTH(TODAY(),0), 'Employee Data'!G2:G, "Terminated")

To get your turnover rate, you divide the number of terminations in a period by the average number of employees during that same period, then multiply by 100. For a simple monthly rate: (Terminations this month / Active Headcount a month ago) * 100.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Creating Headcount Breakdowns with Pivot Tables

Manually writing formulas for every single department would be tedious. This is where pivot tables are a lifesaver. A pivot table can automatically summarize your data and give you a breakdown of headcount by department, location, or any other category.

Here’s how to create one to show headcount per department:

1. Go to your “Employee Data” tab and select all your data (Ctrl+A or Cmd+A).
2. Click on Insert > Pivot table.
3. Choose to create it in your existing “Dashboard” sheet.
4. In the pivot table editor on the right:

Just like that, you have an automatically updating table showing the exact number of active employees in each department.

Visualizing Your Data: From Numbers to Insights

Numbers and tables are great, but charts are what tell the story at a glance. Visualizing your data makes it easier for everyone - from your executive team to department heads - to immediately understand the key takeaways.

Building a Headcount by Department Chart

Now that you have your department breakdown in a pivot table, turning it into a chart is simple. A pie chart or bar chart works perfectly for this.

1. Click on any cell inside your department pivot table.
2. Go to Insert > Chart.
3. Google Sheets will suggest a chart type. A Bar chart is often the clearest way to compare department sizes. A Pie chart can work if you have fewer than six departments.
4. Use the chart editor to customize titles and colors for a clean look.

Visualizing Hires vs. Turnover Over Time

One of the most powerful visuals in HR reporting is a chart that compares new hires to terminations over time. This immediately shows if your team is growing, shrinking, or staying the same.

To create this, you’ll first need a small summary table on your dashboard. Set it up with four columns: 'Month', 'Hires', 'Terminations', and 'Net Change'.

• In the 'Month' column, list the past few months (e.g., "Jan 2024," "Feb 2024").
• Use your COUNTIFS date formulas from earlier to populate the 'Hires' and 'Terminations' columns for each month.
• The 'Net Change' column is a simple formula: =Hires - Terminations.

Once you have this table, select it and go to Insert > Chart. A combo chart is ideal here: use columns for hires and terminations and a line for the net change. This chart will visually tell the story of your company's growth trends.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Maintaining Your Report Without All the Manual Work

A headcount report is only valuable if it’s consistently up to date. The goal is to set it up once and minimize manual intervention later. Formulas and pivot tables update automatically, but you still need a clean process for adding and updating employee data.

One easy win is to use a Google Form for new hire submissions. Link it directly to your 'Employee Data' sheet so that every time a new employee is hired, their information is added consistently without a single copy-paste.

However, spreadsheet-based reporting has its limits. As your company grows, you’ll find keeping the master list accurate - especially with terminations - becomes a constant manual task. Wrestling with broken formulas and pulling in data from other systems like your recruiting software or payroll platform usually means spending hours every week on tedious report updates.

Final Thoughts

Creating a headcount report in Google Sheets is entirely achievable. By starting with a clean employee list, using formulas and pivot tables to calculate key metrics, and visualizing the results with charts, you can get a powerful view of your team’s composition and trajectory. These insights are fundamental to making smart business decisions.

Of course, managing this in a spreadsheet often becomes an endless cycle of manual updates and formula fixing, especially when you need to combine data from other tools. At Graphed, we focus on eliminating that friction. You can connect all your HR, finance, and operations tools, and then building real-time dashboards is as simple as asking a question. Instead of writing formulas, you can just say, “Show me monthly hires versus terminations as a bar chart this year,” and get an interactive dashboard instantly. We built Graphed because we believe your time is better spent on strategy, not spreadsheet management.